YLQ

导航

 

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;
      
       TYPE backup_type IS RECORD (
            table_name VARCHAR(100),
            field_name VARCHAR(100),
            field1_name VARCHAR(100)
       );
       type backup_table_field_array IS TABLE OF backup_type INDEX BY BINARY_INTEGER;
       backup_tables backup_table_field_array;
       backup_table backup_type;
begin
     select max(t.field1) - 40 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');
    
     backup_table.table_name := 'table1';
    
     backup_table.field_name := 'field2';
    
     backup_table.field1_name := 'field1';
    
     backup_tables(1) := backup_table;
    
     backup_table.table_name := 'table2';
    
     backup_table.field_name := 'field3';
    
     backup_table.field1_name := 'field1';
    
     backup_tables(2) := backup_table;
    
     for i IN 1..backup_tables.count LOOP
        
        
         -- 检查将要使用的年月表是否存在
         v_tablename := backup_tables(i).table_name || v_year || v_month;
        
         --dbms_output.put_line('v_tablename ' || v_tablename);
        
         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 ' || backup_tables(i).table_name || ' sr WHERE sr.' || backup_tables(i).field_name || '=''''';
             end;
         end If;
   
         -- 表备份
         execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''';
         execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM ' || backup_tables(i).table_name || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''';
        
         --dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''');
         --dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM ' || backup_tables(i).table_name || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''');
         --dbms_output.put_line('SELECT COUNT(sr.' || backup_tables(i).field_name || ') FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''');
        
         execute immediate 'SELECT COUNT(sr.' || backup_tables(i).field_name || ') FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
         if v_recordcount>0 then begin
             execute immediate 'DELETE FROM ' || backup_tables(i).table_name || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''';
             --dbms_output.put_line('DELETE FROM ' || backup_tables(i).table_name || ' sr WHERE TO_CHAR(sr.' || backup_tables(i).field1_name || ',''yyyy-MM-dd'') = ''' || v_bakdate || '''');
             end;
         end If;
        
         commit;
     END LOOP;
         
    
     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;
/

posted on 2009-09-23 13:45  YLQ  阅读(508)  评论(0编辑  收藏  举报