Oracle 函數回傳 REF CURSOR 實作動態查詢

場景

需在 PL/SQL 中根據輸入參數動態組裝 SQL，並將結果集以游標形式回傳給呼叫端（Java、.NET 或另一 PL/SQL 區塊）。

完整 Package 範例

Package Spec

CREATE OR REPLACE PACKAGE pkg_test AS
  -- 弱類型 REF CURSOR，支援動態 SQL
  TYPE myrctype IS REF CURSOR;

  FUNCTION get(p_id IN NUMBER) RETURN myrctype;
END pkg_test;
/

Package Body

CREATE OR REPLACE PACKAGE BODY pkg_test AS
  FUNCTION get(p_id IN NUMBER) RETURN myrctype IS
    rc      myrctype;
    sqlstr  VARCHAR2(500);
  BEGIN
    IF p_id = 0 THEN
      -- 靜態查詢
      OPEN rc FOR
        SELECT id, name, sex, address, postcode, birthday
        FROM student;
    ELSE
      -- 動態 SQL：使用繫結變數 :w_id 防注入
      sqlstr := 'SELECT id, name, sex, address, postcode, birthday
                 FROM student WHERE id = :w_id';
      OPEN rc FOR sqlstr USING p_id;
    END IF;
    RETURN rc;
  END get;
END pkg_test;
/

呼叫方式

PL/SQL 匿名區塊

DECLARE
  rc   pkg_test.myrctype;
  v_id student.id%TYPE;
  v_nm student.name%TYPE;
BEGIN
  rc := pkg_test.get(123);
  LOOP
    FETCH rc INTO v_id, v_nm;
    EXIT WHEN rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_nm);
  END LOOP;
  CLOSE rc;
END;
/

Java (JDBC) 呼叫

CallableStatement cs = conn.prepareCall("{? = call pkg_test.get(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.setInt(2, 123);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next()) { ... }
rs.close();
cs.close();

關鍵點總結

• 弱類型 REF CURSOR（不指定 RETURN 型別）才能搭配動態 SQL；強類型則限定死查詢欄位結構。
• 動態 SQL 使用 USING 子句繫結變數，避免字串拼接導致 SQL 注入。
• 游標務必在用畢後 CLOSE，否則會造成游標洩漏（ORA-01000 maximum open cursors exceeded）。
• 12c 起可直接在 SQL 中呼叫回傳 CURSOR 的函數：
  SELECT * FROM TABLE(pkg_test.get(123))（需搭配 DBMS_SQL.RETURN_RESULT 或管道化函數）。