Oracle中Table函数的使用

do5dl7ein9

--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函数，可以实现简单的动态视图