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);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16942186/viewspace-678139/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16942186/viewspace-678139/