使用 ADO (VB6) 訪問 Oracle 存儲過程：包、REF CURSOR 與參數綁定完整範例

背景

ADO (ActiveX Data Objects) 透過 OLE DB Provider 訪問 Oracle，物件模型簡潔 (Connection, Command, Recordset, Parameter, Field, Error)，適合 VB6/VBA/VBScript 開發。

資料庫端準備 (Oracle 端)

1. 建立測試表與資料

DROP TABLE person;
CREATE TABLE person (
  ssn NUMBER(9) PRIMARY KEY,
  fname VARCHAR2(15),
  lname VARCHAR2(20)
);
INSERT INTO person VALUES (555662222, 'Sam', 'Goodwin');
INSERT INTO person VALUES (555882222, 'Kent', 'Clark');
INSERT INTO person VALUES (666223333, 'Jane', 'Doe');
COMMIT;

2. 建立 Package 規範 (定義 REF CURSOR 類型與程序介面)

CREATE OR REPLACE PACKAGE packperson AS
  TYPE tssn   IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
  TYPE tfname IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
  TYPE tlname IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

  PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname);
  PROCEDURE oneperson (onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT tlname);
END packperson;
/

3. 建立 Package Body (實作邏輯，使用 PL/SQL 索引表模擬 REF CURSOR 行為)

CREATE OR REPLACE PACKAGE BODY packperson AS
  PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS
    CURSOR person_cur IS SELECT ssn, fname, lname FROM person;
    percount NUMBER := 1;
  BEGIN
    FOR r IN person_cur LOOP
      ssn(percount)   := r.ssn;
      fname(percount) := r.fname;
      lname(percount) := r.lname;
      percount := percount + 1;
    END LOOP;
  END;

  PROCEDURE oneperson (onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS
    CURSOR person_cur IS SELECT ssn, fname, lname FROM person WHERE ssn = onessn;
    percount NUMBER := 1;
  BEGIN
    FOR r IN person_cur LOOP
      ssn(percount)   := r.ssn;
      fname(percount) := r.fname;
      lname(percount) := r.lname;
      percount := percount + 1;
    END LOOP;
  END;
END packperson;
/

注意：此處使用 PL/SQL 索引表 (Associative Array) 作為 OUT 參數，而非 REF CURSOR。ADO 配合 MSDAORA/OLE DB 可直接將此類集合類型映射為 Recordset (需在連接字串指定 {resultset ...} 語法)。

VB6 客戶端程式碼

Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command  ' allperson
Dim CPw2 As ADODB.Command  ' oneperson
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long

Private Sub cmdGetEveryone_Click()
    Set Rs.Source = CPw1
    Rs.Open
    While Not Rs.EOF
        MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
        Rs.MoveNext
    Wend
    Rs.Close
End Sub

Private Sub cmdGetOne_Click()
    Set Rs.Source = CPw2
    inputssn = InputBox("Enter the SSN you wish to retrieve:")
    CPw2(0) = inputssn  ' 設定輸入參數
    Rs.Open
    MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
    Rs.Close
End Sub

Private Sub Form_Load()
    ' 連接字串：請替換 UID/PWD/SERVER
    Conn = "UID=*****;PWD=*****;Driver={Microsoft ODBC for Oracle};SERVER=dSEOracle;"
    Set Cn = New ADODB.Connection
    With Cn
        .ConnectionString = Conn
        .CursorLocation = adUseClient  ' 客戶端游標支援離線/複雜類型
        .Open
    End With

    '--- 命令 1：無參數 allperson ---
    QSQL = "{call packperson.allperson({resultset 9,ssn,fname,lname})}"
    Set CPw1 = New ADODB.Command
    With CPw1
        Set .ActiveConnection = Cn
        .CommandText = QSQL
        .CommandType = adCmdText
    End With

    '--- 命令 2：帶參數 oneperson ---
    QSQL = "{call packperson.oneperson(?,{resultset 2,ssn,fname,lname})}"
    Set CPw2 = New ADODB.Command
    With CPw2
        Set .ActiveConnection = Cn
        .CommandText = QSQL
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter(, adInteger, adParamInput)
    End With

    Set Rs = New ADODB.Recordset
    With Rs
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Cn.Close
    Set Cn = Nothing: Set CPw1 = Nothing: Set CPw2 = Nothing: Set Rs = Nothing
End Sub

關鍵技術點解析

1. 連接字串：使用 Microsoft ODBC for Oracle 驅動 (或 Oracle OLE DB Provider OraOLEDB.Oracle)。
2. CursorLocation = adUseClient：客戶端游標對複雜返回類型 (集合/REF CURSOR) 支援更好。
3. CommandText 語法：{call package.proc({resultset max_rows,col1,col2,...})} 是 MSDAORA 專有擴展，將 PL/SQL 索引表/REF CURSOR 映射為 ADO Recordset。max_rows 建議略大於預期行數。
4. 參數綁定：? 佔位符對應 CreateParameter，順序必須與存儲過程定義一致。
5. Recordset 重用：Set Rs.Source = CPw1/CPw2 切換數據源後 Rs.Open 即可。