怎么在Oracle中利用存儲過程和調(diào)度器對過期數(shù)據(jù)進行清除

怎么在Oracle中利用存儲過程和調(diào)度器對過期數(shù)據(jù)進行清除？針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

實現(xiàn)原理

1張表（SYS_DBA_CONFIG）、1個Job定時器（Job_DBA_AutoRunScript）和2個存儲過程（DBA_AUTODELETE、DBA_AUTORUNSCRIPT）實現(xiàn)自動清理不同表中的過期數(shù)據(jù)。 通過定時器調(diào)用存儲過程查表判斷是否開啟過期數(shù)據(jù)清理功能，如果開啟調(diào)用數(shù)據(jù)清理的存儲過程。

建表

建立數(shù)據(jù)庫任務配置表，用來管理數(shù)據(jù)庫中那些表需要定期處理。

表格類型

建表語法

create table SYS_DBA_CONFIG
(
 NAME  VARCHAR2(200) not null,
 value  VARCHAR2(200) not null,
 type  VARCHAR2(200) not null,
 REMARK VARCHAR2(200) not null
 ISRUN  NUMBER(1) not null,
 COLUMNTYPE VARCHAR2(200)
);

數(shù)據(jù)展示

表中的數(shù)據(jù)有兩種：

• 存儲過程是否啟用

• 表格設(shè)置保存時間

創(chuàng)建存儲過程

判斷系統(tǒng)是否啟動定期清除功能的存儲過程

根據(jù)類型和是否啟用查找刪除數(shù)據(jù)的存儲過程

CREATE OR REPLACE PROCEDURE xxx."DBA_AUTORUNSCRIPT" AS
 /*******************************************************
 功能：定期運行指定腳本V1.0
 說明：SQL語句從配置表SYS_DBA_CONFIG讀取
 ********************************************************/
 v_Name        VARCHAR2(500); --Sql語句變量
 v_Value        VARCHAR2(250); --Sql語句變量
 CURSOR CS IS SELECT UPPER(Name),VALUE FROM SYS_DBA_CONFIG WHERE LOWER(TYPE)='auto_run_script' AND ISRUN = 1;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO v_Name, v_Value;--獲得當前記錄的數(shù)據(jù)
 EXIT WHEN CS%NOTFOUND;
 dbms_output.put_line('執(zhí)行腳本('||v_Name||')：'||v_Value);
 EXECUTE IMMEDIATE ('BEGIN '||v_Name||'; COMMIT; END;');
END LOOP;
END DBA_AutoRunScript;

執(zhí)行刪除過期數(shù)據(jù)的存儲過程

根據(jù)對應的表中的對應字段和當前時間比較，如果時間大于對應的保存時間天數(shù)，刪除數(shù)據(jù)。

CREATE OR REPLACE PROCEDURE xxx."DBA_AUTODELETE" AS
 /*******************************************************
 功能：根據(jù)保留天數(shù)刪除數(shù)據(jù)V1.0
 說明：表名稱、保存天數(shù)從配置表SYS_DBA_CONFIG讀取
 ********************************************************/
 v_name     varchar2(250); --對應的表名
 v_remark    varchar2(250); --對應的表字段
 v_value    number(10); --表對應的表數(shù)據(jù)天數(shù)
 v_endTime   date; --清除的具體時間
 v_sql    varchar2(250);
 v_columntype  varchar2(20);
 CURSOR CS IS SELECT Name,VALUE,remark,columntype FROM SYS_DBA_CONFIG WHERE LOWER(TYPE)='auto_delete_table' AND ISRUN=1;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO v_name, v_value,v_remark,v_columntype;--獲得當前記錄的數(shù)據(jù)
 EXIT WHEN CS%NOTFOUND;
 dbms_output.put_line('刪除的表名：' || v_name||' 保留天數(shù)：'||v_value);
 v_endTime:=TRUNC(SYSDATE- v_value);
 if v_columntype='DATE' then
 v_sql := 'delete from ' || v_name||' where '|| v_remark||' < TRUNC(SYSDATE- '||v_value||')' ;
 dbms_output.put_line('刪除的sql：' || v_sql);
 else
 v_sql := 'delete from ' || v_name||' where to_date('||v_remark||',''yyyy-mm-dd hh34:mi:ss'') < TRUNC(SYSDATE- '||v_value||')';
 dbms_output.put_line('刪除的sql：' || v_sql);
 end if;
 begin
  execute immediate v_sql;
 end;
END LOOP;

END DBA_AutoDelete;

創(chuàng)建Jobs定時器

通過定時器啟動判斷系統(tǒng)是否啟動定期清除功能的存儲過程，然后存儲過程再調(diào)用刪除數(shù)據(jù)的存儲過程完成數(shù)據(jù)清除。

begin 
dbms_scheduler.create_job 
( 
job_name => 'Job_DBA_AutoRunScript', 
job_type => 'PLSQL_BLOCK', 
job_action => 'begin DBA_AutoRunScript; end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=3;byminute=30', 
enabled => true
); 
end;

關(guān)于怎么在Oracle中利用存儲過程和調(diào)度器對過期數(shù)據(jù)進行清除問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。