初学者小问题,散分! 返回游标就可以了TYPE t_CurRef IS REF CURSOR;FUNCTION functionname(p1....) RETURN t_CurRef; 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 还是先看看“弱水三千”的例子吧:测试过程:1、建立测试表CREATE TABLE student ( id NUMBER, name VARCHAR2(30), sex VARCHAR2(10), address VARCHAR2(100), postcode VARCHAR2(10), birthday DATE, photo LONG RAW )/2、建立带ref cursor定义的包和包体及函数:CREATE OR REPLACEpackage pkg_test as/* 定义ref cursor类型 不加return类型,为弱类型,允许动态sql查询, 否则为强类型,无法使用动态sql查询;*/ type myrctype is ref cursor; --函数申明 function get(intID number) return myrctype;end pkg_test;/CREATE OR REPLACEpackage 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;/3、用pl/sql块进行测试:declare w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果 w_id student.id%type; w_name student.name%type; w_sex student.sex%type; w_address student.address%type; w_postcode student.postcode%type; w_birthday student.birthday%type;begin --调用函数,获得记录集 w_rc := pkg_test.get(1); --fetch结果并显示 fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday; dbms_output.put_line(w_name);end;4、测试结果:通过。下面是我自己写的两个例子:--创建测试表:SQL> create table a (id number,name varchar2(50),doctime date);Table created.--插入六条测试数据:SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row created.SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row created.SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row created.SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));1 row created.SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));1 row created.SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.--创建两个typeSQL> create or replace type myobjectype as object (x int,y date,z varchar2(50)); 2 /Type created.SQL> create or replace type mytabletype as table of myobjectype 2 /Type created.--创建可以返回纪录集的函数(不传入表名参数)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;Commit complete.--创建可以返回纪录集的函数(可以传入表名参数)SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number) 2 return mytabletype 3 as 4 l_data mytabletype :=mytabletype(); 5 strsql varchar2(50); 6 type v_cursor is ref cursor; 7 v_tempcursor v_cursor; 8 i1 number; 9 i2 varchar2(50); 10 i3 date; 11 begin 12 strsql := 'select * from ' || tablename || ' where id>=' || tableid; 13 open v_tempcursor for strsql; 14 loop 15 fetch v_tempcursor into i1,i2,i3; 16 l_data.extend; 17 l_data(l_data.count) := myobjectype(i1,i3,i2); 18 exit when v_tempcursor%NOTFOUND; 19 end loop; 20 return l_data; 21 end; 22 /Function created.SQL> commit;Commit complete.--测试不传表名参数的function(testrerecorenotabname)SQL> set serveroutput on SQL> declare 2 testre mytabletype :=mytabletype(); 3 i number :=0; 4 begin 5 testre := testrerecordnotabname(1); 6 loop 7 i := i+1; 8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';'); 9 exit when i = testre.count; 10 end loop; 11 end; 12 /;1;01-7?? -02;aaa;;2;02-7?? -02;bbb;;3;03-7?? -02;ccc;;4;04-7?? -02;ddd;;5;05-7?? -02;eee;;6;06-7?? -02;fff;PL/SQL procedure successfully completed.--测试传表名参数的function(testrerecoretabname)SQL> set serveroutput on SQL> declare 2 testre mytabletype :=mytabletype(); 3 i number :=0; 4 begin 5 testre := testrerecordtabname('a',1); 6 loop 7 i := i+1; 8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';'); 9 exit when i = testre.count; 10 end loop; 11 end; 12 /;1;01-7?? -02;aaa;;2;02-7?? -02;bbb;;3;03-7?? -02;ccc;;4;04-7?? -02;ddd;;5;05-7?? -02;eee;;6;06-7?? -02;fff;;6;06-7?? -02;fff;PL/SQL procedure successfully completed. oracle用户权限问题-新人求救 PFILE与SPFILE的区别 escape wildcard in between and operator 怎样从Utl_File.Fopen打开的文件中读入long raw? 向oracle数据库中添加汉字时出现乱码 REDHAT 9 下安装ORACLE9I错误 很急!!! 请教如何恢复oracle数据库 简单问题! 请看我的PRO*C作品! 请问是否有办法在sql server建立视图,视图资料来自于Oracle 那里可以下到oracle8i/9i的软件,而且下载速度快一点的。 怎样设定 DBLINK 的连接时间? 即timeout!
测试过程:
1、建立测试表
CREATE TABLE student
(
id NUMBER,
name VARCHAR2(30),
sex VARCHAR2(10),
address VARCHAR2(100),
postcode VARCHAR2(10),
birthday DATE,
photo LONG RAW
)
/2、建立带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;
/3、用pl/sql块进行测试:
declare
w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果
w_id student.id%type;
w_name student.name%type;
w_sex student.sex%type;
w_address student.address%type;
w_postcode student.postcode%type;
w_birthday student.birthday%type;begin
--调用函数,获得记录集
w_rc := pkg_test.get(1); --fetch结果并显示
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
dbms_output.put_line(w_name);
end;4、测试结果:
通过。下面是我自己写的两个例子:
--创建测试表:
SQL> create table a (id number,name varchar2(50),doctime date);Table created.--插入六条测试数据:
SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row created.SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row created.SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row created.SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));1 row created.SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));1 row created.SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.--创建两个type
SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
2 /Type created.SQL> create or replace type mytabletype as table of myobjectype
2 /Type created.--创建可以返回纪录集的函数(不传入表名参数)
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;Commit complete.--创建可以返回纪录集的函数(可以传入表名参数)
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 strsql varchar2(50);
6 type v_cursor is ref cursor;
7 v_tempcursor v_cursor;
8 i1 number;
9 i2 varchar2(50);
10 i3 date;
11 begin
12 strsql := 'select * from ' || tablename || ' where id>=' || tableid;
13 open v_tempcursor for strsql;
14 loop
15 fetch v_tempcursor into i1,i2,i3;
16 l_data.extend;
17 l_data(l_data.count) := myobjectype(i1,i3,i2);
18 exit when v_tempcursor%NOTFOUND;
19 end loop;
20 return l_data;
21 end;
22 /Function created.SQL> commit;Commit complete.--测试不传表名参数的function(testrerecorenotabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordnotabname(1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed.--测试传表名参数的function(testrerecoretabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordtabname('a',1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed.