您的位置: 首页  >  文章  >  Oracle存储过程的分类以及JDBC调用

Oracle存储过程的分类以及JDBC调用

分类: 文章 • 2024-03-05 13:34:18
本文主要是总结 如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍： 
[1]、只有输入IN参数，没有输出OUT参数 
[2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表） 
[3]、既有输入IN参数，也有输出OUT参数，输出是列表 
[4]、输入输出参数是同一个（IN OUT） 
[5]、存储过程中 使用 truncate 截断表中的数据 
【准备工作】 
  创建一个测试表TMP_MICHAEL ，并插入数据，SQL如下： 
[sql] view plain copy
  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);   
  17.    


Oracle jdbc 常量： 
[java] view plain copy
  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参数 
[sql] view plain copy
  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] view plain copy
  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参数，输出是简单值（非列表） 
[sql] view plain copy
  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] view plain copy
  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如下： 
[java] view plain copy
  1. 首先需要创建PACKAGE TEST_PKG_CURSOR 的SQL如下：  
  2. Sql代码       
  3. CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS    
  4.     
  5.   -- Author  : MICHAEL  http://sjsky.iteye.com    
  6.   TYPE TEST_CURSOR IS REF CURSOR;    
  7.     
  8. END TEST_PKG_CURSOR;    
  9. 再创建存储过程 TEST_P_OUTRS 的SQL如下：  
  10. Sql代码       
  11. CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER,    
  12.                                          P_OUTRS  OUT TEST_PKG_CURSOR.TEST_CURSOR) IS    
  13.   V_SALARY NUMBER := P_SALARY;    
  14. BEGIN    
  15.   IF P_SALARY IS NULL THEN    
  16.     V_SALARY := 0;    
  17.   END IF;    
  18.   OPEN P_OUTRS FOR    
  19.     SELECT * FROM TMP_MICHAEL T WHERE T.SALARY > V_SALARY;    
  20. END TEST_P_OUTRS;    


调用存储过程的代码如下： 
[java] view plain copy
  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.    }    
  58.  运行结果如下:  
  59. ------- start 测试调用存储过程：有返回值且返回值为列表的   
  60. USER_ID(VARCHAR2) | USER_NAME(VARCHAR2) | SALARY(NUMBER) | OTHER_INFO(VARCHAR2) |    
  61. michael | Michael | 5000 | null |    
  62. zhangsan | 张三 | 10000 | null |    
  63. aoi_sola | 苍井空 | 99999.99 | null |    
  64. jdbc | JDBC | 8000 | http://sjsky.iteye.com |    
  65. ------- Test Proc Out is ResultSet end.  


[四]、输入输出参数是同一个（IN OUT） 
[sql] view plain copy
  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] view plain copy
  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.     }    
  39.  运行结果如下：  
  40. ------- start 测试调用存储过程：INOUT同一个参数   
  41. 符号条件的查询结果 count := 1   
  42. ------- Test End.  


[五] 存储过程中使用 truncate  清空表中的数据 
[sql] view plain copy
  1. create or replace procedure PROC_INSERT_BLDAREN(rownums in number) is  
  2. begin  
  3.   EXECUTE IMMEDIATE 'TRUNCATE TABLE BI_BAOLIAO_DAREN';  
  4.   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;  
  5. end PROC_INSERT_BLDAREN;  

java 调用 
[java] view plain copy
  1. /** 
  2. * 使用 truncate 先清空表中的数据 
  3. * 然后 插入数据 
  4. */  
  5. public static boolean updateData1(int rownum){  
  6.         boolean result=true;  
  7.         Connection conn=null;  
  8.         CallableStatement  cs=null;  
  9.         try {  
  10.             Date stime=new Date();  
  11.             conn=DBConnection.getConnection();  
  12.             cs=conn.prepareCall("{call PROC_INSERT_BLDAREN(?)}");  
  13.             cs.setInt(1, rownum);  
  14.             result=cs.execute();  
  15.             Date etime=new Date();  
  16.             System.out.println(etime.getTime()-stime.getTime());  
  17.         }catch(Exception e){  
  18.             e.printStackTrace();  
  19.         }finally{  
  20.             DBConnection.cleanUp(null, null, cs, null);  
  21.         }  
  22.         return result;  
  23.     }      

欢迎访问我的个人公众号，一起交流学习心得，共同进步！

Oracle存储过程的分类以及JDBC调用

相关推荐