存储过程返回记录集的问题在论坛里有很多贴子讨论的。
下面有两个例子你参考一下吧。例子1：是弱水三千写的：
测试过程：
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.
下面有两个例子你参考一下吧。例子1：是弱水三千写的：
测试过程：
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.
如果在存储过程中不修改数据，则可以作成一个函数将结果返回，再pb中调用这个函数。
你在PB论坛中搜索一下以前的帖子，有好些关于调用存储过程的帖子，也写了不少参数传递的方法，但我没试通过，你可以试试。
from the ( select cast( testrerecordnotabname(1) as zmytableType )
from dual ) a;
比
select * from a;
数据量大的时候速度要慢很多
--------------------------------------------------
create or replace type myqueryType as object
(
line_nbr varchar2(20),
pri_line_nbr varchar2(20),
vn_id varchar2(20),
vn_nbr varchar2(20),
acc_exch_id varchar2(20),
security_password varchar2(20),
serv_cat_id varchar2(20),
so_nbr varchar2(20),
bill_type_id varchar2(20),
cust_id varchar2(20),
addr_id varchar2(20),
agent_id varchar2(20),
sts varchar2(20)
)
/
create or replace type myTableType as table of myqueryType
/
create or replace function demo_proc( serv_id_query in varchar2,
p_end_row in number )
return myTableType
as
l_data myTableType := myTableType();
l_cnt number default 0;
begin
for v in ( select distinct h_serv_acc_nbr.line_nbr line_nbr,h_serv_acc_nbr.pri_line_nbr pri_line_nbr,
h_serv_acc_nbr.vn_id vn_id,h_serv_acc_nbr.vn_nbr vn_nbr,
h_serv_acc_nbr.acc_exch_id acc_exch_id,
h_serv_acc_nbr.SECURITY_PASSWD security_password,
h_serv_acc_nbr.serv_cat_id serv_cat_id,
h_serv_acc_nbr.so_nbr so_nbr,
h_serv.bill_type_id bill_type_id,
h_serv_cust.cust_id cust_id,
h_serv_addr.addr_id addr_id,
h_serv_agent.agent_id agent_id,
h_serv.sts sts
from h_serv,h_serv_acc_nbr,h_serv_cust,h_serv_agent,h_serv_addr
where h_serv.serv_id = h_serv_acc_nbr.serv_id and
h_serv.serv_id = h_serv_cust.serv_id and
h_serv.serv_id = h_serv_agent.serv_id and
h_serv.serv_id = h_serv_addr.serv_id and
h_serv.serv_id = serv_id_query and
h_serv.seq = h_serv_acc_nbr.seq and
h_serv.seq = h_serv_cust.seq and
h_serv.seq = h_serv_agent.seq and
h_serv.seq = h_serv_addr.seq )
loop
l_cnt := l_cnt + 1;
if ( l_cnt >= 1 )
then
l_data.extend;
l_data(l_data.count) :=
myqueryType( v.line_nbr,
v.pri_line_nbr ,
v.vn_id ,
v.vn_nbr ,
v.acc_exch_id ,
v.security_password ,
v.serv_cat_id ,
v.so_nbr ,
v.bill_type_id ,
v.cust_id ,
v.addr_id ,
v.agent_id ,
v.sts );
end if;
exit when l_cnt =p_end_row;
--exit when l_cnt =l_data.count;
end loop;return l_data;
end;
/
/
select v.line_nbr,
v.pri_line_nbr ,
v.vn_id ,
v.vn_nbr ,
v.acc_exch_id ,
v.security_password ,
v.serv_cat_id ,
v.so_nbr ,
v.bill_type_id ,
v.cust_id ,
v.addr_id ,
v.agent_id ,
v.sts
from the ( select cast( demo_proc(:serv_id_query,10000) as mytableType )
from dual ) V;
/
select distinct h_serv_acc_nbr.line_nbr line_nbr,h_serv_acc_nbr.pri_line_nbr pri_line_nbr,
h_serv_acc_nbr.vn_id vn_id,h_serv_acc_nbr.vn_nbr vn_nbr,
h_serv_acc_nbr.acc_exch_id acc_exch_id,
h_serv_acc_nbr.SECURITY_PASSWD security_password,
h_serv_acc_nbr.serv_cat_id serv_cat_id,
h_serv_acc_nbr.so_nbr so_nbr,
h_serv.bill_type_id bill_type_id,
h_serv_cust.cust_id cust_id,
h_serv_addr.addr_id addr_id,
h_serv_agent.agent_id agent_id,
h_serv.sts sts
from h_serv,h_serv_acc_nbr,h_serv_cust,h_serv_agent,h_serv_addr
where h_serv.serv_id = h_serv_acc_nbr.serv_id and
h_serv.serv_id = h_serv_cust.serv_id and
h_serv.serv_id = h_serv_agent.serv_id and
h_serv.serv_id = h_serv_addr.serv_id and
h_serv.serv_id = serv_id_query and
h_serv.seq = h_serv_acc_nbr.seq and
h_serv.seq = h_serv_cust.seq and
h_serv.seq = h_serv_agent.seq and
h_serv.seq = h_serv_addr.seq )
;
/
这样不快啊~~~~~~~~~~~~~~
我要快~快~快~快~快~~~~~~~~~~~~~~~~~~~~~~！！！！！！！！！！！！！！！！！！！~~~~~~~~~~~~~