以前,记得有很多人,说ORACLE的存储过程,不能返回一个ADO的记录集给客户端。这个说法显然是错误的。
下面,给出个例子加以说明
1 ORACLE端建立一个存储包
CREATE OR REPLACE PACKAGE ado_callpkg AS
TYPE eid IS TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE ename IS TABLE of VARCHAR2(40) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename);
end ado_callpkg;
CREATE OR REPLACE PACKAGE BODY ado_callpkg AS
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename) IS
CURSOR c1 IS select employee_id,first_name||','||Middle_Initial||','||last_name as name from employee;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE;
BEGIN
open c1;
LOOP
FETCH c1 INTO c;
empname(cnt):=c.name;
empid(cnt):=c.employee_id;
EXIT WHEN c1%NOTFOUND; -- process the data
cnt :=cnt+1;
END LOOP;
close c1;
END;
end ado_callpkg;
2 客户端用ADO调用
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim str As String
str = "{call ado_callpkg.getEmpNames({resultset 100,empid,empname})}"
cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=ORACLE;Persist Security Info=True"
With cmd
.CommandText = str
.ActiveConnection = cn
.CommandType = adCmdText
End With
rs.CursorLocation = adUseClient
rs.Open cmd
Do While Not rs.EOF
你先创建包
CREATE OR REPLACE PACKAGE test001 IS
TYPE datasets IS REF CURSOR;
END test001;
然后在存储过程中引用它就行了如下:
Create Procedure sp_GetOrders
(
iCursor in out test001.datasets
)
as
tsql varchar2(2000);
begin
tsql := 'Select * from Orders';
OPEN rCursor FOR tSQL;
end;
这样就行了,可以得到执行tSQL后的一个结果集!
建立过程:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
CREATE PROCEDURE PRO_SLE(INPUT1 in integer,INPUT2 in VARCHAR2,p_rc out pkg_test.myrctype)
AS
begin
open p_rc for 'SELECT * FROM TEST WHERE AA='||INPUT1||' AND instr(BB,'||INPUT2||')>0';
end;
/
执行过程:
declare
v_num TEST_1.num%type;
v_name test_1.name%type;
v_sex test_1.sex%type;
v_major test_1.major%type;
v_rc pkg_test.myrctype;
begin
PRO_SLE(1,'1',v_rc);
loop
fetch v_rc into v_num,v_name,v_sex,v_major;
exit when v_rc%notfound;
dbms_output.put_line(v_num||v_name||v_sex||v_major);
end loop;
end;