1.建立类型
create or replace type R_EXP_CAT as object
(
EXPENDITURE_CATEGORY VARCHAR2(30),
LABOR_FLAG NUMBER(1,0),
NONLABOR_TYPE NUMBER(1,0)
);
2.建立Collection
create or replace type T_EXP_CAT as table of R_EXP_CAT;
3.建立函数
create or replace function GET_EXP(ntype in integer)
return t_exp_cat pipelined
is
v r_exp_cat;
begin
for r in (select expenditure_category,labor_flag,nonlabor_type
from exp_cat
where labor_flag= ntype) loop
v:= r_exp_cat(r.expenditure_category,r.labor_flag,r.nonlabor_type);
pipe row(v);
end loop;
return;
end;
4.调用
select * from table(GET_EXP(1));