JDBC执行存储过程的四种情况本文主要是总结?如何实现?JDBC调用Oracle的存储过程，从以下情况分别介绍：[1]、
JDBC执行存储过程的四种情况
本文主要是总结?如何实现?JDBC调用Oracle的存储过程，从以下情况分别介绍：
[1]、只有输入IN参数，没有输出OUT参数[2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）[3]、既有输入IN参数，也有输出OUT参数，输出是列表[4]、输入输出参数是同一个（IN OUT）
【准备工作】
? 创建一个测试表TMP_MICHAEL?，并插入数据，SQL如下：
Sql代码? ??
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 常量：
Java代码? ??
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：
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;??
调用代码如下：
Java代码? ??
/**?????*?测试调用存储过程：无返回值?????*?@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如下：
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;??
? 调用程序如下：
Java代码? ??
/**?????*?测试调用存储过程：返回值是简单值非列表?????*?@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以上人员的数量?，运行结果如下：
------- start 测试调用存储过程：返回值是简单值非列表
符号条件的查询结果 count := 4
------- Test End.
?
[三]、既有输入IN参数，也有输出OUT参数，输出是列表
??首先需要创建PACKAGE TEST_PKG_CURSOR?的SQL如下：
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如下：
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;??
?调用存储过程的代码如下：
Java代码? ??
/**?????*?测试调用存储过程：有返回值且返回值为列表的?????*?@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();?????????????}?????????}?????}??
?运行结果如下:
------- 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代码? ??
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;??
?调用存储过程的代码：
Java代码? ??
/**??????*?测试调用存储过程：?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();??????????????}??????????}??????}??
?运行结果如下：
------- start 测试调用存储过程：INOUT同一个参数
符号条件的查询结果 count := 1
------- Test End.
