-- 创建短信批量转储存储过程,将已经发送的短信转移至短信历史表
create or replace procedure PUB_SHORTMSG_ARCH_BAT
as
MSG_ID pub_shortmsg_td.shotmsg_id%type;
cursor cur_msg is
select shotmsg_id from pub_shortmsg_td a where a.send_flag!=0;
cur_rec cur_msg%rowtype;
begin
open cur_msg;
loop
fetch cur_msg into cur_rec;
exit when cur_msg%notfound;
MSG_ID := cur_rec.shotmsg_id;
insert into his_pub_shortmsg_td (select * from pub_shortmsg_td where shotmsg_id=MSG_ID);
delete pub_shortmsg_td where shotmsg_id=MSG_ID;
COMMIT;
end loop;
close cur_msg;
end PUB_SHORTMSG_ARCH_BAT;
as
MSG_ID pub_shortmsg_td.shotmsg_id%type;
cursor cur_msg is
select shotmsg_id from pub_shortmsg_td a where a.send_flag!=0;
cur_rec cur_msg%rowtype;
begin
open cur_msg;
loop
fetch cur_msg into cur_rec;
exit when cur_msg%notfound;
MSG_ID := cur_rec.shotmsg_id;
insert into his_pub_shortmsg_td (select * from pub_shortmsg_td where shotmsg_id=MSG_ID);
delete pub_shortmsg_td where shotmsg_id=MSG_ID;
COMMIT;
end loop;
close cur_msg;
end PUB_SHORTMSG_ARCH_BAT;
-- 建立job,每天凌晨1:30执行已发送短信的历史转储
VARIABLE job NUMBER;
begin
sys.dbms_job.submit(job => :job,
what => 'PUB_SHORTMSG_ARCH_BAT();',
next_date => to_date('30-08-2008 01:30:05', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1');
commit;
end;
/
begin
sys.dbms_job.submit(job => :job,
what => 'PUB_SHORTMSG_ARCH_BAT();',
next_date => to_date('30-08-2008 01:30:05', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1');
commit;
end;
/