Oracle 存儲過程使用 REF CURSOR 返回遞歸查詢結果：WITH 子句與游標參數

需求

創建存儲過程，接收起始/目的鏈路 ID，返回收費路徑 (遞歸查詢)。

關鍵代碼

-- 包定義游標類型
CREATE OR REPLACE PACKAGE TOLL_PATH_PACKAGE IS
    TYPE REFCURSOR IS REF CURSOR;
END;

-- 存儲過程
CREATE OR REPLACE PROCEDURE TOLL_PATH_LENGTH (
    CUR_RET OUT TOLL_PATH_PACKAGE.REFCURSOR,
    IN_LINK IN NUMBER,   -- 起始
    OUT_LINK IN NUMBER   -- 目的
) IS
BEGIN
    OPEN CUR_RET FOR
    WITH T (LINK_PID, S_NODE_PID, E_NODE_PID, tollpath, LENGTH, lvl) AS (
        SELECT LINK_PID, S_NODE_PID, E_NODE_PID,
               '/' || LINK_PID AS tollpath,
               LENGTH, 1 AS lvl
        FROM rd_link123
        WHERE LINK_PID = IN_LINK
        UNION ALL
        SELECT r.LINK_PID, r.S_NODE_PID, r.E_NODE_PID,
               t.tollpath || '/' || r.LINK_PID,
               t.LENGTH + r.LENGTH,
               t.lvl + 1
        FROM T, rd_link123 r
        WHERE r.S_NODE_PID = t.E_NODE_PID
          AND IN_LINK != r.E_NODE_PID
          AND t.LINK_PID != OUT_LINK
          AND t.lvl < 400
          AND INSTR(t.tollpath, '/' || r.LINK_PID) = 0
    )
    SELECT LINK_PID, TO_CLOB(tollpath || '/') AS path, LENGTH
    FROM T
    WHERE LINK_PID = OUT_LINK;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

注意事項

• TO_CLOB 防止路徑字符串超過 4000 字節截斷。
• 遞歸深度限制 lvl < 400 防止無限循環。
• Java 調用時註冊 OracleTypes.CURSOR 類型參數。