存储过程返回数组对象示例代码
<p>其实就相当于返回List里面放的对象数据，定义如下<br><br>
1.创建存储过程对象</p>
<p class="codetitle">
<span><u>复制代码</u></span> 代码如下:</p>
<p class="codebody">
<br>
CREATE OR REPLACE TYPE "T_ACCOUNT_MONTH"<br>
as object(<br>
ACCOUNT_ID NUMBER,<br>
INIT_AMOUNT NUMBER,<br>
DEBIT_AMOUNT NUMBER,<br>
CREDIT_AMOUNT NUMBER<br>
)</p>
<p>
<br>
2.创建存数过程数组</p>
<p class="codetitle">
<span><u>复制代码</u></span> 代码如下:</p>
<p class="codebody">
<br>
CREATE OR REPLACE TYPE "T_ACCOUNT_MONTH_TABLE"<br>
as table of t_account_month</p>
<p>
<br>
3.创建存储过程</p>
<p class="codetitle">
<span><u>复制代码</u></span> 代码如下:</p>
<p class="codebody">
<br>
create or replace function account_month(tDate IN DATE)<br>
return t_account_month_table pipelined<br>
as<br>
v_account_month t_account_month;<br>
v_date DATE;<br>
begin<br>
v_date:=tDate;<br>
IF v_date IS NULL THEN<br>
v_date:=sysdate;<br>
END IF;<br>
for myrow in (<br>
select d.ACCOUNT_ID,<br>
sum(decode(sign(d.create_time-trunc(v_date,'month')),-1,<br>
d.debit_unvoucher + d.debit_unposted +d.debit_posted - d.CREDIT_UNVOUCHER -d.CREDIT_UNPOSTED- d.CREDIT_POSTED_D,<br>
0)) INIT_AMOUNT,<br>
sum(decode(sign(trunc(d.create_time,'year')-trunc(sysdate,'year')),0,<br>
d.debit_unposted+d.debit_posted,<br>
0)) DEBIT_AMOUNT,<br>
sum(decode(sign(trunc(d.create_time,'year')-trunc(sysdate,'year')),0,<br>
d.credit_unposted+d.credit_posted,<br>
0)) CREDIT_AMOUNT<br>
from ACCOUNT_DAILY_VEIW d<br>
group by d.ACCOUNT_ID<br>
) loop<br>
v_account_month := t_account_month(<br>
myrow.ACCOUNT_ID,<br>
myrow.INIT_AMOUNT,<br>
myrow.DEBIT_AMOUNT,<br>
myrow.CREDIT_AMOUNT<br>
);<br>
pipe row (v_account_month);<br>
end loop;<br>
return;<br>
end;</p>
頁:
[1]