The tutorial of helping customer House (www.bkjia.com) uses ORACLE stored procedure and JOB to realize automatic backup of data tables.
1. Create a stored procedure
Create or replace procedure data_auto_backup
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 ');
-- Check whether the year table to be used exists
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 ('This table exists! ');
Else begin
Dbms_output.put_line ('The table does not exist or the current user is not authorized to access it! ');
Execute immediate 'create table' | v_tablename | 'as SELECT * FROM table1 sr WHERE sr. field2 = ''''';
End;
End If;
-- Table1 table backup
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;
-- Check whether the year table to be used exists
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 ('This table exists! ');
Else begin
Dbms_output.put_line ('The table does not exist or the current user is not authorized to access it! ');
Execute immediate 'create table' | v_tablename | 'as SELECT * FROM table2 cpi WHERE cpi. field3 = ''''';
End;
End If;
-- Table2 table backup
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. Create a JOB
-- Run once a day, the first time currently running
Begin
Sys. dbms_job.submit (job =>: job,
What => 'data _ auto_backup ;',
Next_date => sysdate,
Interval => 'sysdate + 1 ');
Commit;
End;
/