【参考】JDBC执行存储过程的四种情况
- [1]、只有输入IN参数，没有输出OUT参数
- [2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
- [3]、既有输入IN参数，也有输出OUT参数，输出是列表
- [4]、输入输出参数是同一个（IN OUT）
-
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参数
存储过程 TEST_MICHAEL_NOOUT 的相关SQL：
-
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参数，输出是简单值（非列表）
存储过程 TEST_MICHAEL 的SQL如下：
-
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以上人员的数量 ，运行结果如下：
[三]、既有输入IN参数，也有输出OUT参数，输出是列表
首先需要创建PACKAGE TEST_PKG_CURSOR 的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如下：
-
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(); } } }
运行结果如下:
[四]、输入输出参数是同一个（IN OUT）
创建存储过程TEST_P_INOUT 的SQL如下：
-
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(); } } }
运行结果如下：
