Sample Code of the array object returned by the Stored Procedure

The returned array object in the stored procedure is equivalent to returning the object data stored in the List. The following is an example. If you are interested, you can learn it.

Actually, it is equivalent to returning the object data stored in the List. The definition is as follows:

1. Create a stored procedure object

Copy codeThe Code is 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 codeThe Code is as follows:
Create or replace type "T_ACCOUNT_MONTH_TABLE"
As table of t_account_month

3. Create a stored procedure

Copy codeThe Code is 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 (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;