用 Java 執行 Oracle 預儲程序 (Calling Oracle Stored Procedure / Function in Java)

Oracle Stored Procedure 與 Function 有個最大的相異處就是，Oracle Function 必須/一定有 Return 值，執行後就會把 Return 值丟出來，Return 值可以是任何的 Type，甚至是 Oracle Object Type 都是可行的。而 Oracle Stored Procedure，則由參數的 IN/OUT 性質來定義/控制的輸出入方向，所以或許它根本不會 Return 任何的東西出來，只有單純的呼叫與執行。

一個 Oracle Stored Procedure / Function 可能沒有任何的參數(parameters), 也可能需要傳入參數(IN parameters)，或許也有傳出參數(OUT parameters)，也或許該參數身兼傳入傳出的性質(IN/OUT parameters)。這幾種 Oracle 參數性質的相關知識與特性，有機會再行介紹。

在 Java 中叫用的時候，大概只要搞得清楚這參數是傳進去的還是傳出來的，暫時就足夠了。

• Oracle 資料庫連結及 Java API CallableStatement
  
• 在 Java 中呼叫 Stored Procedure
  
• 在 Java 中呼叫 Oracle Function

Oracle 資料庫連結及 Java API CallableStatement

1. 在 Java 中連結 Oracle 資料庫：
   這很基本，基本到我應該不會專門寫個文來討論它，就在這順道帶一下就好。

   Connection connection = null;
   try {
   // 載入 JDBC driver
   Class.forName("oracle.jdbc.driver.OracleDriver");
   
   // 連結到資料庫
   String dbHost = "127.0.0.1";
   String dbPort = "1521";
   String dbSID  = "oraSID";
   String dbUser = "username";
   String dbPswd = "password";
   connection = DriverManager.getConnection("jdbc:oracle:thin:@" + dbHost + ":" + dbPort + ":" + dbSID, dbUser, dbPswd);
   } catch (ClassNotFoundException e) {
   // 找不到 JDBC Driver
   } catch (SQLException e) {
   // 無法連結 Oracle 資料庫
   }
   

   如果，你的問題是去哪下載 JDBC Driver ，可以來這裡看看。
   
2. Java API CallableStatement：
   這兒的要角是 CallableStatement，如果想先認識一下這個 API，可以參考這裡。
   大概先記下這些『內功心法』就夠用了：
   
   • 用 connectionInstance.prepareCall 來設定 CallableStatement
     callableStatementInstance = connectionInstance.prepareCall("{叫用的 SQL 指令}");
     
   • 只要含有 OUT 性質的，用 CallableStatement.registerOutParameter 來設定
     所謂含 OUT 性質者包含了 OUT, IN/OUT 及 Oracle Function 的 Return 值。
     callableStatementInstance.registerOutParameter (Index, TypeConstant);
     
   • 只要含有 IN 性質的，用 CallableStatement.setDataType 來設定
     至於有哪幾種 DataType 可以使用，請詳見 API reference。
     callableStatementInstance.setDataType(Index, 傳入值);
     
   • 只要含有 OUT 性質的，用 CallableStatement.getDataType 來取值
     至於有哪幾種 DataType 可以使用，請詳見 API reference。
     String outValue = callableStatementInstance.getDataType(Index);
     

在 Java 中呼叫 Stored Procedure：

1. 呼叫沒有沒有任何的參數的 Oracle Stored Procedure：

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{call myproc}");
   
   // 執行 CallableStatement
   cs.execute();
   } catch (SQLException e) {
   }
   

2. 呼叫有一個 IN 參數的 Oracle Stored Procedure：

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{call myprocin(?)}");
   
   // 設定 IN 參數的 Index 及值
   cs.setString(1, "傳入的字串");
   
   // 執行 CallableStatement
   cs.execute();
   } catch (SQLException e) {
   }
   

   
   
3. 呼叫有一個 OUT 參數的 Oracle Stored Procedure：
   

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{call myprocout(?)}");
   
   // 定義 OUT 參數的 Index 與型態
   cs.registerOutParameter(1, Types.VARCHAR);
   
   // 執行並取回 OUT 參數值
   cs.execute();
   String outParam = cs.getString(1);    // OUT 參數值
   } catch (SQLException e) {
   }
   

   
   
4. 呼叫有一個 IN/OUT 參數的 Oracle Stored Procedure：
   

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{call myprocinout(?)}");
   
   // 定義 IN/OUT 參數的 Index 與型態
   cs.registerOutParameter(1, Types.VARCHAR);
   
   // 設定 IN/OUT 參數的 Index 及值
   cs.setString(1, "傳入的字串");
   
   // 執行並取回 IN/OUT 參數值
   cs.execute();
   String outParam = cs.getString(1);           // IN/OUT 參數值
   } catch (SQLException e) {
   }
   

在 Java 中呼叫 Oracle Function：

1. 呼叫沒有沒有任何的參數的 Oracle Function (Return VARCHAR)：
   

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{? = call myfunc}");
   
   // 定義 Return 值的 Index 與型態
   cs.registerOutParameter(1, Types.VARCHAR);
   
   // 執行並取回 Return 值
   cs.execute();
   String retValue = cs.getString(1);
   } catch (SQLException e) {
   }
   

   
   
2. 呼叫有一個 IN 參數的 Oracle Function (Return VARCHAR)：
   

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{? = call myfuncin(?)}");
   
   // 定義 Return 值的 Index 與型態
   cs.registerOutParameter(1, Types.VARCHAR);
   
   // 設定 IN 參數的 Index 及值
   cs.setString(2, "傳入的字串");
   
   // 執行並取回 Return 值
   cs.execute();
   String retValue = cs.getString(1);
   } catch (SQLException e) {
   }
   

   
   
3. 呼叫有一個 OUT 參數的 Oracle Function (Return VARCHAR)：
   

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{? = call myfuncout(?)}");
   
   // 定義 Return 值, 及 OUT 參數的 Index 與型態
   cs.registerOutParameter(1, Types.VARCHAR);
   cs.registerOutParameter(2, Types.VARCHAR);
   
   // 執行並取回 Return 值及 OUT 參數值
   cs.execute();
   String retValue = cs.getString(1);    // Return 值
   String outParam = cs.getString(2);    // OUT 參數值 
   } catch (SQLException e) {
   }
   

   
   
4. 呼叫有一個 IN/OUT 參數的 Oracle Function (Return VARCHAR)：
   

   CallableStatement cs;
   try {
   // 設定 CallableStatement
   cs = connection.prepareCall("{? = call myfuncinout(?)}");
   
   // 定義 Return 值, 及 IN/OUT 參數的 Index 與型態
   cs.registerOutParameter(1, Types.VARCHAR);
   cs.registerOutParameter(2, Types.VARCHAR);
   
   // 設定 IN/OUT 參數的 Index 及值
   cs.setString(2, "傳入的字串");
   
   // 執行並取回 Return 值及 IN/OUT 參數值
   cs.execute();
   String retValue = cs.getString(1);    // Return 值
   String outParam = cs.getString(2);    // IN/OUT 參數值
   } catch (SQLException e) {
   }