返回记录集的方法很多 一般长用的是定义表类型或纪录类型来返回比如使用TABLE()函数等,只要定义了一个相应的对象类型就好了PS: PL/SQL表---table()函数用法 /*PL/SQL表---table()函数用法: 利用table()函数,我们可以将PL/SQL返回的结果集代替table。simple example:1、table()结合数组:*/create or replace type t_test as object( id integer, rq date, mc varchar2(60) );create or replace type t_test_table as table of t_test;create or replace function f_test_array(n in number default null) return t_test_table as v_test t_test_table := t_test_table(); begin for i in 1 .. nvl(n,100) loop v_test.extend(); v_test(v_test.count) := t_test(i,sysdate,'mc'||i); end loop; return v_test; end f_test_array; /select * from table(f_test_array(10));/*2、table()结合PIPELINED函数:*/create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED as v_test t_test_table := t_test_table(); begin for i in 1 .. nvl(n,100) loop pipe row(t_test(i,sysdate,'mc'||i)); end loop; return; end f_test_pipe; /select * from table(f_test_pipe(20));/*3、table()结合系统包:*/create table test (id varchar2(20)); insert into test values('1'); commit; explain plan for select * from test; select * from table(dbms_xplan.display);
先建立create or replace type tb_index_type is table of varchar2(20)建立函数 或者过程,返回类型为刚才建立的 create or replace function Fun_test(v_d varchar2) return tb_index_type is Result tb_index_type ; begin ... end;
PL/SQL表---table()函数用法
/*PL/SQL表---table()函数用法:
利用table()函数,我们可以将PL/SQL返回的结果集代替table。simple example:1、table()结合数组:*/create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);create or replace type t_test_table as table of t_test;create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
end loop;
return v_test;
end f_test_array;
/select * from table(f_test_array(10));/*2、table()结合PIPELINED函数:*/create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
pipe row(t_test(i,sysdate,'mc'||i));
end loop;
return;
end f_test_pipe;
/select * from table(f_test_pipe(20));/*3、table()结合系统包:*/create table test (id varchar2(20));
insert into test values('1');
commit;
explain plan for select * from test;
select * from table(dbms_xplan.display);
sql server中的存储过程也只是在query analyzer中看的,无法操作存储过程就是存储过程它不可能有可操作的返回值,除非借助out参数把recordset拿出来用
create or replace function Fun_test(v_d varchar2) return tb_index_type is
Result tb_index_type ;
begin
...
end;