oracle利用存储过程备份,利用ORACLE存储过程与JOB结合实现对数据表自动备份

存储过程

http://www.doczj.com/doc/19c8d82c915f804d2b16c10a.html/view/cb5d7d6baf1ffc4ffe47ac60.html?from=rec&pos=2&weight=5&lastweight=3&count=5

包体创建

http://www.doczj.com/doc/19c8d82c915f804d2b16c10a.html/view/a142f2e69b89680203d825d9.html

利用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('该表不存在或当前用户无权访问！');