Oracle快速创建定时job执行批量转储过程脚本参考案例

-- 创建短信批量转储存储过程，将已经发送的短信转移至短信历史表

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;

 

-- 建立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;
/