Oracle中的table()函数使用

create or replace type type_name as object (
        v_name1 datatype [ ,v_name2 datatype,... ],
        [ member | static method1 spec, member | static method2 spec , ... ]
);
-- type_name是对象类型的名称；
-- v_name是属性名称；
-- datatype是属性数据类型；
-- method是方法的名称；

create or replace type body type_name as
    member | static method1 body;
    member | static method1 body;...
-- type_name是对象类型的名称；
-- method是方法的名称；
-- member | static 见上表格

create or replace type table_name as table of type_name;
--table_name 表类型名称
--type_name 对象类型名称

create or replace function function_name ([p1,p2...pn]) return table_name
as
v_test table_name := table_name();
begin
...
end loop;
return v_test;
end function_name;
-- function_name 函数名称
-- p1,p2...pn 函数入参
-- table_name 表函数名称

select * from table(function_name(20));
或者
select * from the(select function_name(20) from dual);
--function_name 定义好的表函数名称

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));
或
select * from the(select f_test_array(10) from dual);

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(10));
或
select * from the(select f_test_pipe(10) from dual);

create table test (id varchar2(20),mc varchar2(20));

insert into test values('1','mc1');
commit;

explain plan for select * from test;

select * from table(dbms_xplan.display);