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

Oracle 進階 PL/SQL 技巧：參數化游標、REF CURSOR 與動態 SQL 實戰

編輯：Oracle 資料庫基礎

一、參數化游標：構建可復用查詢模塊

參數化游標將查詢邏輯與輸入參數解耦，提升代碼復用性與安全性（防注入）。

語法結構

CURSOR cursor_name (param_name datatype) IS
  SELECT ... WHERE column = param_name;

完整範例：按部門查詢員工

DECLARE
  v_deptno  emp.deptno%TYPE;
  v_empno   emp.empno%TYPE;
  v_ename   emp.ename%TYPE;
  
  -- 定義帶參數游標
  CURSOR emp_cur (p_deptno NUMBER) IS
    SELECT empno, ename FROM emp WHERE deptno = p_deptno;
BEGIN
  v_deptno := &部門編號;  -- SQL*Plus 替換變量
  
  OPEN emp_cur(v_deptno);
  LOOP
    FETCH emp_cur INTO v_empno, v_ename;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' - ' || v_ename);
  END LOOP;
  CLOSE emp_cur;
END;
/
-- 執行時提示輸入：Enter value for 部門編號: 10

二、 REF CURSOR：動態結果集的通用接口

REF CURSOR 是指向游標的指針，允許運行時決定查詢邏輯，是構建通用報表引擎、異構數據交換的核心。

強類型 vs 弱類型

強類型：TYPE ref_cur_type IS REF CURSOR RETURN emp%ROWTYPE; 編譯期檢查結構。
弱類型：TYPE ref_cur_type IS REF CURSOR; 運行期綁定，靈活性最高。

實戰：根據用戶選擇動態查詢員工或部門

ACCEPT v_choice PROMPT '查詢類型 (E-員工 / D-部門): ';

DECLARE
  TYPE ref_cur_t IS REF CURSOR;  -- 弱類型
  v_cur     ref_cur_t;
  v_id      NUMBER;
  v_name    VARCHAR2(100);
  v_sel     VARCHAR2(1) := UPPER(SUBSTR('&v_choice', 1, 1));
BEGIN
  IF v_sel = 'E' THEN
    OPEN v_cur FOR SELECT empno, ename FROM emp;
    DBMS_OUTPUT.PUT_LINE('=== 員工列表 ===');
  ELSIF v_sel = 'D' THEN
    OPEN v_cur FOR SELECT deptno, dname FROM dept;
    DBMS_OUTPUT.PUT_LINE('=== 部門列表 ===');
  ELSE
    DBMS_OUTPUT.PUT_LINE('輸入錯誤');
    RETURN;
  END IF;

  LOOP
    FETCH v_cur INTO v_id, v_name;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('#' || v_id || ': ' || v_name);
  END LOOP;
  CLOSE v_cur;
END;
/
-- 交互式輸入 E 或 D，動態返回不同結果集

三、動態 SQL (EXECUTE IMMEDIATE / DBMS_SQL)：運行時構建 SQL

適用於：表名/列名變量化、DDL 執行、條件動態拼接。

核心語法：EXECUTE IMMEDIATE ... USING ... RETURNING INTO ...

USING：綁定輸入參數（防注入、提升解析復用）
RETURNING INTO：捕獲 DML 影響行數或 DDL 結果

實戰：動態查詢高薪員工

VARIABLE v_maxsal NUMBER;
EXEC :v_maxsal := 2500;

DECLARE
  v_emp     emp%ROWTYPE;
  TYPE cur_t IS REF CURSOR;
  v_cur     cur_t;
  v_sal     NUMBER := :v_maxsal;
BEGIN
  -- 動態 SQL 中使用綁定變量 :1 對應 USING v_sal
  OPEN v_cur FOR 'SELECT * FROM emp WHERE sal > :1 ORDER BY sal DESC' USING v_sal;
  
  DBMS_OUTPUT.PUT_LINE('薪資 > ' || v_sal || ' 的員工：');
  LOOP
    FETCH v_cur INTO v_emp;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('編號:' || v_emp.empno || ' 姓名:' || v_emp.ename || ' 薪資:' || v_emp.sal);
  END LOOP;
  CLOSE v_cur;
END;
/
-- 輸出薪資大於 2500 的員工明細

進階：DBMS_SQL 封裝通用動態查詢函數

CREATE OR REPLACE FUNCTION run_dynamic_query(p_sql VARCHAR2) RETURN SYS_REFCURSOR IS
  v_cur  SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR p_sql;
  RETURN v_cur;
END;
/
-- 調用
DECLARE
  v_rc SYS_REFCURSOR;
  v_rec emp%ROWTYPE;
BEGIN
  v_rc := run_dynamic_query('SELECT * FROM emp WHERE deptno = 20');
  LOOP
    FETCH v_rc INTO v_rec;
    EXIT WHEN v_rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.ename);
  END LOOP;
  CLOSE v_rc;
END;
/
-- 注意：生產環境嚴防 SQL 注入，必須驗證/清洗 p_sql