oracle创建job实践sql

1.创建表

create table PO_JQ_TYPE_STAT_MONTH
(
  ID         NUMBER not null,
  STATSJ     NVARCHAR2(30),
  STATTYPEDM VARCHAR2(30),
  PCSDM      VARCHAR2(12),
  COUNT      NUMBER,
  MONTHCOUNT NUMBER,
  FJDM       VARCHAR2(12)
)

2.创建序列

-- Create sequence 
create sequence SEQ_PO_JQ_TYPE_STAT_MONTH
minvalue 1
maxvalue 999999999999999999999999999
start with 1893973
increment by 1
cache 20;

3.创建存储过程

create or replace procedure T_JQ_PT_MONTH is
begin


   INSERT INTO PO_JQ_TYPE_STAT_MONTH (ID,STATSJ,STATTYPEDM,PCSDM,COUNT,MONTHCOUNT,FJDM)
       SELECT   SEQ_PO_JQ_TYPE_STAT_MONTH.NEXTVAL, TO_CHAR( STATSJ,'YYYYMM') STATSJ, STATTYPEDM,  PCSDM, COUNT, DAYS, FJDM FROM
         (SELECT   P.STATSJ STATSJ ,P.STATTYPEDM STATTYPEDM,P.PCSDM   PCSDM,P.COUNT COUNT,P.FJDM FJDM ,
                (select  to_char( add_months(last_day(sysdate),-1) ,'dd') days from dual) DAYS
              -- (select to_char( last_day(p.statsj) ,'DD')    from  PO_JQ_TYPE_STAT_DAY p  WHERE  to_char(P.STATSJ, 'yyyy-MM') = to_char( add_months(last_day(sysdate),-1),'yyyy-mm')) DAYS
            FROM    PO_JQ_TYPE_STAT_DAY  P WHERE  to_char(P.STATSJ, 'yyyyMM') = to_char( add_months(last_day(sysdate),-1),'yyyymm'))
     commit;
end T_JQ_PT_MONTH;

4.创建job

declare
 job number; 
begin 
sys.dbms_job.submit(job,
                      what => 'T_JQ_PT_MONTH;',
                      next_date => to_date('04-09-2013 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'to_date(to_char(sysdate+1,''yyyy/mm/dd'') || '' 01:00:00'',''yyyy/mm/dd hh24:mi:ss'')');--每天1440分钟，即一分钟运行test过程一次 
commit; 
end;