新建过程和JOb的脚本

create or replace procedure Pro_cal_cltx is
  -----------------------------------------
  -- Author  : wang  chaoqun
  -- Created : 2010-9-25 18:38:03
  -- Purpose : 用来预计算车辆通行表，每小时执行一次，每天晚上多索引进行（分钟、小时、天、月表）

  --修改人：
  --修改内容：
  --修改时间：
  -----------------------------------------
  v_day       date;
  v_yesterday date;
begin
  ---参数初始化
  select to_date(to_char(sysdate, 'yyyymmdd'), 'yyyymmdd'),
         to_date(to_char(sysdate - 1, 'yyyymmdd'), 'yyyymmdd')
    into v_day, v_yesterday
    from dual;

  if to_char(sysdate, 'HH24') = '01' then
    ---分钟表
    delete from traffic_min where tjsj >= v_yesterday;
 
    insert into traffic_min nologging
      select to_date(to_char(jgsj, 'yyyy-MM-dd HH24:') ||
                     to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
                     'yyyy-MM-dd HH24:mi'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from cltx
       where jgsj >= v_yesterday
       group by to_char(jgsj, 'yyyy-MM-dd HH24:') ||
                to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
                road,
                fxbh,
                cdh;
    ---小时表
    delete from traffic_hour where tjsj >= v_yesterday;
 
    insert into traffic_hour nologging
      select to_date(to_char(jgsj, 'yyyy-MM-dd HH24'), 'yyyy-MM-dd HH24'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from cltx
       where jgsj >= v_yesterday
       group by to_char(jgsj, 'yyyy-MM-dd HH24'), road, fxbh, cdh;
    ---天表
    delete from traffic_day where tjsj >= v_yesterday;
 
    insert into traffic_day nologging
      select to_date(to_char(jgsj, 'yyyy-MM-dd'), 'yyyy-MM-dd'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from cltx
       where jgsj >= v_yesterday
       group by to_char(jgsj, 'yyyy-MM-dd'), road, fxbh, cdh;
    ---月表
    delete from traffic_month
     where tjsj >=
           to_date(to_char(add_months(v_day, -1), 'yyyy-MM') || '-01',
                   'yyyy-MM-dd');
 
    insert into traffic_month nologging
      select to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from traffic_day
       where tjsj >=
             to_date(to_char(add_months(v_day, -1), 'yyyy-MM') || '-01',
                     'yyyy-MM-dd')
       group by to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),road, fxbh, cdh;
    ----索引重建
    -----------------
  else
    ---分钟表
    delete from traffic_min where tjsj >= v_day;
 
    insert into traffic_min nologging
      select to_date(to_char(jgsj, 'yyyy-MM-dd HH24:') ||
                     to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
                     'yyyy-MM-dd HH24:mi'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from cltx
       where jgsj >= v_day
       group by to_char(jgsj, 'yyyy-MM-dd HH24:') ||
                to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
                road,
                fxbh,
                cdh;
    ---小时表
    delete from traffic_hour where tjsj >= v_yesterday;
 
    insert into traffic_hour nologging
      select to_date(to_char(jgsj, 'yyyy-MM-dd HH24'), 'yyyy-MM-dd HH24'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from cltx
       where jgsj >= v_day
       group by to_char(jgsj, 'yyyy-MM-dd HH24'), road, fxbh, cdh;
    ---天表
    delete from traffic_day where tjsj >= v_yesterday;
 
    insert into traffic_day nologging
      select to_date(to_char(jgsj, 'yyyy-MM-dd'), 'yyyy-MM-dd'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from cltx
       where jgsj >= v_yesterday
       group by to_char(jgsj, 'yyyy-MM-dd'), road, fxbh, cdh;
    ---月表
    delete from traffic_month
     where tjsj >=
           to_date(to_char(v_day, 'yyyy-MM') || '-01', 'yyyy-MM-dd');
 
    insert into traffic_month nologging
      select to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),
             road,
             fxbh,
             cdh,
             round(sum(clsd) / count(1), 1),
             count(1) ll,
             1,
             0,
             0
        from traffic_day
       where tjsj >=
             to_date(to_char(v_day, 'yyyy-MM') || '-01', 'yyyy-MM-dd')
       group by to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),road, fxbh, cdh;
  end if;

  commit;
end Pro_cal_cltx;
/

DECLARE

  X NUMBER;

BEGIN

  SYS.DBMS_JOB.SUBMIT(job => X,
                     
                      what => 'begin

Pro_cal_cltx;

end;',
                     
                      next_date => to_date('16-11-2010 10:30:29',
                                           'dd-mm-yyyy hh24:mi:ss'),
                      interval  => 'sysdate+1/24');

  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  commit;
END;

/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17179887/viewspace-678192/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17179887/viewspace-678192/