存储过程返回数组对象示例代码

其实就相当于返回List里面放的对象数据，定义如下1.创建存储过程对象

复制代码 代码如下:

CREATE OR REPLACE type "T_ACCOUNT_MONTH"as object(ACCOUNT_ID NUMBER,INIT_AMOUNT NUMBER,DEBIT_AMOUNT NUMBER,CRedIT_AMOUNT NUMBER)

2.创建存数过程数组

复制代码 代码如下:

CREATE OR REPLACE TYPE "T_ACCOUNT_MONTH_TABLE"as table of t_account_month

3.创建存储过程

复制代码 代码如下:

create or replace function account_month(tDate IN DATE)return t_account_month_table pipelinedasv_account_month t_account_month;v_date DATE;beginv_date:=tDate;IF v_date IS NULL TheNv_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_AMOUNTfrom ACCOUNT_DAILY_VEIW dgroup by d.ACCOUNT_ID) loopv_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;