建立带ref cursor定义的包和包体及函数: CREATE OR REPLACE package pkg_test as /* 定义ref cursor类型 不加return类型,为弱类型,允许动态sql查询, 否则为强类型,无法使用动态sql查询; */ type myrctype is ref cursor; --函数申明 function get(intID number) return myrctype; end pkg_test; /CREATE OR REPLACE package body pkg_test as --函数体 function get(intID number) return myrctype is rc myrctype; --定义ref cursor变量 sqlstr varchar2(500); begin if intID=0 then --静态测试,直接用select语句直接返回结果 open rc for select id,name,sex,address,postcode,birthday from student; else --动态sql赋值,用:w_id来申明该变量从外部获得 sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; --动态测试,用sqlstr字符串返回结果,用using关键词传递参数 open rc for sqlstr using intid; end if; return rc; end get;end pkg_test; /
SQL> create or replace type mytabletype as table of myobjectype 2 / --创建可以返回纪录集的函数(不传入表名参数) SQL> create or replace function testrerecordnotabname (tableid in number) 2 return mytabletype 3 as 4 l_data mytabletype :=mytabletype(); 5 begin 6 for i in (select * from a where id>=tableid) loop 7 l_data.extend; 8 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name); 9 exit when i.id = 62; 10 end loop; 11 return l_data; 12 end; 13 /Function created.SQL> commit;以上转载OTN網,beckham發表
type myrctype is ref cursor;create function f_SicknessCheck(tt in varchar2) returns myrctype as sqlstr varchar2(4000); rc myrctype; begin sqlstr := 'select * from student '; open rc for sqlstr ;end f_SicknessCheck;
type myrctype is ref cursor; create function f_SicknessCheck(tt in varchar2) return myrctype is sqlstr varchar2(4000); rc myrctype; begin sqlstr :='select * from TParameterMedicare'; open rc for sqlstr; return(rc); end;提示无效的sql语句!
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
不加return类型,为弱类型,允许动态sql查询,
否则为强类型,无法使用动态sql查询;
*/
type myrctype is ref cursor; --函数申明
function get(intID number) return myrctype;
end pkg_test;
/CREATE OR REPLACE
package body pkg_test as
--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试,直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值,用:w_id来申明该变量从外部获得
sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试,用sqlstr字符串返回结果,用using关键词传递参数
open rc for sqlstr using intid;
end if; return rc;
end get;end pkg_test;
/
2 /
--创建可以返回纪录集的函数(不传入表名参数)
SQL> create or replace function testrerecordnotabname (tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 begin
6 for i in (select * from a where id>=tableid) loop
7 l_data.extend;
8 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
9 exit when i.id = 62;
10 end loop;
11 return l_data;
12 end;
13 /Function created.SQL> commit;以上转载OTN網,beckham發表
returns myrctype
as
sqlstr varchar2(4000);
rc myrctype;
begin
sqlstr := 'select * from student '; open rc for sqlstr ;end f_SicknessCheck;
create function f_SicknessCheck(tt in varchar2)
return myrctype
is
sqlstr varchar2(4000);
rc myrctype;
begin
sqlstr :='select * from TParameterMedicare';
open rc for sqlstr;
return(rc);
end;提示无效的sql语句!