oracle定时调用存储过程

要求：每月24日定时抽取表mlk_flag_2012最新月份的数据作为当月的数据插入到表mlk_flag_2012中，例如，7月24号前，表mlk_flag_2012中只有1-6月份的数据，7月24号抽取6月份的数据作为7月份的初始值。

步骤：

一：创建存储过程MLK_FLAG_PROC 作用：抽取表mlk_flag最新月份的数据作为当月的数据插入。对于表不存在及无历史数据的情况都做了处理，也可以作为oracle初始化之用。

create or replace procedure MLK_FLAG_PRO_NEW as
    year_ varchar2(4); 
    create_table_ varchar2(3000); 
    insert_data_  varchar2(3000); 
    is_latest_year varchar2(100); 
    delete_sql_ varchar2(3000); 
    is_exists_sql_ varchar2(3000); 
    is_exists_ number(10); 
    username varchar(200); 
    columns_name_sql varchar(3000); 
    columns_name varchar(3000);

begin year_ := to_char(sysdate,'yyyy');
 --处理mlk flag  
DBMS_OUTPUT.PUT_LINE('处理mlk flag');  
select substr(max(table_name),length(max(table_name))-3,4) into is_latest_year from user_tables   where table_name like 'MLK_FLAG_%';  
--判断当年表是否存在  
--1.当年表存在  
if is_latest_year=year_ then 
    --判断当前月份是否存在数据，若存在，删除当前月份数据，然后倒入最新数据  
    is_exists_sql_ :=' select count(*)   from  MLK_FLAG_'||year_||'  where time_ =  to_char(sysdate,''yyyymm'') ';    
    execute immediate is_exists_sql_ into is_exists_;   
    if is_exists_>0 then  
        delete_sql_  :='delete from MLK_FLAG_'||year_||' where time_ =  to_char(sysdate,''yyyymm'') ';   
        execute immediate delete_sql_;   
        commit;     
    end if;   
    columns_name_sql :='select substr(max(sys_connect_by_path(NAME, '','')), 2, 10000)  columns_name  
                        from (                         
                    SELECT COLUMN_NAME as NAME, rownum ro FROM USER_TAB_COLUMNS                         
                    WHERE TABLE_NAME = ''MLK_FLAG_'||year_||''' AND COLUMN_NAME!=''TIME_''                        
                     ORDER BY COLUMN_ID                  
                ) newtab                   
                start with newtab.ro = 1                  
                connect by prior newtab.ro = newtab.ro-1';    
    DBMS_OUTPUT.PUT_LINE(columns_name_sql);   
    execute immediate columns_name_sql into columns_name;   
    DBMS_OUTPUT.PUT_LINE(columns_name);  
    insert_data_ := ' insert into MLK_FLAG_'||year_||'   
            ('||columns_name||',time_)   
            select '||columns_name||' ,to_char(sysdate,''yyyymm'')  as TIME_      
            from MLK_FLAG_'||year_||'     
            where time_=(select max(time_) from MLK_FLAG_'||year_||')' ;     
    DBMS_OUTPUT.PUT_LINE(insert_data_); execute immediate insert_data_; 
    commit; 
end if; 
--2.当年表不存在，创建当年表，并将上年的最后一个月份的数据倒入当当年表中 
select user into username from dual; 
execute immediate 'grant create table to '||username; 
if is_latest_year<year_ then 
    create_table_ := ' create table MLK_FLAG_'||year_|| ' as   
            select * from MLK_FLAG_'||is_latest_year||' where rownum<1' ; 
    execute immediate create_table_;  
    columns_name_sql :='select substr(max(sys_connect_by_path(NAME, '','')), 2, 10000)  columns_name                   
                from (                         
                    SELECT COLUMN_NAME as NAME, rownum ro FROM USER_TAB_COLUMNS                        
                    WHERE TABLE_NAME = ''MLK_FLAG_'||is_latest_year||''' AND COLUMN_NAME!=''TIME_''                         
                    ORDER BY COLUMN_ID                  
                ) newtab                  
                start with newtab.ro = 1                   
                connect by prior newtab.ro = newtab.ro-1';   
    --DBMS_OUTPUT.PUT_LINE(columns_name_sql);   
    execute immediate columns_name_sql into columns_name;   
    --DBMS_OUTPUT.PUT_LINE(columns_name); 
    insert_data_ := ' insert into MLK_FLAG_'||year_||' 
             ('||columns_name||',time_)   
            select '||columns_name||',to_char(sysdate,''yyyymm'') as TIME_ from MLK_FLAG_'||is_latest_year||' 
            where time_=(select max(time_) from MLK_FLAG_'||is_latest_year||')'; 
    Dbms_output.Put_line(insert_data_); 
    execute immediate insert_data_; 
    commit; 
end if;
end MLK_FLAG_PRO_NEW;

二：执行定时任务

declare  

   jobno number;  

 begin  

   dbms_job.submit(

     jobno,

     'MLK_FLAG_PROC;', --what

     to_date('24-07-2012 02:00:00', 'dd-mm-yyyy hh24:mi:ss'), --next_date

     ' TRUNC(LAST_DAY(SYSDATE))+24+2/24' --interval,关键设置 每月24号凌晨2点执行

  );  

 commit;  

end; 

三： 查看定时任务的详细情况

select * from user_jobs;



四：关闭定时任务

begin dbms_job.remove(n); end;



n为任务号，通过三察看任务号。

五：Interval的相关设置

1、 每分钟执行

Interval => TRUNC(sysdate,'mi') + 1 / (24*60)

Interval =>'sysdate+1/1440'

2、 每天定时执行

例如：每天的凌晨2点执行

Interval => TRUNC(sysdate) + 1 +2 / (24)

3、 每周定时执行

例如：每周一凌晨2点执行

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

4、 每月定时执行

例如：每月1日凌晨2点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

5、 每季度定时执行

例如每季度的第一天凌晨2点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

6、 每半年定时执行

例如：每年7月1日和1月1日凌晨2点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

7、 每年定时执行

例如：每年1月1日凌晨2点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24


8、 每3小时执行一次

Interval =>sysdate+180/1440'