Oracle存储过程以及java调用

一、没有返回值的储存过程:

1.创建一个表:

CREATE TABLE B_ID(I_ID VARCHAR2(30),I_NAME VARCHAR2(30));

2.插入数据:

INSERT INTO B_ID VALUES('1001','TESTING');

3. 创建储存过程:

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)  ASBEGIN   INSERT INTO SYS.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);END TESTA;

4. 在java中调用储存过程:

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";        Statement 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.setString(2, "TestTwo");          proc.execute();        }        catch (SQLException ex2) {          ex2.printStackTrace();        }        catch (Exception ex2) {          ex2.printStackTrace();        }        finally{          try {            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_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;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: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(?,?) }");       //该句把1001替换第一个问号。          proc.setString(1, "1001");      //该句将第二个问号设置成存储过程的返回参数类型。          proc.registerOutParameter(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();        }        finally{          try {            if(rs != null){              rs.close();              if(stmt!=null){                stmt.close();              }              if(conn!=null){                conn.close();              }            }          }          catch (SQLException ex1) {          }        }      }}

---

三、储存过程返回多条记录:

1.插入数据

INSERT INTO SECONDT VALUES('1002','TESTINGTWO');

2.建一个程序包

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS TYPE Test_CURSOR IS REF CURSOR;end TESTPACKAGE;

3.建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) ISBEGIN   OPEN p_CURSOR FOR SELECT * 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 driver = "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.prepareCall("{ 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);里的1          while(rs.next())          {              System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");          }        }        catch (SQLException ex2) {          ex2.printStackTrace();        }        catch (Exception ex2) {          ex2.printStackTrace();        }        finally{          try {            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 REF 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 <= higherNum) where rownum_ >lowerNum; 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 LOOP; CLOSE c; end; -------------------------------------------------------------------------------------CREATE OR REPLACE TYPE USERSEQID_ARRAY IS VARRAY(50000) OF NUMBER(9)CREATE OR REPLACE TYPE USERNAME_ARRAY  AS VARRAY(32) of varchar(32)CREATE OR REPLACE TYPE USERPWD_ARRAY  AS VARRAY(50000) of varchar(60)

四、java调用输出参数为自定义数组的存储过程：

1. 输出参数为自定义数组的存储过程make_logincard_pro：

procedure make_logincard_pro (p_cardsuitcode in varchar,p_userseqidArr out USERSEQID_ARRAY ,p_usernameArr out USERNAME_ARRAY )ISv_addedtime date:= sysdate;BEGIN    FOR ii IN 1 .. 10 LOOP        IF p_userseqidArr IS NULL THEN          p_userseqidArr := USERSEQID_ARRAY(ii);        ELSE           p_userseqidArr.EXTEND;   --超过数组定义大小(50000)将抛出异常             p_userseqidArr(ii) := ii;                        END IF;        IF p_usernameArr IS NULL THEN          p_usernameArr := USERSEQID_ARRAY(ii || 'TT');        ELSE           p_usernameArr.EXTEND;      --超过数组定义大小(32)将抛出异常           p_usernameArr(ii) := ii || 'TT';                        END IF;    END LOOPEND make_logincard_pro ;

2. JAVA调用存储过程make_logincard_pro：

//代码片段Connection con = session.connection();java.sql.CallableStatement cst = con        prepareCall("call CNBT.test_pro(?,?,?)");cst.setString(1, cardSuitCode);cst.registerOutParameter(2, OracleTypes.ARRAY,"USERSEQID_ARRAY");cst.registerOutParameter(3, OracleTypes.ARRAY,"USERNAME_ARRAY");java.sql.Array userSeqIdArr = cst.getArray(2);java.sql.Array userNameArr = cst.getArray(3);if ( userSeqIdArr  != null ) ...{    BigDecimal userSeqIdList[] = (BigDecimal[])userSeqIdArr.getArray();//数据库的number映射为BigDecimal     //。。。。。。}if ( userNameArr  != null ) ...{    String userNameList[] = (String[])userNameArr.getArray();     //。。。。。。}