JDBC执行存储过程的四种情况

本文主要是总结 如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：

• [1]、只有输入IN参数，没有输出OUT参数
• [2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
• [3]、既有输入IN参数，也有输出OUT参数，输出是列表
• [4]、输入输出参数是同一个（IN OUT）

【准备工作】

  创建一个测试表TMP_MICHAEL ，并插入数据，SQL如下：

Sql代码    

1. create table TMP_MICHAEL  
2. (  
3.   USER_ID    VARCHAR2(20),  
4.   USER_NAME  VARCHAR2(10),  
5.   SALARY     NUMBER(8,2),  
6.   OTHER_INFO VARCHAR2(100)  
7. )  
8.   
9. insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
10. values ('michael', 'Michael', 5000, 'http://sjsky.iteye.com');  
11. insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
12. values ('zhangsan', '张三', 10000, null);  
13. insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
14. values ('aoi_sola', '苍井空', 99999.99, 'twitter account');  
15. insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
16. values ('李四', '李四', 2500, null);  

  Oracle jdbc 常量：

Java代码    

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

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

 

 

存储过程 TEST_MICHAEL_NOOUT 的相关SQL：

Sql代码    

1. CREATE OR REPLACE PROCEDURE TEST_MICHAEL_NOOUT(P_USERID    IN VARCHAR2,  
2.                                                P_USERNAME  IN VARCHAR2,  
3.                                                P_SALARY    IN NUMBER,  
4.                                                P_OTHERINFO IN VARCHAR2) IS  
5. BEGIN  
6.   
7.   INSERT INTO TMP_MICHAEL  
8.     (USER_ID, USER_NAME, SALARY, OTHER_INFO)  
9.   VALUES  
10.     (P_USERID, P_USERNAME, P_SALARY, P_OTHERINFO);  
11.   
12. END TEST_MICHAEL_NOOUT;  

调用代码如下：

Java代码    

1. /** 
2.     * 测试调用存储过程：无返回值 
3.     * @blog http://sjsky.iteye.com 
4.     * @author Michael 
5.     * @throws Exception 
6.     */  
7.    public static void testProcNoOut() throws Exception {  
8.        System.out.println("-------  start 测试调用存储过程：无返回值");  
9.        Connection conn = null;  
10.        CallableStatement callStmt = null;  
11.        try {  
12.            Class.forName(DB_DRIVER);  
13.            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
14.            // 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据  
15.            callStmt = conn.prepareCall("{call TEST_MICHAEL_NOOUT(?,?,?,?)}");  
16.   
17.            // 参数index从1开始，依次 1,2,3...  
18.            callStmt.setString(1, "jdbc");  
19.            callStmt.setString(2, "JDBC");  
20.            callStmt.setDouble(3, 8000.00);  
21.            callStmt.setString(4, "http://sjsky.iteye.com");  
22.            callStmt.execute();  
23.            System.out.println("-------  Test End.");  
24.        } catch (Exception e) {  
25.            e.printStackTrace(System.out);  
26.        } finally {  
27.            if (null != callStmt) {  
28.                callStmt.close();  
29.            }  
30.            if (null != conn) {  
31.                conn.close();  
32.            }  
33.        }  
34.    }  

 

 

 

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

 

存储过程 TEST_MICHAEL 的SQL如下：

Sql代码    

1. CREATE OR REPLACE PROCEDURE TEST_MICHAEL(P_USERID IN VARCHAR2,  
2.                                          P_SALARY IN NUMBER,  
3.                                          P_COUNT  OUT NUMBER) IS  
4.   V_SALARY NUMBER := P_SALARY;  
5. BEGIN  
6.   IF V_SALARY IS NULL THEN  
7.     V_SALARY := 0;  
8.   END IF;  
9.   IF P_USERID IS NULL THEN  
10.     SELECT COUNT(*)  
11.       INTO P_COUNT  
12.       FROM TMP_MICHAEL T  
13.      WHERE T.SALARY >= V_SALARY;  
14.   ELSE  
15.     SELECT COUNT(*)  
16.       INTO P_COUNT  
17.       FROM TMP_MICHAEL T  
18.      WHERE T.SALARY >= V_SALARY  
19.        AND T.USER_ID LIKE '%' || P_USERID || '%';  
20.   END IF;  
21.   DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);  
22. END TEST_MICHAEL;  

  调用程序如下：

Java代码    

1. /** 
2.     * 测试调用存储过程：返回值是简单值非列表 
3.     * @blog http://sjsky.iteye.com 
4.     * @author Michael 
5.     * @throws Exception 
6.     */  
7.    public static void testProcOutSimple() throws Exception {  
8.        System.out.println("-------  start 测试调用存储过程：返回值是简单值非列表");  
9.        Connection conn = null;  
10.        CallableStatement stmt = null;  
11.        try {  
12.            Class.forName(DB_DRIVER);  
13.            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
14.   
15.            stmt = conn.prepareCall("{call TEST_MICHAEL(?,?,?)}");  
16.   
17.            stmt.setString(1, "");  
18.            stmt.setDouble(2, 3000);  
19.   
20.            // out 注册的index 和取值时要对应  
21.            stmt.registerOutParameter(3, Types.INTEGER);  
22.            stmt.execute();  
23.   
24.            // getXxx(index)中的index 需要和上面registerOutParameter的index对应  
25.            int i = stmt.getInt(3);  
26.            System.out.println("符号条件的查询结果 count := " + i);  
27.            System.out.println("-------  Test End.");  
28.        } catch (Exception e) {  
29.            e.printStackTrace(System.out);  
30.        } finally {  
31.            if (null != stmt) {  
32.                stmt.close();  
33.            }  
34.            if (null != conn) {  
35.                conn.close();  
36.            }  
37.        }  
38.    }  

   测试程序就是查询薪水3000以上人员的数量 ，运行结果如下：

------- start 测试调用存储过程：返回值是简单值非列表 
符号条件的查询结果 count := 4 
------- Test End.

 

[三]、既有输入IN参数，也有输出OUT参数，输出是列表

  首先需要创建PACKAGE TEST_PKG_CURSOR 的SQL如下：

Sql代码    

1. CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS  
2.   
3.   -- Author  : MICHAEL  http://sjsky.iteye.com  
4.   TYPE TEST_CURSOR IS REF CURSOR;  
5.   
6. END TEST_PKG_CURSOR;  

再创建存储过程 TEST_P_OUTRS 的SQL如下：

Sql代码    

1. CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER,  
2.                                          P_OUTRS  OUT TEST_PKG_CURSOR.TEST_CURSOR) IS  
3.   V_SALARY NUMBER := P_SALARY;  
4. BEGIN  
5.   IF P_SALARY IS NULL THEN  
6.     V_SALARY := 0;  
7.   END IF;  
8.   OPEN P_OUTRS FOR  
9.     SELECT * FROM TMP_MICHAEL T WHERE T.SALARY > V_SALARY;  
10. END TEST_P_OUTRS;  

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

Java代码    

1. /** 
2.     * 测试调用存储过程：有返回值且返回值为列表的 
3.     * @blog http://sjsky.iteye.com 
4.     * @author Michael 
5.     * @throws Exception 
6.     */  
7.    public static void testProcOutRs() throws Exception {  
8.        System.out.println("-------  start 测试调用存储过程：有返回值且返回值为列表的");  
9.        Connection conn = null;  
10.        CallableStatement stmt = null;  
11.        ResultSet rs = null;  
12.        try {  
13.            Class.forName(DB_DRIVER);  
14.            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
15.   
16.            stmt = conn.prepareCall("{call TEST_P_OUTRS(?,?)}");  
17.   
18.            stmt.setDouble(1, 3000);  
19.            stmt.registerOutParameter(2, OracleTypes.CURSOR);  
20.            stmt.execute();  
21.   
22.            // getXxx(index)中的index 需要和上面registerOutParameter的index对应  
23.            rs = (ResultSet) stmt.getObject(2);  
24.            // 获取列名及类型  
25.            int colunmCount = rs.getMetaData().getColumnCount();  
26.            String[] colNameArr = new String[colunmCount];  
27.            String[] colTypeArr = new String[colunmCount];  
28.            for (int i = 0; i < colunmCount; i++) {  
29.                colNameArr[i] = rs.getMetaData().getColumnName(i + 1);  
30.                colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1);  
31.                System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")"  
32.                        + " | ");  
33.            }  
34.            System.out.println();  
35.            while (rs.next()) {  
36.                StringBuffer sb = new StringBuffer();  
37.                for (int i = 0; i < colunmCount; i++) {  
38.                    sb.append(rs.getString(i + 1) + " | ");  
39.                }  
40.                System.out.println(sb);  
41.            }  
42.            System.out.println("------- Test Proc Out is ResultSet end. ");  
43.   
44.        } catch (Exception e) {  
45.            e.printStackTrace(System.out);  
46.        } finally {  
47.            if (null != rs) {  
48.                rs.close();  
49.            }  
50.            if (null != stmt) {  
51.                stmt.close();  
52.            }  
53.            if (null != conn) {  
54.                conn.close();  
55.            }  
56.        }  
57.    }  

 运行结果如下:

------- 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）

 

创建存储过程TEST_P_INOUT 的SQL如下：

Sql代码    

1. CREATE OR REPLACE PROCEDURE TEST_P_INOUT(P_USERID IN VARCHAR2,  
2.                                          P_NUM    IN OUT NUMBER) IS  
3.   V_COUNT  NUMBER;  
4.   V_SALARY NUMBER := P_NUM;  
5. BEGIN  
6.   IF V_SALARY IS NULL THEN  
7.     V_SALARY := 0;  
8.   END IF;  
9.   
10.   SELECT COUNT(*)  
11.     INTO V_COUNT  
12.     FROM TMP_MICHAEL  
13.    WHERE USER_ID LIKE '%' || P_USERID || '%'  
14.      AND SALARY >= V_SALARY;  
15.   P_NUM := V_COUNT;  
16. END TEST_P_INOUT;  

 调用存储过程的代码：

Java代码    

1. /** 
2.      * 测试调用存储过程： INOUT同一个参数： 
3.      * @blog http://sjsky.iteye.com 
4.      * @author Michael 
5.      * @throws Exception 
6.      */  
7.     public static void testProcInOut() throws Exception {  
8.         System.out.println("-------  start 测试调用存储过程：INOUT同一个参数");  
9.         Connection conn = null;  
10.         CallableStatement stmt = null;  
11.         try {  
12.             Class.forName(DB_DRIVER);  
13.             conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);  
14.   
15.             stmt = conn.prepareCall("{call TEST_P_INOUT(?,?)}");  
16.   
17.             stmt.setString(1, "michael");  
18.             stmt.setDouble(2, 3000);  
19.   
20.             // 注意此次注册out 的index 和上面的in 参数index 相同  
21.             stmt.registerOutParameter(2, Types.INTEGER);  
22.             stmt.execute();  
23.   
24.             // getXxx(index)中的index 需要和上面registerOutParameter的index对应  
25.             int count = stmt.getInt(2);  
26.             System.out.println("符号条件的查询结果 count := " + count);  
27.             System.out.println("-------  Test End.");  
28.         } catch (Exception e) {  
29.             e.printStackTrace(System.out);  
30.         } finally {  
31.             if (null != stmt) {  
32.                 stmt.close();  
33.             }  
34.             if (null != conn) {  
35.                 conn.close();  
36.             }  
37.         }  
38.     }  

 运行结果如下：

------- start 测试调用存储过程：INOUT同一个参数 
符号条件的查询结果 count := 1 
------- Test End.