萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

ADO 訪問 Oracle 存儲過程完整實戰:VB6 連接包、REF CURSOR 參數、PL/SQL 表類型映射

在 Client/Server 架構下,ADO 通過 OLE DB Provider for Oracle 實現 VB6 等高級語言訪問 Oracle 存儲過程。本文以 PACKPERSON 包為例,演示無參數 REF CURSOR 返回集合與帶輸入參數 REF CURSOR 返回集合兩種模式的完整實現。

資料庫端對象構建

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. 包規格與主體(含 PL/SQL 索引表類型)

CREATE OR REPLACE PACKAGE packperson AS
  TYPE t_ssn   IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
  TYPE t_fname IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
  TYPE t_lname IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

  PROCEDURE allperson (
    p_ssn   OUT t_ssn,
    p_fname OUT t_fname,
    p_lname OUT t_lname
  );
  PROCEDURE oneperson (
    p_onessn IN NUMBER,
    p_ssn    OUT t_ssn,
    p_fname  OUT t_fname,
    p_lname  OUT t_lname
  );
END packperson;
/

CREATE OR REPLACE PACKAGE BODY packperson AS
  PROCEDURE allperson (p_ssn OUT t_ssn, p_fname OUT t_fname, p_lname OUT t_lname) IS
  BEGIN
    FOR r IN (SELECT * FROM person ORDER BY ssn) LOOP
      p_ssn(p_ssn.COUNT+1)   := r.ssn;
      p_fname(p_fname.COUNT+1) := r.fname;
      p_lname(p_lname.COUNT+1) := r.lname;
    END LOOP;
  END;

  PROCEDURE oneperson (p_onessn IN NUMBER, p_ssn OUT t_ssn, p_fname OUT t_fname, p_lname OUT t_lname) IS
  BEGIN
    SELECT ssn, fname, lname
    INTO p_ssn(p_ssn.COUNT+1), p_fname(p_fname.COUNT+1), p_lname(p_lname.COUNT+1)
    FROM person WHERE ssn = p_onessn;
  EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
  END;
END packperson;
/

VB6/ADO 客戶端調用核心代碼

' 參考:Microsoft ActiveX Data Objects 2.x Library
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

With cmd
  .ActiveConnection = "Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=scott;Password=tiger;"
  .CommandType = adCmdStoredProc
  
  ' 調用無參數 allperson
  .CommandText = "PACKPERSON.ALLPERSON"
  .Parameters.Append .CreateParameter("P_SSN", adNumeric, adParamOutput, , , 100)
  .Parameters.Append .CreateParameter("P_FNAME", adVarChar, adParamOutput, 15, , 100)
  .Parameters.Append .CreateParameter("P_LNAME", adVarChar, adParamOutput, 20, , 100)
  .Execute
  
  ' 處理返回的 PL/SQL 索引表(需遍歷 Parameters 集合)
  ' 注意:ADO 無法直接將 PL/SQL 表映射為 Recordset,需循環讀取參數值
End With

關鍵技術點提示

  • Oracle OLE DB Provider 需支援 PL/SQL Index-by Tables 綁定(OraOLEDB 9.2+ 支援)
  • REF CURSOR 返回時,ADO 可直接獲取 Recordset(Command.Execute 返回)
  • PL/SQL 索引表作為 OUT 參數時,需預先指定 Size 參數(最大元素數)
  • 推薦改用 REF CURSOR 返回結果集,ADO 映射更自然:

改用 REF CURSOR 版包規格

CREATE OR REPLACE PACKAGE packperson AS
  TYPE t_cursor IS REF CURSOR;
  PROCEDURE allperson (p_rc OUT t_cursor);
  PROCEDURE oneperson (p_onessn IN NUMBER, p_rc OUT t_cursor);
END;

ADO 端即可 Set rs = cmd.Execute 直接獲取 Recordset,無需手動遍歷參數。

copyright © 萬盛學電腦網 all rights reserved