思享工具箱

JDBC存储过程

摘要：
CallableStatement是用于执行存储过程的特殊接口。它继承了Statement接口的常用方法：boolean execute（）：执行SQL语句并返回true。第一个SQL语句的结果是ResultSet；否则，第一个sql语句将返回受影响的行。GetResultSet（）：用于在执行“select”语句后获取查询结果集。
CallableStatement
    专执行存储过程的接口，继承了Statement接口
 
常用方法:
    boolean execute(): 执行sql语句,返回true,第一个sql语句的结果是ResultSet;否则,第一个sql语句返回的是受影响的行。
    getResultSet():用于获取执行"select"语句后的查询结果集。
    getUpdateCount():用于获取执行非查询后的受影响的行数。
 
    boolean getMoreResults(): 移到下一个结果,如果下一个结果是查询返回true,否则返回false。
 
 
    输出参数(output)
    registerOutParameter(int parameterIndex,
                          int sqlType)：　注册输出参数
 
 
 案例：调用一个无参,仅包含一个select语句的存储过程(p1)
 
调用一个无参,仅包含一个非select语句的存储过程(p2)
 
调用一个带输入参数,仅包含一个select语句的存储过程(p3)
 
调用一个带输入参数,仅包含一个非select语句的存储过程(p4)
 
调用一个带输入参数,包含多行语句（即有select也有非select）的存储过程(p5)
 
调用一个带输出参数,仅包含一个非select语句的存储过程(p6)
 
 
 
 
 
 
本文主要是总结 如何实现 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(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参数，输出是简单值（非列表）
 
存储过程 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(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如下：
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(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.    }  
 运行结果如下:
------- 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(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.     }  
 运行结果如下：
------- start 测试调用存储过程：INOUT同一个参数 
符号条件的查询结果 count := 1 
------- Test End.

标签：#存储过程#jdbc#返回值#select | 浏览：125 | 发布日期：

免责声明：文章转载自《JDBC存储过程》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇iOS异常捕获linux MD5 SHA1 等 文件校验方法下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

IE下设置SELECT项不可选

因为最近在用jquery 所以JS是Jquery格式写的 代码 1<selectname="drpADAddress"id="drpADAddress">2<optionvalue="0">请选择</option>3<optionvalue="0"disabled="true">首页</option&...

JDBC实现从数据库中读取信息以表格的形式展示

方法都是通用的，要看你如何去灵活的运用！！！ 本文以“新闻发布系统”为例 第一步：建立数据库（使用的是mysql数据库） 其中数据库信息为： 数据库名：news 表名：title，字段名分别为（序号、标题名称、创建者、创建时间） create table title( id int(2) primary key, name varchar(20), cre...

转 mysql mysql命令行中执行sql的几种方式总结

https://www.jb51.net/article/96394.htm 1.直接输入sql执行 MySQL> select now(); +---------------------+ | now() | +---------------------+ | 2013-09-18 13:55:45 | +---------------------...

jndi和jta事务

一般情况下，J2EE应用服务器支持JDBC事务、JTA事务、容器管理事务。这里讨论JTA和JDBC事务的区别。这2个是常用的DAO模式事务界定方式。 JDBC 事务 JDBC 事务是用 Connection 对象控制的。JDBC Connection 接口( java.sql.Connection )提供了两种事务模式：自动提交和手工提交。例如如下片断：...

Oracle 内置函数

SQL中的单记录函数 1.ASCII返回与指定的字符对应的十进制数;SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE--------- --------- --------- ---------65 97 4...

HTML、jsp页面中radio，checkbox，select数据回显功能，默认被选中问题

最近常常遇到各种复选框、单选框、下拉框的默认被选中的问题，开始也是绞尽脑汁的想办法，今天写一篇学习总结的博文来写一下学习总结。 单选框（radio）默认被选中： 一、jstl技术进行回显 <input type="radio" name="sex" <f:if test="${c.sex=='男' }">checked="checked"...

最新文章

随机推荐

思享工具箱导航