JDBC执行存储过程的四种情况

本文主要是总结 如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：

[1] 只有输入IN参数，没有输出OUT参数；
[2] 既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）；
[3] 既有输入IN参数，也有输出OUT参数，输出是列表；
[4] 输入输出参数是同一个（IN OUT）。

准备工作

创建一个表tb_jdbc_procedure，并插入数据，具体SQL语句如下：

--删除表
drop table  tb_jdbc_procedure;
--新建表
create table tb_jdbc_procedure(
user_id varchar2(20),
user_name varchar2(10),
salary number(8,2),
other_info varchar2(100)
);
--插入数据
insert into tb_jdbc_procedure
  (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
  ('cobcmw', 'cobcmw', 5000, 'http://www.4spaces.org');
insert into tb_jdbc_procedure
  (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
  ('zhangsan', '张三', 10000, null);
insert into tb_jdbc_procedure
  (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
  ('aoi_sola', '苍井空', 99999.99, 'twitter account');
insert into tb_jdbc_procedure
  (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
  ('李四', '李四', 2500, null);
--查询
select * from tb_jdbc_procedure;

Oracle Jdbc连接参数：

String className = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "cobcmw";
String password = "1234567890";

下面针对四种情况进行测试：

[1] 只有输入IN参数，没有输出OUT参数

存储过程pro_jdbc_in创建的相关SQL：

-- 新建存储过程 pro_jdbc_in  只有输入IN参数，没有输出OUT参数

create or replace procedure pro_jdbc_in(p_user_id    in varchar2,
                                        p_user_name  in varchar2,
                                        p_salary     in number,
                                        p_other_info in varchar2) is
BEGIN
  insert into tb_jdbc_procedure
    (USER_ID, USER_NAME, SALARY, OTHER_INFO)
  values
    (p_user_id, p_user_name, p_salary, p_other_info);
END pro_jdbc_in;

调用的Java代码如下：

public static void main(String[] args) {
        String className = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "cobcmw";
        String password = "1234567890";
        /**
         * 执行存储过程:[1] 只有输入IN参数，没有输出OUT参数；
         */
        try {
            Class.forName(className).newInstance();
            Connection conn = DriverManager.getConnection(url,user,password);
            CallableStatement   cs = conn.prepareCall("{ call pro_jdbc_in(?,?,?,?) }");
            cs.setString(1,"xiaowang");
            cs.setString(2,"小王");
            cs.setInt(3,5000);
            cs.setString(4,"http://yoloshine.com");
            cs.execute();
            conn.close();
            cs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

[2] 既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）

存储过程pro_jdbc_in_out_value的创建SQL语句如下：

--新建存储过程 pro_jdbc_in_out_value  既有输入IN参数，也有输出参数，输出是简单值（非列表）

CREATE OR REPLACE PROCEDURE pro_jdbc_in_out_value(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 tb_jdbc_procedure T
     WHERE T.SALARY >= V_SALARY;
  ELSE
    SELECT COUNT(*)
      INTO P_COUNT
      FROM tb_jdbc_procedure T
     WHERE T.SALARY >= V_SALARY
       AND T.USER_ID LIKE '%' || P_USERID || '%';
  END IF;
  DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);
END pro_jdbc_in_out_value;

Java调用代码如下：

public static void main(String[] args)   {
        String className = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "cobcmw";
        String password = "1234567890";
        /**
         * [2] 既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）；
         */
        Connection conn = null;
        CallableStatement cs = null;
        try {
            Class.forName(className).newInstance();
            conn = DriverManager.getConnection(url,user,password);
            //调用存储过程：统计薪水5000以上的人员的数量
            cs = conn.prepareCall("{ call pro_jdbc_in_out_value(?,?,?) }");
            cs.setString(1,"");
            cs.setDouble(2, 5000);
            //注意：注册的index和取值时要对应
            cs.registerOutParameter(3,Types.INTEGER);
            //执行存储过程
            cs.execute();
            // getXxx(index)中的index 需要和上面registerOutParameter的index对应
            int i = cs.getInt(3);
            System.out.println("符号条件的查询结果 count := " + i);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                if (null != cs) {
                    cs.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

[3] 既有输入IN参数，也有输出OUT参数，输出是列表

首先需要创建PACKAGE PKG_JDBC_PROCEDURE_CURSOR 的SQL如下：

CREATE OR REPLACE PACKAGE PKG_JDBC_PROCEDURE_CURSOR IS
  TYPE CURSOR_JDBC_PROCEDURE IS REF CURSOR;
END PKG_JDBC_PROCEDURE_CURSOR;

再创建存储过程 PRO_JDBC_IN_OUT_LIST 的SQL如下：

--新建存储过程PRO_JDBC_IN_OUT_LIST
CREATE OR REPLACE PROCEDURE PRO_JDBC_IN_OUT_LIST(P_SALARY IN NUMBER,
                                                 P_OUTRS  OUT PKG_JDBC_PROCEDURE_CURSOR.CURSOR_JDBC_PROCEDURE) IS
  V_SALARY NUMBER := P_SALARY;
BEGIN
  IF P_SALARY IS NULL THEN
    V_SALARY := 0;
  END IF;
  OPEN P_OUTRS FOR
    SELECT * FROM tb_jdbc_procedure T WHERE T.SALARY > V_SALARY;
END PRO_JDBC_IN_OUT_LIST;

Java调用部分的代码如下：

public static void main(String[] args)   throws  Exception {
        String className = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "cobcmw";
        String password = "1234567890";
        /**
         * [3] 有输入IN参数，也有输出OUT参数，输出是列表；
         */
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            Class.forName(className).newInstance();
            conn = DriverManager.getConnection(url,user,password);
            cs = conn.prepareCall("{call PRO_JDBC_IN_OUT_LIST(?,?)}");
            //输入参数
            cs.setDouble(1, 3000);
            //输出参数
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();
            // getXxx(index)中的index 需要和上面registerOutParameter的index对应
            rs = (ResultSet) cs.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);
            }

        } catch ( SQLException e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != rs) {
                rs.close();
            }
            if (null != cs) {
                cs.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

[4] 输入输出参数是同一个（IN OUT）

创建存储过程PRO_JDBC_IN_OUT的SQL如下：

--创建存储过程PRO_JDBC_IN_OUT
CREATE OR REPLACE PROCEDURE PRO_JDBC_IN_OUT(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 tb_jdbc_procedure
   WHERE USER_ID LIKE '%' || P_USERID || '%'
     AND SALARY >= V_SALARY;
  P_NUM := V_COUNT;
END PRO_JDBC_IN_OUT;

Java调用代码如下：

 public static void main(String[] args)   throws  Exception {
        String className = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "cobcmw";
        String password = "1234567890";
        /**
         * [4] 输入输出参数是同一个（IN OUT）；
         */
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            Class.forName(className).newInstance();
            conn = DriverManager.getConnection(url,user,password);
            cs = conn.prepareCall("{call PRO_JDBC_IN_OUT(?,?)}");
            cs.setString(1, "michael");
            cs.setDouble(2, 3000);
            // 注意此次注册out 的index 和上面的in 参数index 相同
            cs.registerOutParameter(2, Types.INTEGER);
            cs.execute();
            // getXxx(index)中的index 需要和上面registerOutParameter的index对应
            int count = cs.getInt(2);
            System.out.println("符号条件的查询结果 count := " + count);
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != cs) {
                cs.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }