存储过程返回数组对象示例代码

其实就相当于返回list里面放的对象数据，定义如下

1.创建存储过程对象

create or replace type "t_account_month"
as object(
account_id number,
init_amount number,
debit_amount number,
credit_amount number
)

2.创建存数过程数组

create or replace type "t_account_month_table"
as table of t_account_month

3.创建存储过程

create or replace function account_month(tdate in date)
return t_account_month_table pipelined
as
v_account_month t_account_month;
v_date date;
begin
v_date:=tdate;
if v_date is null then
v_date:=sysdate;
end if;
for myrow in (
select d.account_id,
sum(decode(sign(d.create_time-trunc(v_date,'month')),-1,
d.debit_unvoucher + d.debit_unposted +d.debit_posted - d.credit_unvoucher -d.credit_unposted- d.credit_posted_d,
0)) init_amount,
sum(decode(sign(trunc(d.create_time,'year')-trunc(sysdate,'year')),0,
d.debit_unposted+d.debit_posted,
0)) debit_amount,
sum(decode(sign(trunc(d.create_time,'year')-trunc(sysdate,'year')),0,
d.credit_unposted+d.credit_posted,
0)) credit_amount
from account_daily_veiw d
group by d.account_id
) loop
v_account_month := t_account_month(
myrow.account_id,
myrow.init_amount,
myrow.debit_amount,
myrow.credit_amount
);
pipe row (v_account_month);
end loop;
return;
end;