《储存过程基本实例》由会员分享，可在线阅读，更多相关《储存过程基本实例（12页珍藏版）》请在金锄头文库上搜索。
1、储存过程基本实例储存过程基本实例没有返回值的储存过程:1.创建一个表:CREATE TABLE B_ID(I_ID VARCHAR2(30),I_NAME VARCHAR2(30);2.插入数据:INSERT INTO B_ID VALUES(1001,TESTING);创建储存过程:CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) ASBEGININSERT INTO SYS.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);END TESTA;在 java 中调用储存
2、过程:import java.sql.*;import java.sql.PreparedStatement;import java.sql.CallableStatement;import java.sql.Connection;public class jiangdi /* param args*/public static void main(String args) String driver = “oracle.jdbc.driver.OracleDriver“;String strUrl = “jdbc:oracle:thin:localhost:1521:ORCL“;Statem
3、ent stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement cstmt = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl,“SYS as SYSDBA“,“123abcABC“);CallableStatement proc = null;proc = conn.prepareCall(“ call SYS.TESTA(?,?) “);proc.setString(1, “101“);proc.setS
4、tring(2, “TestTwo“);proc.execute();catch (SQLException ex2) ex2.printStackTrace();catch (Exception ex2) ex2.printStackTrace();finallytry if(rs != null)rs.close();if(stmt!=null)stmt.close();if(conn!=null)conn.close();catch (SQLException ex1) -有返回值的储存过程:1.新建一个表:CREATE TABLE SECONDT(I_ID VARCHAR2(30),I
5、_NAME VARCHAR2(30);2.插入数据:INSERT INTO SECONDT VALUES(1001,TESTING);3.新建一个储存过程:CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) ASBEGIN SELECT I_NAME INTO PARA2 from SECONDT WHERE I_ID=PARA1;END TESTB;4.在 java 调用储存过程:import java.sql.CallableStatement;import java.sql.Connection;
6、import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;public class ProINOUT /* param args*/public static void main(String args) String driver = “oracle.jdbc.driver.OracleDriver“;String strUrl = “jdbc:oracle:thin:localhost
7、:1521:ORCL“;Statement stmt = null;ResultSet rs = null;Connection conn = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl, “SYS as SYSDBA“, “123abcABC“);CallableStatement proc = null;proc = conn.prepareCall(“ call SYS.TESTB(?,?) “);proc.setString(1, “1001“);proc.registerOutPar
8、ameter(2, Types.VARCHAR);proc.execute();String testPrint = proc.getString(2);System.out.println(“testPrint=is=“+testPrint);catch (SQLException ex2) ex2.printStackTrace();catch (Exception ex2) ex2.printStackTrace();finallytry if(rs != null)rs.close();if(stmt!=null)stmt.close();if(conn!=null)conn.clos
9、e();catch (SQLException ex1) -储存过程返回多条记录:1.插入数据INSERT INTO SECONDT VALUES(1002,TESTINGTWO);2.CREATE OR REPLACE PACKAGE TESTPACKAGE ASTYPE Test_CURSOR IS REF CURSOR;end TESTPACKAGE;3.建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) ISBEGINOPEN p_CURSOR FOR SELECT *
10、 FROM SYS.SECONDT;END TESTC;4.java 调用储存过程:import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class ProCursor /* param args*/public static void main(String args) String driv
11、er = “oracle.jdbc.driver.OracleDriver“;String strUrl = “jdbc:oracle:thin:localhost:1521:ORCL“;Statement stmt = null;ResultSet rs = null;Connection conn = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl,“SYS as SYSDBA“, “123abcABC“);CallableStatement proc = null;proc = conn.p
12、repareCall(“ call SYS.TESTC(?) “);proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);proc.execute();rs = (ResultSet)proc.getObject(1); /1 代表 proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);里的 1while(rs.next()System.out.println(“ + rs.getString(1) + “+rs.getString(2)+“);catch (SQ
13、LException ex2) ex2.printStackTrace();catch (Exception ex2) ex2.printStackTrace();finallytry if(rs != null)rs.close();if(stmt!=null)stmt.close();if(conn!=null)conn.close();catch (SQLException ex1) -补充一个使用 oracle 存储过程分页的小例子： 1， 建一个程序包。如下： CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS R
14、EF CURSOR; end TESTPACKAGE; 2，建立存储过程，存储过程为： create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR,lowerNum in numeric,higherNum in numeric) is begin OPEN p_CURSOR FOR select * from ( select row_.*, rownum rownum_ from (select id,title,status from HYQTEST) row_ where rownum lowerNum;
15、 end TESTC; 使用 plsql 测试： declare lowerNum integer; higherNum integer; id varchar2(10); title varchar2(500); status numeric; c testpackage.Test_CURSOR; rownum_ integer; begin lowerNum:=1; higherNum:=10; TESTC(c,lowerNum,higherNum); LOOP FETCH c INTO id,title,status,rownum_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(=行号=|rownum_|=|id|=|title|=|status|=); END L
