java中调用存储过程【附带案例】-windows ad域的作用-程序博客网

如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：
[1]、只有输入IN参数，没有输出OUT参数
[2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
[3]、既有输入IN参数，也有输出OUT参数，输出是列表
[4]、输入输出参数是同一个（IN OUT）

【准备工作】

  创建一个测试表TMP_MICHAEL ，并插入数据，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);

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

java中调用存储过程【附带案例】

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