SQL> select * from aa; ID NAME ---------- -------- 1 猪八戒 2 孙悟空 CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype) IS sqlstr VARCHAR2 (50); BEGIN sqlstr:='select * from aa'; OPEN p_rc FOR sqlstr; END get; / 在窗体上放一个ADOStoredproc 设置 procedurename=get prepared = true active = true 还有 connectstring 代码如下,好运! procedure TForm1.Button1Click(Sender: TObject); var mm : string; var nn : integer; begin mm:='myid'; nn:=2; ADOStoredProc1.Close; ADOStoredProc1.Parameters.Clear; ADOStoredProc1.Parameters.CreateParameter(mm,ftstring,Pdinput,0,0); --这个就是参数 adostoredproc1.Parameters.ParamByName(mm).value:=nn; --设置参数值 adostoredproc1.open; label1.caption:= adostoredproc1.fields[1].asstring; while not adostoredproc1.eof do begin label2.caption:= adostoredproc1.fields[1].asstring; adostoredproc1.Next; end; end;
SQL> select * from aa; ID NAME ---------- -------- 1 猪八戒 2 孙悟空 CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype) IS sqlstr VARCHAR2 (50); BEGIN sqlstr:='select * from aa'; OPEN p_rc FOR sqlstr; END get; / 在窗体上放一个ADOStoredproc 设置 procedurename=get prepared = true active = true 还有 connectstring 代码如下,好运! procedure TForm1.Button1Click(Sender: TObject); var mm : string; var nn : integer; begin mm:='myid'; nn:=2; ADOStoredProc1.Close; ADOStoredProc1.Parameters.Clear; ADOStoredProc1.Parameters.CreateParameter(mm,ftstring,Pdinput,0,0); --这个就是参数 adostoredproc1.Parameters.ParamByName(mm).value:=nn; --设置参数值 adostoredproc1.open; label1.caption:= adostoredproc1.fields[1].asstring; while not adostoredproc1.eof do begin label2.caption:= adostoredproc1.fields[1].asstring; adostoredproc1.Next; end; end;
CREATE OR REPLACE PACKAGE ZHT_TESTAS TYPE crTmp IS REF CURSOR ; TYPE m is TABLE of varchar2(21) INDEX BY BINARY_INTEGER; PROCEDURE ff(mm out m); procedure gg(crtemp out crTmp) ; end; / CREATE OR REPLACE PACKAGE BODY "ZHT_TEST"as procedure ff(mm out m) AS kk3 varchar(13); crtemp crTmp; a2 varchar2(20); a1 varchar2(20); percount NUMBER DEFAULT 1; BEGIN open crtemp for select f1,f2 from temptable ; Loop fetch crtemp into a2,a1;
EXIT WHEN crtemp%NOTFOUND; mm(percount):=a1; percount := percount + 1; end loop;
END; procedure gg(crtemp out crTmp) as --crtemp crTmp; a1 varchar2(20); a2 varchar2(20); BEGIN open crtemp for select * from temptable;
我的疑问 ,是需要游标型参数的地方怎么传参数?????????????????希望能给祥细的代码!!!
---------- --------
1 猪八戒
2 孙悟空
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/ create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype)
IS
sqlstr VARCHAR2 (50);
BEGIN
sqlstr:='select * from aa';
OPEN p_rc FOR sqlstr;
END get;
/ 在窗体上放一个ADOStoredproc
设置 procedurename=get
prepared = true
active = true
还有 connectstring
代码如下,好运! procedure TForm1.Button1Click(Sender: TObject);
var mm : string;
var nn : integer;
begin
mm:='myid';
nn:=2;
ADOStoredProc1.Close;
ADOStoredProc1.Parameters.Clear;
ADOStoredProc1.Parameters.CreateParameter(mm,ftstring,Pdinput,0,0); --这个就是参数
adostoredproc1.Parameters.ParamByName(mm).value:=nn; --设置参数值
adostoredproc1.open;
label1.caption:= adostoredproc1.fields[1].asstring;
while not adostoredproc1.eof do
begin
label2.caption:= adostoredproc1.fields[1].asstring;
adostoredproc1.Next;
end;
end;
---------- --------
1 猪八戒
2 孙悟空
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/ create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype)
IS
sqlstr VARCHAR2 (50);
BEGIN
sqlstr:='select * from aa';
OPEN p_rc FOR sqlstr;
END get;
/ 在窗体上放一个ADOStoredproc
设置 procedurename=get
prepared = true
active = true
还有 connectstring
代码如下,好运! procedure TForm1.Button1Click(Sender: TObject);
var mm : string;
var nn : integer;
begin
mm:='myid';
nn:=2;
ADOStoredProc1.Close;
ADOStoredProc1.Parameters.Clear;
ADOStoredProc1.Parameters.CreateParameter(mm,ftstring,Pdinput,0,0); --这个就是参数
adostoredproc1.Parameters.ParamByName(mm).value:=nn; --设置参数值
adostoredproc1.open;
label1.caption:= adostoredproc1.fields[1].asstring;
while not adostoredproc1.eof do
begin
label2.caption:= adostoredproc1.fields[1].asstring;
adostoredproc1.Next;
end;
end;
CREATE OR REPLACE PACKAGE ZHT_TESTAS TYPE crTmp IS REF CURSOR ;
TYPE m is TABLE of varchar2(21)
INDEX BY BINARY_INTEGER;
PROCEDURE ff(mm out m);
procedure gg(crtemp out crTmp) ;
end;
/
CREATE OR REPLACE PACKAGE BODY "ZHT_TEST"as
procedure ff(mm out m)
AS
kk3 varchar(13);
crtemp crTmp;
a2 varchar2(20);
a1 varchar2(20);
percount NUMBER DEFAULT 1; BEGIN open crtemp for select f1,f2 from temptable ;
Loop
fetch crtemp into a2,a1;
EXIT WHEN crtemp%NOTFOUND;
mm(percount):=a1;
percount := percount + 1;
end loop;
END;
procedure gg(crtemp out crTmp)
as
--crtemp crTmp;
a1 varchar2(20);
a2 varchar2(20);
BEGIN open crtemp for select * from temptable;
end;
END;
/中间层:
ADOCn1->ConnectionString="Provider=MSDAORA.1;Password=xrmbauser;User ID=xrmbauser;Data Source=jennydb;Persist Security Info=True";
注意:provider一定要用微软的(msdaora)
客户端:CData->Close();
CData->CommandText="{call zht_test.gg({resultset 0,crTemp})}";
CData->Open();这样,调用没有问题,但是可能会造成内存泄漏,我正在测试中。