How to realize that stored procedures return array objects
This article focuses on "how to achieve stored procedures to return array objects", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to implement stored procedures to return array objects".
In fact, it is equivalent to returning the object data put in List, which is defined as follows
1. Create a stored procedure object
The copy 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
)
two。 Create an array of stored procedures
The copy code is as follows:
CREATE OR REPLACE TYPE "T_ACCOUNT_MONTH_TABLE"
As table of t_account_month
3. Create a stored procedure
The copy 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)),-1
D.debit_unvoucher + d.debit_unposted + d.debit_posted-d.CREDIT_UNVOUCHER-d.CREDITY UNPOSTEDD-d.CREDIT_POSTED_D
0) INIT_AMOUNT
Sum (decode (sign (trunc (d.createkeeper timebook yearbook)-trunc (sysdate,'year')), 0
D.debit_unposted+d.debit_posted
0) DEBIT_AMOUNT
Sum (decode (sign (trunc (d.createkeeper timebook yearbook)-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
At this point, I believe you have a deeper understanding of "how to realize the stored procedure to return array objects". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
