【参考】JDBC执行存储过程的四种情况-白红宇

【参考】JDBC执行存储过程的四种情况

阅读量：6933 次

发布时间：2019-06-27

本文共 9204 字，大约阅读时间需要 30 分钟。

[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) ISBEGIN  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以上人员的数量，运行结果如下：

------- start 测试调用存储过程：返回值是简单值非列表

符号条件的查询结果 count := 4

------- Test End.

[三]、既有输入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();            }        }    }

运行结果如下:

------- 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如下：

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();            }        }    }

运行结果如下：

------- start 测试调用存储过程：INOUT同一个参数

符号条件的查询结果 count := 1

------- Test End.

转载于:https://www.cnblogs.com/zhangxsh/p/3494562.html

你可能感兴趣的文章

CentOS7.X的系统管理、安全设置及系统优化思路

查看>>

npm全局安装和本地安装和本地开发安装（npm install --g/--save/--save-dev）

查看>>

20个非常有用的Java程序片段

查看>>