create package test_age as type mycursor is ref cursor; end; / create procedure pro(p_rc out test_age.mycursor) as open p_rc for select ...; end; /
问题是我的SELECT 是也是存储过程的一个参数,是动态执行的,直接用游标返回好象不对
A very simple samples:create or replace package types as type rc is ref cursor; end; /create or replace procedure FetchCount(iLowVal in number, iHighVal in number, TableName in varchar2, sColumn in varchar2, sOtherCon in varchar2, iRetCount in out number) IS --参数: 返回记录数 p_cursor types.rc ; BEGIN
open p_cursor for ' Select count(*) from ' || TableName || ' where ' || sColumn || ' >= :iLowVal ' || ' and ' || sColumn || ' <= :iHighVal ' || Nvl(sOtherCon,'') using iLowVal, iHighVal; fetch p_cursor into iRetCount; if p_cursor%notfound then iRetCount:=-1; dbms_output.put_line(' Can not fetch recordcount from ' || TableName); else dbms_output.put_line(' Ok! recordcount is ' || iRetCount); end if;
close p_cursor; EXCEPTION WHEN OTHERS THEN iRetCount:=-1; End; /
as
type mycursor is ref cursor;
end;
/
create procedure pro(p_rc out test_age.mycursor)
as
open p_rc for select ...;
end;
/
as type rc is ref cursor;
end;
/create or replace procedure FetchCount(iLowVal in number,
iHighVal in number,
TableName in varchar2,
sColumn in varchar2,
sOtherCon in varchar2,
iRetCount in out number)
IS
--参数: 返回记录数
p_cursor types.rc ;
BEGIN
open p_cursor for ' Select count(*) from ' || TableName || ' where ' ||
sColumn || ' >= :iLowVal ' || ' and ' || sColumn ||
' <= :iHighVal ' || Nvl(sOtherCon,'')
using iLowVal, iHighVal;
fetch p_cursor into iRetCount;
if p_cursor%notfound
then
iRetCount:=-1;
dbms_output.put_line(' Can not fetch recordcount from ' || TableName);
else
dbms_output.put_line(' Ok! recordcount is ' || iRetCount);
end if;
close p_cursor; EXCEPTION WHEN OTHERS THEN
iRetCount:=-1;
End;
/