本文主要是总结 如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：
- [1]、只有输入IN参数，没有输出OUT参数
- [2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
- [3]、既有输入IN参数，也有输出OUT参数，输出是列表
- [4]、输入输出参数是同一个（IN OUT）
【准备工作】
创建一个测试表TMP_MICHAEL ，并插入数据，SQL如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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://www.micmiu.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 常量：
|
1 2 3 4 |
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参数
存储过程 TEST_MICHAEL_NOOUT 的相关SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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; |
调用代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/** * 测试调用存储过程：无返回值 * @blog http://www.micmiu.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://www.micmiu.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参数，输出是简单值（非列表）
存储过程 TEST_MICHAEL 的SQL如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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; |
调用程序如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
/** * 测试调用存储过程：返回值是简单值非列表 * @blog http://www.micmiu.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如下：
|
1 2 3 4 5 6 |
CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS -- Author : MICHAEL http://www.micmiu.com TYPE TEST_CURSOR IS REF CURSOR; END TEST_PKG_CURSOR; |
再创建存储过程 TEST_P_OUTRS 的SQL如下：
|
1 2 3 4 5 6 7 8 9 10 |
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; |
调用存储过程的代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
/** * 测试调用存储过程：有返回值且返回值为列表的 * @blog http://www.micmiu.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://www.micmiu.com |
——- Test Proc Out is ResultSet end.
[四]、输入输出参数是同一个（IN OUT）
创建存储过程TEST_P_INOUT 的SQL如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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; |
调用存储过程的代码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
/** * 测试调用存储过程： INOUT同一个参数： * @blog http://www.micmiu.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.
到处基本介绍已经结束了，希望能给大家有所帮助。
原创文章，转载请注明： 转载自micmiu – 软件开发+生活点滴[ http://www.micmiu.com/ ]
本文链接地址: http://www.micmiu.com/j2ee/jdbc-tech/jdbc-proc-sample/
0 条评论。