Oracle中Table函数的使用
Oracle中Table函数的使用
--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));
select * from the(select f_test_pipe(20) from dual);
--引申,结何Table函数,可以实现简单的动态视图