oracle job 定时执行存储过程

最近需要一个首页展示的报表图片,用flash根据sql的结果生成图片,但需要执行的sql效率实在太低,跑一遍下来大约需要1min+的样子,完全达不到首页展示的要求。各种优化无果,毕竟数据量太大(2kw+)还要执行group by 什么的,最后一怒之下想写个临时表存放着需要的结果集,因为真正需要的结果就几十条所以不会产生较大的冗余。由于数据有时效性要求,所以临时表最好能定时更新或者手动更新,想了半天觉得还是写个存储过程然后写个job定时执行一下的好。但臣妾真的很难做到啊,最怕写sql了,而且还是高端sql,一通百度谷歌stackoverflow 。最终把活算是草草的完成了。期间学到了不少新东西,此处记录一下下:

在存储过程中建表时,提示用户权限不足。这个很诡异,在建用户时给用户赋予了较高的权限,甚至dba的权限都给了。然后大神们普遍认为应给给用户再重新给要执行存储过程的用户赋建表权限:

grant create any table to your_user_name

建表时使用 execute immediate 的时候要注意单引号的问题。

下面贴一下一种可能的存储过程建表语句:

create or replace procedure create_。。。_top is --隐去了可能暴露信息的内容
v_tablename varchar2(30); --定义变量,以便于维护
v_flag number(10,0);  
--sql_str varchar2(500);
begin
  v_tablename := '...';
   select count(*) into v_flag from user_TABLES where table_name= v_tablename;
  if v_flag =1 then
    begin
      execute immediate 'drop table '|| v_tablename ; --删除旧信息
     end;
  end if;
  begin
    execute immediate 'create table '|| v_tablename || ' as select 。。。,
        from (select
              。。。
          from 。。。 t
         where 1 = 1
           and t.。。。 >= trunc(sysdate,'''||'yy'||''')
           and t.。。。 <= sysdate
         group by t.。。
         order by amt desc)
   where rownum <= 100';
   end;
end create_。。。_top;

到这里大头已经写完了,剩下的就是建job的问题了。如果你熟练使用pl/sql developer那恭喜你,直接在dbms_job 里面点新建就好了。

如果非要写sql那基本上是下面这个形式:

begin
  sys.dbms_job.submit(job => :job,
                      what => 'create_..._top;',
                      next_date => to_date('21-12-2013 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate+1)+1/24');
  commit;
end;
/

主要问题在于interval的处理上这个只要搜一下dbms_job.submit()应该会有好多,这个就不再赘述了,当然为了通用性可以仿照上面的存储过程在建立之前检查一下此job是否存在,唯一蛋疼的是job号是指定的不过可以通过查看job表的what字段来对比,要注意的是what中需要加分号。顺便吐槽:所有强制写分号的语言都是sb。写惯了js现在每次修改完java代码一ant满屏的错误,都在提示应该写;,但你都知道那里应该写分号了自己加上不行么。:-(

发表评论

电子邮件地址不会被公开。 必填项已用*标注