PL/SQL 返回記錄集的實作範例：使用 REF CURSOR 輸出結果

前言

在 Oracle PL/SQL 中，若需從預存程序返回查詢結果集給呼叫端（如 Java、.NET 或其他工具），常見的做法是使用 REF CURSOR（弱型別游標）。本文提供一個完整的範例，示範如何定義套件規格、宣告游標型別，以及在程序中開啟游標並輸出資料。

完整程式碼範例

CREATE OR REPLACE PACKAGE my_pack AS
  TYPE p_cursor IS REF CURSOR;
END my_pack;
/

CREATE OR REPLACE PROCEDURE server_check_report_pro(p_cursor OUT my_pack.p_cursor) IS
  vlevel NUMBER;
  vre    VARCHAR2(3000);
  CURSOR c1 IS
    SELECT * FROM server_dbcheck_list;
BEGIN
  -- 清空報表暫存表
  DELETE FROM server_check_report;

  -- 逐筆處理 server_dbcheck_list 中的每台伺服器
  FOR c IN c1 LOOP
    server_getlogbyip(c.ip, vre, vlevel);
    INSERT INTO server_check_report (ip, check_item, check_order, error_level, detail)
    VALUES (c.ip, 'OS DB Log Check', 0, vlevel, SUBSTR(vre, 1, 2999));
  END LOOP;

  -- 磁碟空間檢查
  INSERT INTO server_check_report (ip, check_item, check_order, error_level, detail)
  SELECT a.ip, 'Disk Spaces Check', 1, NVL(b.error_level, 0), NVL(b.remark, '')
  FROM server_dbcheck_list a, server_logcheck_logs b
  WHERE a.ip = b.ip(+)
    AND error_level(+) > 0
    AND id(+) = 4;

  -- 資料庫日誌檢查
  INSERT INTO server_check_report (ip, check_item, check_order, error_level, detail)
  SELECT a.ip,
         a.tns_name || ':' || b.LOG_INFO,
         2,
         NVL(b.error_level, 0),
         DECODE(NVL(b.error_level, 0), 0, '', REPLACE(REPLACE(b.value_list, '####', ''), '"', ''))
  FROM server_dbcheck_list a, server_dbcheck_logs b
  WHERE a.ip = b.ip(+)
    AND a.tns_name = b.tns_name(+)
    AND error_level(+) > 0
    AND b.id(+) = 1;

  COMMIT;

  -- 開啟游標並返回最終報表資料
  OPEN p_cursor FOR 'SELECT * FROM server_check_report ORDER BY ip, check_order';
END server_check_report_pro;
/

關鍵點解析

• 套件定義游標型別：在 my_pack 中宣告 TYPE p_cursor IS REF CURSOR;，使呼叫端可共用此弱型別游標定義。
• OUT 參數傳回游標：程序參數 p_cursor OUT my_pack.p_cursor 將開啟後的游標傳回給呼叫者。
• 暫存表累積資料：先將各項檢查結果寫入 server_check_report，最後一次性查詢並開啟游標，效能較好。
• 動態 SQL 開啟游標：使用 OPEN p_cursor FOR 'SELECT ...' 字串形式，靈活指定排序與欄位。

呼叫端範例（以 Java JDBC 為例）

CallableStatement cs = conn.prepareCall("{call server_check_report_pro(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next()) {
    // 處理每一列報表資料
}
rs.close();
cs.close();

常見應用場景

• 後台批次作業產生報表，前端分頁顯示。
• 多層架構中，資料庫層只負責查詢邏輯，應用層處理格式化。
• 避免將大量資料一次性傳回，利用游標流式處理。

此範例展示了企業級資料庫稽核系統中常見的「收集→彙總→輸出」模式，適合作為 REF CURSOR 實作的參考範本。