在 Client/Server 架構下,ADO 通過 OLE DB Provider for Oracle 實現 VB6 等高級語言訪問 Oracle 存儲過程。本文以 PACKPERSON 包為例,演示無參數 REF CURSOR 返回集合與帶輸入參數 REF CURSOR 返回集合兩種模式的完整實現。
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;
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;
/
' 參考: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
PL/SQL Index-by Tables 綁定(OraOLEDB 9.2+ 支援)Recordset(Command.Execute 返回)Size 參數(最大元素數)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,無需手動遍歷參數。