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)  AS
BEGIN
   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)  AS
BEGIN  
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) IS
BEGIN
   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 
)
IS
v_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 LOOP
END 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(); 
    //。。。。。。
}