Oracle创建存储过程及job定时实施范例

1) 查询出前一天的数据，并插入到另一表中的存储过程:

----------创建一存储过程：
create or replace procedure P_TEST is
begin
  insert into test_table
    (id, content, account,states)
      select 'ty' || to_char(current_timestamp,'yyyymmddhh24missff'),
           'test content....',
           a.account,
           1
      from intf.serv@databaselink       a,
           intf.merge_staff@databaselink b,
           intf.merge_site@databaselink  c
     where a.begin_staff_id = b.staff_id(+)
       and a.begin_site_id = c.site_id(+)
       and a.begin_date >= TRUNC(SYSDATE-1, 'DD')
       and a.begin_date < TRUNC(SYSDATE, 'DD')
       and a.class_id2 = '111111111';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
end P_TEST ;

 

 2）定制一job,实时执行创建的存储过程，如下job为每间隔10分钟执行一次：

declare   job1   number(10); 
begin 
  job1:=11; 
  dbms_output.put_line(   job1   ); 

  sys.dbms_job.submit(job => job1,
                      what => 'P_TEST;',
                      next_date => sysdate,
                      interval => 'SYSDATE + 10/(60*24)');
  commit;
end;

 

注意：这句what => 'P_TEST;',这里的存储过程名后面一定要记得加上英文分号。当然如果存储过程要执行的操作比较简单，这里也可以直接写SQL语句，即如：

what => 'UPDATE table_name SET cnt= 0;
COMMIT;',

 

 

3）创建完JOB后发现job不执行，查找原因。一般可能是CJQ0进程失效导致的，此时要重起CJQ0进程。具体操作如下：

       首先设置JOB_QUEUE_PROCESSES为0，Oracle会杀掉CJQ0及相应job进程：

      

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0; 

      其次再等2~3分钟后，重新设置：

  

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10; 

 

    此时PMON会重起CJQ0进程了。

 

4）Oracle获取时间精确到毫秒：

    

select to_char(current_timestamp,'yyyymmddhh24missff') from test_table；

 

    这里获取的时间就是精确到毫秒以后三位的，如20090409123030230000