Oracle 存储过程的使用

本文主要是总结 如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：
[1]、只有输入IN参数，没有输出OUT参数
[2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
[3]、既有输入IN参数，也有输出OUT参数，输出是列表
[4]、输入输出参数是同一个（IN OUT）
[5]、存储过程中 使用 truncate 截断表中的数据
【准备工作】
  创建一个测试表TMP_MICHAEL ，并插入数据，SQL如下：

create table TMP_MICHAEL  
(  
  USER_ID    VARCHAR2(20),  
  USER_NAME  VARCHAR2(10),  
  SALARY     NUMBER(8,2),  
  OTHER_INFO VARCHAR2(100)  
)  
  
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
values ('michael', 'Michael', 5000, 'http://sjsky.iteye.com');  
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
values ('zhangsan', '张三', 10000, null);  
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
values ('aoi_sola', '苍井空', 99999.99, 'twitter account');  
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
values ('李四', '李四', 2500, null); 
 

Oracle jdbc 常量：

private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";  
private final static String DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1521:Ora11g";  
private final static String DB_NAME = "mytest";  
private final static String DB_PWd = "111111"; 

[一]、只有输入IN参数，没有输出OUT参数

CREATE OR REPLACE PROCEDURE TEST_MICHAEL_NOOUT(P_USERID    IN VARCHAR2,  
                                               P_USERNAME  IN VARCHAR2,  
                                               P_SALARY    IN NUMBER,  
                                               P_OTHERINFO IN VARCHAR2) IS  
BEGIN  
  
  INSERT INTO TMP_MICHAEL  
    (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
  VALUES  
    (P_USERID, P_USERNAME, P_SALARY, P_OTHERINFO);  
  
END TEST_MICHAEL_NOOUT;  

调用代码如下：

/** 
    * 测试调用存储过程：无返回值 
    * @blog http://sjsky.iteye.com 
    * @author Michael 
    * @throws Exception 
    */  
   public static void testProcNoOut() throws Exception {  
       System.out.println("-------  start 测试调用存储过程：无返回值");  
       Connection conn = null;  
       CallableStatement callStmt = null;  
       try {  
           Class.forName(DB_DRIVER);  
           conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
           // 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据  
           callStmt = conn.prepareCall("{call TEST_MICHAEL_NOOUT(?,?,?,?)}");  
  
           // 参数index从1开始，依次 1,2,3...  
           callStmt.setString(1, "jdbc");  
           callStmt.setString(2, "JDBC");  
           callStmt.setDouble(3, 8000.00);  
           callStmt.setString(4, "http://sjsky.iteye.com");  
           callStmt.execute();  
           System.out.println("-------  Test End.");  
       } catch (Exception e) {  
           e.printStackTrace(System.out);  
       } finally {  
           if (null != callStmt) {  
               callStmt.close();  
           }  
           if (null != conn) {  
               conn.close();  
           }  
       }  
   }  

[二]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）

CREATE OR REPLACE PROCEDURE TEST_MICHAEL(P_USERID IN VARCHAR2,  
                                         P_SALARY IN NUMBER,  
                                         P_COUNT  OUT NUMBER) IS  
  V_SALARY NUMBER := P_SALARY;  
BEGIN  
  IF V_SALARY IS NULL THEN  
    V_SALARY := 0;  
  END IF;  
  IF P_USERID IS NULL THEN  
    SELECT COUNT(*)  
      INTO P_COUNT  
      FROM TMP_MICHAEL T  
     WHERE T.SALARY >= V_SALARY;  
  ELSE  
    SELECT COUNT(*)  
      INTO P_COUNT  
      FROM TMP_MICHAEL T  
     WHERE T.SALARY >= V_SALARY  
       AND T.USER_ID LIKE '%' || P_USERID || '%';  
  END IF;  
  DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);  
END TEST_MICHAEL;  

调用程序如下

/** 
    * 测试调用存储过程：返回值是简单值非列表 
    * @blog http://sjsky.iteye.com 
    * @author Michael 
    * @throws Exception 
    */  
   public static void testProcOutSimple() throws Exception {  
       System.out.println("-------  start 测试调用存储过程：返回值是简单值非列表");  
       Connection conn = null;  
       CallableStatement stmt = null;  
       try {  
           Class.forName(DB_DRIVER);  
           conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
  
           stmt = conn.prepareCall("{call TEST_MICHAEL(?,?,?)}");  
  
           stmt.setString(1, "");  
           stmt.setDouble(2, 3000);  
  
           // out 注册的index 和取值时要对应  
           stmt.registerOutParameter(3, Types.INTEGER);  
           stmt.execute();  
  
           // getXxx(index)中的index 需要和上面registerOutParameter的index对应  
           int i = stmt.getInt(3);  
           System.out.println("符号条件的查询结果 count := " + i);  
           System.out.println("-------  Test End.");  
       } catch (Exception e) {  
           e.printStackTrace(System.out);  
       } finally {  
           if (null != stmt) {  
               stmt.close();  
           }  
           if (null != conn) {  
               conn.close();  
           }  
       }  
   }  

测试程序就是查询薪水3000以上人员的数量 ，运行结果如下：
------- start 测试调用存储过程：返回值是简单值非列表
符号条件的查询结果 count := 4
------- Test End.


[三]、既有输入IN参数，也有输出OUT参数，输出是列表
  首先需要创建PACKAGE TEST_PKG_CURSOR 的SQL如下：

首先需要创建PACKAGE TEST_PKG_CURSOR 的SQL如下：
Sql代码     
CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS  
  
  -- Author  : MICHAEL  http://sjsky.iteye.com  
  TYPE TEST_CURSOR IS REF CURSOR;  
  
END TEST_PKG_CURSOR;  
再创建存储过程 TEST_P_OUTRS 的SQL如下：
Sql代码     
CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER,  
                                         P_OUTRS  OUT TEST_PKG_CURSOR.TEST_CURSOR) IS  
  V_SALARY NUMBER := P_SALARY;  
BEGIN  
  IF P_SALARY IS NULL THEN  
    V_SALARY := 0;  
  END IF;  
  OPEN P_OUTRS FOR  
    SELECT * FROM TMP_MICHAEL T WHERE T.SALARY > V_SALARY;  
END TEST_P_OUTRS;  

调用存储过程的代码如下：

/** 
    * 测试调用存储过程：有返回值且返回值为列表的 
    * @blog http://sjsky.iteye.com 
    * @author Michael 
    * @throws Exception 
    */  
   public static void testProcOutRs() throws Exception {  
       System.out.println("-------  start 测试调用存储过程：有返回值且返回值为列表的");  
       Connection conn = null;  
       CallableStatement stmt = null;  
       ResultSet rs = null;  
       try {  
           Class.forName(DB_DRIVER);  
           conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
  
           stmt = conn.prepareCall("{call TEST_P_OUTRS(?,?)}");  
  
           stmt.setDouble(1, 3000);  
           stmt.registerOutParameter(2, OracleTypes.CURSOR);  
           stmt.execute();  
  
           // getXxx(index)中的index 需要和上面registerOutParameter的index对应  
           rs = (ResultSet) stmt.getObject(2);  
           // 获取列名及类型  
           int colunmCount = rs.getMetaData().getColumnCount();  
           String[] colNameArr = new String[colunmCount];  
           String[] colTypeArr = new String[colunmCount];  
           for (int i = 0; i < colunmCount; i++) {  
               colNameArr[i] = rs.getMetaData().getColumnName(i + 1);  
               colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1);  
               System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")"  
                       + " | ");  
           }  
           System.out.println();  
           while (rs.next()) {  
               StringBuffer sb = new StringBuffer();  
               for (int i = 0; i < colunmCount; i++) {  
                   sb.append(rs.getString(i + 1) + " | ");  
               }  
               System.out.println(sb);  
           }  
           System.out.println("------- Test Proc Out is ResultSet end. ");  
  
       } catch (Exception e) {  
           e.printStackTrace(System.out);  
       } finally {  
           if (null != rs) {  
               rs.close();  
           }  
           if (null != stmt) {  
               stmt.close();  
           }  
           if (null != conn) {  
               conn.close();  
           }  
       }  
   }  
 运行结果如下:
------- start 测试调用存储过程：有返回值且返回值为列表的 
USER_ID(VARCHAR2) | USER_NAME(VARCHAR2) | SALARY(NUMBER) | OTHER_INFO(VARCHAR2) |  
michael | Michael | 5000 | null |  
zhangsan | 张三 | 10000 | null |  
aoi_sola | 苍井空 | 99999.99 | null |  
jdbc | JDBC | 8000 | http://sjsky.iteye.com |  
------- Test Proc Out is ResultSet end.

[四]、输入输出参数是同一个（IN OUT）

CREATE OR REPLACE PROCEDURE TEST_P_INOUT(P_USERID IN VARCHAR2,  
                                         P_NUM    IN OUT NUMBER) IS  
  V_COUNT  NUMBER;  
  V_SALARY NUMBER := P_NUM;  
BEGIN  
  IF V_SALARY IS NULL THEN  
    V_SALARY := 0;  
  END IF;  
  
  SELECT COUNT(*)  
    INTO V_COUNT  
    FROM TMP_MICHAEL  
   WHERE USER_ID LIKE '%' || P_USERID || '%'  
     AND SALARY >= V_SALARY;  
  P_NUM := V_COUNT;  
END TEST_P_INOUT;  

调用存储过程的代码：

/** 
     * 测试调用存储过程： INOUT同一个参数： 
     * @blog http://sjsky.iteye.com 
     * @author Michael 
     * @throws Exception 
     */  
    public static void testProcInOut() throws Exception {  
        System.out.println("-------  start 测试调用存储过程：INOUT同一个参数");  
        Connection conn = null;  
        CallableStatement stmt = null;  
        try {  
            Class.forName(DB_DRIVER);  
            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
  
            stmt = conn.prepareCall("{call TEST_P_INOUT(?,?)}");  
  
            stmt.setString(1, "michael");  
            stmt.setDouble(2, 3000);  
  
            // 注意此次注册out 的index 和上面的in 参数index 相同  
            stmt.registerOutParameter(2, Types.INTEGER);  
            stmt.execute();  
  
            // getXxx(index)中的index 需要和上面registerOutParameter的index对应  
            int count = stmt.getInt(2);  
            System.out.println("符号条件的查询结果 count := " + count);  
            System.out.println("-------  Test End.");  
        } catch (Exception e) {  
            e.printStackTrace(System.out);  
        } finally {  
            if (null != stmt) {  
                stmt.close();  
            }  
            if (null != conn) {  
                conn.close();  
            }  
        }  
    }  
 运行结果如下：
------- start 测试调用存储过程：INOUT同一个参数 
符号条件的查询结果 count := 1 
------- Test End.

[五] 存储过程中使用 truncate  清空表中的数据

create or replace procedure PROC_INSERT_BLDAREN(rownums in number) is
begin
  EXECUTE IMMEDIATE 'TRUNCATE TABLE BI_BAOLIAO_DAREN';
  insert into BI_BAOLIAO_DAREN (ID,USERID,USERNAME,BAOLIAONUM,CREDITS) select bi_baoliao_sequence.nextval,bl.* from (select b.userid,b.username,count(b.id),sum(b.credits) credits from bi_baoliao b  group by b.userid,b.username order by credits desc) bl where rownum <=rownums;
end PROC_INSERT_BLDAREN;

java 调用

/**
* 使用 truncate 先清空表中的数据
* 然后 插入数据
*/
public static boolean updateData1(int rownum){
		boolean result=true;
		Connection conn=null;
		CallableStatement  cs=null;
		try {
			Date stime=new Date();
			conn=DBConnection.getConnection();
			cs=conn.prepareCall("{call PROC_INSERT_BLDAREN(?)}");
			cs.setInt(1, rownum);
			result=cs.execute();
			Date etime=new Date();
			System.out.println(etime.getTime()-stime.getTime());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.cleanUp(null, null, cs, null);
		}
		return result;
	}