将下列的存储过程做为例子:
create or replace procedure Account_datasync_Pro(
aSucc out integer,
aReason out varchar2
)
is
vSql varchar2(2000);
vCur sys_refcursor;
vAcct_No varchar2(20);
vPay_sum integer;
vLast_act_date date;
begin
vSql := 'select a.acct_no
,(case when sum(b.Payment) is null then 0 else sum(b.Payment) end) Pay_sum
,(case when max(b.Date_paid) is null then max(a.date_created) else max(b.Date_paid) end) Last_act_date
from Account a
left join Payment b
on a.acct_no = b.acct_no
group by a.Acct_No';
open vCur for vSql;
loop
fetch vCur into vAcct_No,vPay_sum,vLast_act_date;
exit when vCur%notfound;
vSql := 'update Account a set a.Pay_sum =:Pay_sum,a.Last_act_date =: Last_act_date where a.Acct_no = : Acct_No';
execute immediate vSql using vPay_sum,vLast_act_date,vAcct_no;
end loop;
close vCur;
exception
when others
then
dbms_output.put_line(sqlerrm);
end;
在左边的对象栏中,Procedures目录下,找到“Account_datasync_Pro”,右键,选择“测试”,如下图:
单击“放大镜”按钮,如下图,然后,点击“运行”按钮(三角型),再点击“单步进入”进行调试和查看变量。