Oracle 8i/7.3 動態游標寫法差異：REF CURSOR 與 DBMS_SQL 對比

背景

Oracle 7.3 不支持原生動態游標（OPEN cur FOR sql_str），必須使用 DBMS_SQL 包實現動態 SQL。Oracle 8i 引入 REF CURSOR（弱/強類型游標變量），極大簡化了動態查詢開發。本文通過同一業務邏輯的兩個版本存儲過程，直觀對比兩種寫法差異。

業務場景

票據統計合並：根據窗口 ID、用戶 ID，從 bill_tj_out 讀取數據，按票據類型、起止號排序後合並插入 bill_tj_out_a 中間表。

版本一：Oracle 8i 原生動態游標（推薦）

CREATE OR REPLACE PROCEDURE pro_remain_merge_win (
    p_orgid_win  IN VARCHAR2,
    p_userid     IN VARCHAR2
) IS
    TYPE cur_t IS REF CURSOR;  -- 弱類型游標
    cur_src  cur_t;
    cur_tgt  cur_t;

    v_sql_src  VARCHAR2(3000);
    v_sql_tgt  VARCHAR2(3000);

    -- 接收字段變量
    v_bill_id       NUMBER;
    v_bill_type_id  NUMBER;
    v_bill_sign     VARCHAR2(12);
    v_start_no      NUMBER;
    v_end_no        NUMBER;
    v_all_num       NUMBER;
    v_bill_time     DATE;
BEGIN
    -- 1. 獲取窗口票據類型、車站 ID
    SELECT NVL(b.billtypeid, 0)
      INTO v_bill_type_id
      FROM bill_org o, bill_wintobilltype b
     WHERE o.org_id = b.winid
       AND o.org_id = p_orgid_win;

    SELECT NVL(o.parent_id, 0)
      INTO v_orgid_zhan
      FROM bill_org o
     WHERE o.org_id = p_orgid_win;

    -- 2. 構建動態查詢 SQL
    v_sql_src := 'SELECT bill_id, bill_typeid, bill_sign, start_no, end_no, all_num, bill_time '
                 || 'FROM bill_tj_out WHERE userid = :uid ORDER BY bill_sign, start_no, end_no';

    v_sql_tgt := 'SELECT bill_id, bill_typeid, bill_sign, start_no, end_no, all_num, bill_time '
                 || 'FROM bill_tj_out_a WHERE userid = :uid ORDER BY bill_sign, start_no';

    -- 3. 打開游標並綁定變量
    OPEN cur_src FOR v_sql_src USING p_userid;
    OPEN cur_tgt FOR v_sql_tgt USING p_userid;

    -- 4. 迴圈處理邏輯（略）
    -- LOOP FETCH cur_src INTO ...; EXIT WHEN cur_src%NOTFOUND; ... END LOOP;

    CLOSE cur_src;
    CLOSE cur_tgt;
END;
/

版本二：Oracle 7.3 DBMS_SQL 兼容寫法

CREATE OR REPLACE PROCEDURE pro_remain_merge_win_73 (
    p_orgid_win  IN VARCHAR2,
    p_userid     IN VARCHAR2
) IS
    v_cur_src  INTEGER := DBMS_SQL.OPEN_CURSOR;
    v_cur_tgt  INTEGER := DBMS_SQL.OPEN_CURSOR;
    v_dummy    INTEGER;

    v_sql_src  VARCHAR2(3000);
    v_sql_tgt  VARCHAR2(3000);
BEGIN
    -- 同樣獲取參數、構建 SQL（略）

    -- 1. 解析 SQL
    DBMS_SQL.PARSE(v_cur_src, v_sql_src, DBMS_SQL.NATIVE);
    DBMS_SQL.PARSE(v_cur_tgt, v_sql_tgt, DBMS_SQL.NATIVE);

    -- 2. 綁定變量
    DBMS_SQL.BIND_VARIABLE(v_cur_src, ':uid', p_userid);
    DBMS_SQL.BIND_VARIABLE(v_cur_tgt, ':uid', p_userid);

    -- 3. 定義輸出列（需逐列定義）
    DBMS_SQL.DEFINE_COLUMN(v_cur_src, 1, v_bill_id);
    DBMS_SQL.DEFINE_COLUMN(v_cur_src, 2, v_bill_type_id);
    -- ... 其餘列同理

    -- 4. 執行並循環取數
    v_dummy := DBMS_SQL.EXECUTE(v_cur_src);
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cur_src) = 0;
        DBMS_SQL.COLUMN_VALUE(v_cur_src, 1, v_bill_id);
        DBMS_SQL.COLUMN_VALUE(v_cur_src, 2, v_bill_type_id);
        -- ... 取值處理
    END LOOP;

    -- 5. 關閉游標
    DBMS_SQL.CLOSE_CURSOR(v_cur_src);
    DBMS_SQL.CLOSE_CURSOR(v_cur_tgt);
END;
/

核心差異總結

遷移建議

1. 優先重寫為 REF CURSOR，利用 SYS_REFCURSOR 內置弱類型簡化聲明。
2. 若涉及 DBMS_SQL 才能做的操作（如動態 DDL、描述未知結果集），保留混用。
3. 注意游標變量作為 OUT 參數跨進程傳遞時，客戶端驅動需支持（ODP.NET、JDBC 均支持）。

理解兩種動態游標機制的演進，有助於在遺留系統改造與新開發中做出正確技術選型。