Oracle存儲過程返回結果集：REF CURSOR實戰詳解

Oracle存儲過程返回結果集：REF CURSOR實戰詳解

Oracle存儲過程通過REF CURSOR類型輸出參數返回結果集。本文詳解包聲明、過程定義、Delphi調用三個層面的完整實現。

核心機制

• 無法直接在過程參數聲明REF CURSOR，需先在包中定義類型
• 輸出參數模式必須為OUT或IN OUT
• 過程內使用OPEN cursor FOR SELECT ...綁定查詢結果

1. 定義游標類型包

CREATE OR REPLACE PACKAGE FuxjPackage IS
  TYPE FuxjResultSet IS REF CURSOR;
  -- 可擴展其他公共類型/常量
END FuxjPackage;
/

2. 定義返回結果集的存儲過程

更新操作返回狀態

CREATE OR REPLACE PROCEDURE UpdatefuxjExample (
  sDM    IN  CHAR,
  sMC    IN  CHAR,
  pRecCur IN OUT FuxjPackage.FuxjResultSet
) AS
BEGIN
  UPDATE fuxjExample SET mc = sMC WHERE dm = sDM;
  
  IF SQL%ROWCOUNT = 0 THEN
    ROLLBACK;
    OPEN pRecCur FOR SELECT '0' AS res FROM dual;  -- 失敗標識
  ELSE
    COMMIT;
    OPEN pRecCur FOR SELECT '1' AS res FROM dual;  -- 成功標識
  END IF;
END;
/

插入操作返回完整數據

CREATE OR REPLACE PROCEDURE InsertfuxjExample (
  sDM    IN  CHAR,
  sMC    IN  CHAR,
  pRecCur IN OUT FuxjPackage.FuxjResultSet
) AS
BEGIN
  INSERT INTO FuxjExample (dm, mc) VALUES (sDM, sMC);
  COMMIT;
  OPEN pRecCur FOR SELECT * FROM FuxjExample;  -- 返回全表數據
END;
/

3. Delphi客戶端調用

使用TStoredProc或TQuery組件，關鍵參數設置：

• DataType = ftCursor
• ParamType = ptInputOutput

TStoredProc配置示例

object StoredProc1: TStoredProc
  DatabaseName = 'UseProc'
  StoredProcName = 'UPDATEFUXJEXAMPLE'
  ParamData = <
    item
      DataType = ftString
      Name = 'sDM'
      ParamType = ptInput
    end
    item
      DataType = ftString
      Name = 'sMC'
      ParamType = ptInput
    end
    item
      DataType = ftCursor
      Name = 'pRecCur'
      ParamType = ptInputOutput
      Value = Null
    end
  >
end

運行時代碼

StoredProc1.ParamByName('sDM').AsString := 'DM001';
StoredProc1.ParamByName('sMC').AsString := '新名稱';
StoredProc1.ExecProc;  -- 執行過程

// 獲取結果集
var
  ds: TDataSet;
begin
  ds := StoredProc1.ParamByName('pRecCur').AsDataSet;
  if ds <> nil then
  begin
    ds.First;
    while not ds.Eof do
    begin
      ShowMessage(ds.FieldByName('res').AsString);
      ds.Next;
    end;
  end;
end;

進階模式：強/弱類型REF CURSOR

弱類型（通用）

TYPE WeakRefCur IS REF CURSOR;  -- 無返回結構定義

靈活但編譯期無類型檢查。

強類型（推薦）

TYPE EmpRec IS RECORD (
  empno NUMBER(4),
  ename VARCHAR2(20),
  sal   NUMBER(7,2)
);
TYPE StrongRefCur IS REF CURSOR RETURN EmpRec;  -- 綁定記錄結構

編譯期類型安全，IDE智能提示友好。

最佳實踐

1. 統一包管理：所有REF CURSOR類型集中在公共包中
2. 強類型優先：明確返回結構時使用強類型
3. 文檔化：說明返回結果集欄位、順序、業務含義
4. 異常處理：過程內捕獲異常，打開游標返回錯誤信息而非拋出
5. 資源管理：客戶端用畢須關閉游標/數據集，避免游標泄漏
6. 分頁查詢：大結果集配合ROWNUM或OFFSET/FETCH(12c+)分頁返回

總結

REF CURSOR是Oracle存儲過程返回結果集的標準機制。通過「包定義類型→過程輸出參數→OPEN FOR綁定查詢→客戶端ftCursor接收」四步流程，實現數據庫邏輯與應用層的高效解耦。掌握強/弱類型選擇、異常處理、資源管理細節，是構建健壯數據訪問層的關鍵。