Stored procedure return Array Object Sample code _ database Other

In fact, it is equivalent to return the list of object data, defined as follows

1. Create a stored Procedure object

Copy Code code as follows:

CREATE OR REPLACE TYPE "T_account_month"
As Object (
account_id number,
Init_amount number,
Debit_amount number,
Credit_amount number
)

2. Create an array of stored procedures

Copy Code code as follows:

CREATE OR REPLACE TYPE "T_account_month_table"
As Table of T_account_month

3. Create a stored procedure

Copy Code code as follows:

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 (trunc (D.create_time, ' year ')-trunc (Sysdate, ' year '), 0, decode (sign)
D.debit_unposted+d.debit_posted,
0)) Debit_amount,
Sum (trunc (D.create_time, ' year ')-trunc (Sysdate, ' year '), 0, decode (sign)
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