oracle定时删库,PL/SQL编写定时Job Oracle定时删除

1. 要做什么

如何使用Oracle存储过程结合定时Job来达到定时删除指定数据库表数据的目的。

2. 大致过程

新建两张测试表格

编写insert存储过程和delete存储过程

测试存储过程

编写insert定时Job和delete定时Job

测试定时Job

3. 参考文章

4. 准备工作

使用PL/SQL Developer作为连接和开发工具。

使用SYS账户以dba身份登录。

使用CRXJ_COLLECT作为当前用户模式。

两张表名：crxj_collect.TEST_TABLE; crxj_collect.TEST_TABLE_2;

每10秒执行Job的Interval写法：sysdate+ 10/(24*60*60)

每60秒执行Job的Interval写法：sysdate+ 60/(24*60*60)

5. 建测试表

-- 创建 crxj_collect.test_table 表

create table crxj_collect.test_table

(

seq number(8) primary key,

seqtime date

);

-- 创建 crxj_collect.test_table_2 表

create table crxj_collect.test_table_2

(

seq number(8) primary key,

seqtime date

);

6. 创建存储过程

-- 创建insert存储过程

create or replace procedure crxj_collect.test_insert_proc is

begin

insert into crxj_collect.test_table(seq, seqtime)

values(NVL((SELECT MAX(seq) FROM crxj_collect.test_table) +1, 0),sysdate);

insert into crxj_collect.test_table_2(seq, seqtime)

values(NVL((SELECT MAX(seq) FROM crxj_collect.test_table) +2, 0),sysdate);

commit;

exception

when others then

dbms_output.put_line('Exception happened,data was rollback!');

rollback;

end test_insert_proc;

-- 创建delete存储过程

create or replace procedure crxj_collect.test_delete_proc is

begin

delete from crxj_collect.TEST_TABLE;

delete from crxj_collect.TEST_TABLE_2;

commit;

exception

when others then

dbms_output.put_line('Exception happened, data will rollback!');

rollback;

end test_delete_proc;

使用Command Window来执行存储过程：

SQL>

SQL> create or replace procedure crxj_collect.test_delete_proc is

2 begin

3 delete from crxj_collect.TEST_TABLE;

4 delete from crxj_collect.TEST_TABLE_2;

5 commit;

6 exception

7 when others then

8 dbms_output.put_line('Exception happened, data will rollback!');

9 rollback;

10 end test_delete_proc;

11 /

Procedure created

SQL>

7. 测试存储过程

右击待测试的存储过程(注意下图是以另外一个存储过程作为例子，但是操作步骤一样)

Start debugger -> Run

可以在DBMS Output栏目查看是否有错误消息。

可以查看是否正常执行存储过程(删除数据)。

8. 编写定时Job

-- 创建定时insert Job

var job_num number;

begin

dbms_job.submit(:job_num,'crxj_collect.test_insert_proc;',sysdate,'sysdate + 10/(24*60*60)');

end;

-- 创建定时delete Job

var job_num number;

begin

dbms_job.submit(:job_num,'crxj_collect.test_delete_proc;',sysdate,'sysdate + 60/(24*60*60)');

end;

9. 检测Job是否创建成功

select * from user_jobs;

可以到对应测试表查看数据

经过观察，insert执行和delete执行皆正常。

完成。

*10. 修改JOB

如果你需要修改的话，请这样：

begin

dbms_job.change

(24,'crxj_collect.test_delete_proc;',sysdate,'SYSDATE + 1/24');

end;

first created by ifuteng# 2014/4/15