Oracle 存储过程的使用
本文主要是总结 如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：
[1]、只有输入IN参数，没有输出OUT参数
[2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
[3]、既有输入IN参数，也有输出OUT参数，输出是列表
[4]、输入输出参数是同一个（IN OUT）
[5]、存储过程中 使用 truncate 截断表中的数据
【准备工作】
  创建一个测试表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); 
 

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参数

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参数，输出是简单值（非列表）

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 Michae