Oracle table function

Oracle table function

1. create object type

create or replace type ty_obj as object
(
    col01 varchar2(100),
    col02 varchar2(100),
    col03 varchar2(100),
    col04 varchar2(100),
    col05 varchar2(100),
    col06 varchar2(100),
    col07 varchar2(100),
    col08 varchar2(100),
    col09 varchar2(100),
    col10 varchar2(100)
)

2. create table type

create or replace type ty_tbl as table of ty_obj

3. create function

create or replace function sf_table
(
    ps_gubn1 varchar2,
    ps_gubn2 varchar2
)
 return ty_tbl
 is
    vs_rtn ty_tbl;
begin

    if upper(ps_gubn1) = 'TEST' then
       
        select ty_obj(a, b, c, '', '', '', '', '', '', '')
          bulk collect
          into vs_rtn
          from (select a,
                       b,
                       c
                  from (select '01' as a, '02' as b, '03' as c
                          from dual
                        union all
                        select '11' as a, '12' as b, '13' as c
                          from dual
                        union all
                        select '21' as a, '22' as b, '23' as c
                          from dual)
                );

    end if;
    return vs_rtn;
end;

4. select

select * From table(sf_table('test',''));

col01	col02	col03	col04	col05	col06	col07	col08	col09	col10
01	02	03
11	12	13
21	22	23