VB 呼叫 Oracle 返回 REF CURSOR 存儲過程完整實戰 (ADO/ADO.NET 對比)

Oracle 端：套件定義與實作

1. 套件規格 (Package Spec)

CREATE OR REPLACE PACKAGE PKG_GETQUERY IS
  TYPE CUR_REF IS REF CURSOR;  -- 定義弱型游標類型
  
  PROCEDURE PROC_GETQUERYSTUDINFO (
    STRKEY     IN  VARCHAR2,
    CUR_RESULT OUT CUR_REF
  );
END PKG_GETQUERY;
/

2. 套件主體 (Package Body)

CREATE OR REPLACE PACKAGE BODY PKG_GETQUERY IS
  PROCEDURE PROC_GETQUERYSTUDINFO (
    STRKEY     IN  VARCHAR2,
    CUR_RESULT OUT CUR_REF
  ) IS
    STRSQL VARCHAR2(1000);
  BEGIN
    STRSQL := 'SELECT * FROM STUDINFO WHERE STUDNAME LIKE ''' || STRKEY || '%''';
    OPEN CUR_RESULT FOR STRSQL;  -- 關鍵：動態開啟游標
  END;
END PKG_GETQUERY;
/

VB6 / VBA (ADO) 呼叫實作

關鍵難點：ADO 不直接支援 adParamOutput + Oracle REF CURSOR，需透過 Command.Execute 直接返回 Recordset。

Private Sub Command1_Click()
  Dim conn As New ADODB.Connection
  Dim cmd As New ADODB.Command
  Dim rs As New ADODB.Recordset
  Dim strConn As String
  
  ' 連線字串 (MSDAORA / OraOLEDB.Oracle)
  strConn = "Provider=MSDAORA.1;Password=****;User ID=scott;Data Source=ORCL;Persist Security Info=True"
  
  conn.ConnectionString = strConn
  conn.CursorLocation = adUseClient  ' 關鍵：客戶端游標支援 REF CURSOR
  conn.Open
  
  Set cmd.ActiveConnection = conn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "SCOTT.PKG_GETQUERY.PROC_GETQUERYSTUDINFO"
  
  ' 參數 1：輸入字串
  Dim param1 As New ADODB.Parameter
  Set param1 = cmd.CreateParameter("STRKEY", adVarChar, adParamInput, 1000, "張")
  cmd.Parameters.Append param1
  
  ' 參數 2：輸出 REF CURSOR - ADO 不需顯式宣告，直接 Execute 取得 Recordset
  ' 注意：嘗試用 adUserDefined 會失敗
  
  Set rs = cmd.Execute  ' 直接返回 Recordset
  
  If rs.RecordCount > 0 Then
    MsgBox "筆數: " & rs.RecordCount
    Set DataGrid1.DataSource = rs
    DataGrid1.Refresh
  Else
    MsgBox "查無資料"
  End If
  
  ' 清理
  rs.Close
  conn.Close
  Set rs = Nothing
  Set conn = Nothing
End Sub

ADO 關鍵設定說明

C# (ADO.NET / ODP.NET) 呼叫實作 - 現代標準寫法

using Oracle.ManagedDataAccess.Client;
using System.Data;

public DataTable ExecuteRefCursorProc(string procName, Hashtable inParams, string refCursorParamName)
{
  DataTable dt = new DataTable();
  string connStr = "User Id=scott;Password=tiger;Data Source=ORCL;";
  
  using (OracleConnection conn = new OracleConnection(connStr))
  using (OracleCommand cmd = new OracleCommand(procName, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    
    // 輸入參數
    foreach (DictionaryEntry entry in inParams)
    {
      cmd.Parameters.Add(entry.Key.ToString(), entry.Value);
    }
    
    // 輸出 REF CURSOR 參數 - 關鍵：OracleType.Cursor
    OracleParameter refCursor = new OracleParameter(refCursorParamName, OracleDbType.RefCursor);
    refCursor.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(refCursor);
    
    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
    {
      da.Fill(dt);
    }
  }
  return dt;
}

// 呼叫範例
Hashtable ht = new Hashtable();
ht.Add("STRKEY", "張");
DataTable result = ExecuteRefCursorProc("SCOTT.PKG_GETQUERY.PROC_GETQUERYSTUDINFO", ht, "CUR_RESULT");

進階：強型別 REF CURSOR (SYS_REFCURSOR)

-- Oracle 9i+ 內建弱型別，無需自訂 TYPE
PROCEDURE GET_EMPS (P_DEPTNO IN NUMBER, P_CURSOR OUT SYS_REFCURSOR);

-- 對應 C# 參數型別同為 OracleDbType.RefCursor

效能與最佳實踐

• 游標關閉：ADO.NET using 區塊自動處理；VB6 需顯式 rs.Close
• 大量資料：考慮分頁 FETCH FIRST N ROWS ONLY (12c+) 或 ROWNUM 限制
• 連線池：ADO.NET 預設啟用連線池 (Pooling=true)，VB6 需手動管理或使用 MTS/COM+
• 例外處理：Oracle 端 EXCEPTION WHEN OTHERS THEN ... 記錄錯誤日誌

遷移建議：VB6 → .NET

1. 上一篇文章： WebLogic 連線池整合 Oracle + Servlet + 無狀態 SessionBean 完整實戰
2. 下一篇文章： Ubuntu 安裝 Oracle Client 10g/11g/12c/19c 完整指南：環境準備、亂碼解決、驗證測試

Oracle 資料庫基礎

Oracle 11g 閃回資料歸檔 (FDA) 深度解析：從原理到實戰

前言：為什麼需要 FDA？ 傳統方案需自建歷史表 + 觸發器

遭遇大規模 Oracle 壞塊？資深 DBA 教你正確應對策略

前言：技術必須回歸業務場景 近期業界熱談「場景化運維」與「場

C# OracleParameter 錯誤「No size set for variable length data type」解決方案

使用 OracleDataAccess.Client 或 S

Oracle ORA-00257 故障實戰排查：Flash Recovery Area 空間耗盡根因分析與擴容解決方案

故障現象 環境：HP ProLiant DL580 G4