当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2015-02-10   作者:_wy_   来源:转载   浏览:
摘要: 转载自:http://sishuok.com/forum/blogPost/list/488.html   本文主要是总结 如何实现 JDBC调用Oracle的存储过程,从以下情况分别介绍: [1]、只有输入IN参数,没有输出OUT参数 [2]、既有输入IN参数,也有输出OUT参数,输出是简单值(非列表) [3]、既有输入IN参数,也有输出OUT参

转载自:http://sishuok.com/forum/blogPost/list/488.html

 

本文主要是总结 如何实现 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(38000.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(23000);  
  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(13000);  
  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(23000);  
  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.

 

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

推荐文章
编辑推荐
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号