利用ORACLE存儲過程與JOB結合實現對數據表自動備份

網(LieHuo.Net)教程 利用ORACLE存儲過程與JOB結合實現對數據表自動備份。

1、創建存儲過程
create or replace procedure data_auto_backup as
v_tablename varchar2(200);
v_year varchar2(10);
v_month varchar2(10);
v_bakdate varchar2(50);
v_maxdate date;
v_tablecount integer;
v_recordcount integer;
begin
select max(t.field1) - 30 into v_maxdate from table1 t;
v_year := to_char(v_maxdate,'yyyy');
v_month := to_char(v_maxdate,'MM');
v_bakdate := to_char(v_maxdate,'yyyy-MM-dd');

-- 檢查將要使用的年月表是否存在
v_tablename := 'table1' || v_year || v_month;

SELECT COUNT(TABLE_NAME) INTO v_tablecount FROM USER_TABLES ut WHERE ut.table_name = v_tablename;

if v_tablecount>0 then
dbms_output.put_line('該表存在！');
else begin
dbms_output.put_line('該表不存在或當前用戶無權訪問！');
execute immediate 'CREATE TABLE ' || v_tablename || ' AS SELECT * FROM table1 sr WHERE sr.field2=''''';
end;
end If;

-- table1表備份
execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';

--dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('SELECT COUNT(sr.field2) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');

execute immediate 'SELECT COUNT(sr.field2) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
if v_recordcount>0 then begin
execute immediate 'DELETE FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
--dbms_output.put_line('DELETE FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
end;
end If;

commit;

-- 檢查將要使用的年月表是否存在
v_tablename := 'table2' || v_year || v_month;

SELECT COUNT(TABLE_NAME) INTO v_tablecount FROM USER_TABLES ut WHERE ut.table_name = v_tablename;

if v_tablecount>0 then
dbms_output.put_line('該表存在！');
else begin
dbms_output.put_line('該表不存在或當前用戶無權訪問！');
execute immediate 'CREATE TABLE ' || v_tablename || ' AS SELECT * FROM table2 cpi WHERE cpi.field3=''''';
end;
end If;

-- table2表備份
execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';

--dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
--dbms_output.put_line('SELECT COUNT(sr.field3) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');

execute immediate 'SELECT COUNT(sr.field3) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
if v_recordcount>0 then begin
execute immediate 'DELETE FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
--dbms_output.put_line('DELETE FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
end;
end If;

commit;
dbms_output.put_line('Ok' || v_recordcount);
end;

2、創建JOB

--1天運行一次，當前運行第一次
begin
sys.dbms_job.submit(job => :job,
what => 'data_auto_backup;',
next_date => sysdate,
interval => 'sysdate+1');
commit;
end;
/

轉自：http://www.cnblogs.com/stoneblog/

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈