Oracle 存儲過程嵌套調用返回游標：SYS_REFCURSOR 兩大場景完整實現

核心類型：SYS_REFCURSOR

Oracle 9i+ 引入弱類型游標變數 SYS_REFCURSOR，可指向任意查詢結果集，實現「過程生成游標、過程消費游標」的解耦架構。

場景一：返回完整表/視圖結構（%ROWTYPE 直接映射）

生產端：開啟游標返回全表

CREATE OR REPLACE PROCEDURE get_all_users(p_cursor OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cursor FOR SELECT * FROM users;
END;

消費端：%ROWTYPE 變數直接 FETCH

CREATE OR REPLACE PROCEDURE process_all_users AS
    v_cursor SYS_REFCURSOR;
    v_row    users%ROWTYPE;  -- 關鍵：錨定表結構
BEGIN
    get_all_users(v_cursor);
    LOOP
        FETCH v_cursor INTO v_row;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('User: ' || v_row.username || ', Email: ' || v_row.email);
    END LOOP;
    CLOSE v_cursor;
END;

場景二：返回自定義欄位子集（需定義匹配結構游標）

生產端：只返回 ID、NAME

CREATE OR REPLACE PROCEDURE get_user_id_name(p_cursor OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cursor FOR SELECT user_id, username FROM users;
END;

消費端：聲明「結構相同」的強類型游標做 %ROWTYPE

CREATE OR REPLACE PROCEDURE process_user_id_name AS
    v_cursor    SYS_REFCURSOR;
    -- 關鍵：定義一個「結構完全一致」的強類型游標，取其 %ROWTYPE
    CURSOR c_dummy IS SELECT user_id, username FROM users WHERE ROWNUM = 1;
    v_row c_dummy%ROWTYPE;
BEGIN
    get_user_id_name(v_cursor);
    LOOP
        FETCH v_cursor INTO v_row;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_row.user_id || ', Name: ' || v_row.username);
    END LOOP;
    CLOSE v_cursor;
END;

核心規則：結構嚴格匹配

• 消費端 FETCH 目標變數（Record/集合）的欄位數量、順序、資料類型必須與生產端 OPEN ... FOR SELECT ... 的選項完全一致。
• 不匹配拋出 ORA-06504: PL/SQL: Return types of Result Set variables or query do not match。
• c_dummy%ROWTYPE 技巧：避免手寫 TYPE rec IS RECORD(...)，自動同步 DDL 變更。

進階模式：參數化游標生產

CREATE OR REPLACE PROCEDURE get_users_by_dept(
    p_dept_id  IN NUMBER,
    p_cursor   OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT user_id, username, email
        FROM users
        WHERE dept_id = p_dept_id
        ORDER BY username;
END;

Java/.NET 客戶端消費

• JDBC：CallableStatement.registerOutParameter(idx, OracleTypes.CURSOR) → ResultSet rs = (ResultSet) cstmt.getObject(idx)。
• ODP.NET：OracleParameter.Direction = ParameterDirection.OutputReturnValue; OracleParameter.OracleDbType = OracleDbType.RefCursor。

最佳實踐

1. 統一使用 SYS_REFCURSOR 作為 OUT 參數，避免自定義 TYPE refcur IS REF CURSOR 增加依賴。
2. 生產端 OPEN FOR 後不關閉，由消費端負責 CLOSE（所有權轉移）。
3. 大結果集消費端使用 BULK COLLECT ... LIMIT 1000 分批處理，避免 PGA 爆漲。
4. 文檔化游標結構：在過程註解列出 SELECT 欄位順序與類型，便於消費端維護。