Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.