Stored procedure return Array Object sample code

The stored procedure returns an array object, which is equivalent to returning the object data in the list, and here is an example of a friend who is interested in learning to

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 (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;