帮忙解释下下面的SQL语句什么意思(分数献上)

wx358165717 2010-04-20 06:58:29

在电信的同学说这个是视图,但是不懂,请有学问的各位大神来解释下
谢谢

create or replace procedure coll.coll_stop3_accounts_proc
/*
author: xuyuqing
date: 2006.12.26
use for: to get the accounts who have been suspended over three month,
the data will be provided to print center
*/
is
v_control_seq number;
v_account_no number(15);
v_crm_complete_date date;
v_detail_seq number(11);
v_total_cnt number;
v_cnt number;
v_flag number;
v_msg number;
v_err varchar2(500);

cursor get_stop_cur is
select to_number(a.account_no),b.crm_complete_date
from coll_suspended_list a,coll_suspended_cmd_log b
where a.suspended_seq=b.seq
and a.suspended_type=12
and b.crm_complete_code='0'
and a.closed_date is null
group by a.account_no,b.crm_complete_date
having to_char(add_months(max(b.crm_complete_date),3),'yyyymm')<to_char(sysdate,'yyyymm');
begin
select coll_program_control_log_seq.nextval into v_control_seq from dual;
insert into coll_program_control_log
(run_seq,
program_name,
run_start_time,
status)
values (v_control_seq,
'COLL_STOP3_ACCOUNTS_PROC',
sysdate,
0);

execute immediate 'truncate table bl_stopoverlimit_req';
delete from bl_stopoverlimit_req@spt11;
commit;

v_total_cnt:=0;
if get_stop_cur%isopen then
close get_stop_cur;
end if;
open get_stop_cur;
loop
fetch get_stop_cur into v_account_no,v_crm_complete_date;
exit when get_stop_cur%notfound;

select count(*) into v_cnt
from bl_fee_summary_local@spt11
where dbd_detail_seq in (select detail_seq
from bl_device_business_detail@spt11
where are_req_id=v_account_no
and business_close_date is null
and bty_business_type_id=1)
and to_char(fee_generate_date,'yyyymm')=to_char(sysdate,'yyyymm')
and amount_due>0;
if v_cnt>0 then
v_flag:=0; -- in use
else
v_flag:=1;-- not in use
end if;
insert into bl_stopoverlimit_req (req_id,flag,stop_date)
values (v_account_no,v_flag,v_crm_complete_date);

v_total_cnt:=v_total_cnt+1;
end loop;
close get_stop_cur;

update coll_program_control_log
set run_end_time = sysdate,
run_counts = v_total_cnt,
status = 1
where run_seq = v_control_seq;
commit;
insert into bl_stopoverlimit_req@spt11 select * from bl_stopoverlimit_req;
commit;
exception when others then
v_msg := sqlcode;
v_err := sqlerrm;
update coll_program_control_log
set run_end_time = sysdate,
run_counts = v_total_cnt,
status = v_msg,
err_desc = v_err
where run_seq = v_control_seq;
commit;
end;

...全文