您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 高階程式設計：Java 呼叫預存程序與 REF CURSOR 實戰

編輯：Oracle 教學

前置知識連結

1. 單一回傳值預存程序（Java 呼叫）

String sql = "{call smsbusiness.deleteZhZMember(?,?,?)}";
Connection conn = DriverManager.getConnection(...);
CallableStatement cs = conn.prepareCall(sql);
cs.setInt(1, id);
cs.setInt(2, -2);
cs.registerOutParameter(3, Types.NUMERIC);
cs.execute();

2. 多值回傳：使用 REF CURSOR

資料庫端建立套件與程序

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE Test_CURSOR IS REF CURSOR;
END TESTPACKAGE;

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR OUT TESTPACKAGE.Test_CURSOR) IS
BEGIN
  OPEN p_CURSOR FOR SELECT * FROM grade;
END TESTC;

Java 呼叫取得 ResultSet

CallableStatement cs = conn.prepareCall("{call TESTC(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next()) { ... }

3. 無回傳值預存程序完整範例

資料庫端

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS
BEGIN
  INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2);
END TESTA;

Java 完整程式碼

public class TestProcedureOne {
  public static void main(String[] args) {
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    try (Connection conn = DriverManager.getConnection(url, "zl", "123456");
         CallableStatement proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }")) {
      proc.setString(1, "100");
      proc.setString(2, "TestOne");
      proc.execute();
    } catch (SQLException e) { e.printStackTrace(); }
  }
}

4. 基本回傳值預存程序

資料庫端

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) AS
BEGIN
  SELECT col INTO PARA2 FROM TESTTB WHERE I_ID = PARA1;
END TESTB;

Java 端註冊輸出參數並取值

CallableStatement proc = conn.prepareCall("{ call zl.TESTB(?,?) }");
proc.setString(1, "100");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String result = proc.getString(2);