vb.net 存储过程 返回结果集 存储过程返回数组

其实就相当于返回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 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;