oracle job的用法：

— pl/sql 中显示的 job

begin

  sys.dbms_job.submit(job => :job,

                      what => ‘mypro;’,

                      next_date => to_date(’07-11-2007 17:41:32′, ‘dd-mm-yyyy hh24:mi:ss’),

                      interval => ‘sysdate+1/1440′);

  commit;

end;

/

–procedure

create or replace procedure mypro as

begin

insert into test(id,t) values(1,sysdate);

end;

1.创建过程 mypro

create or replace procedure mypro as

begin

insert into test(id,t) values(1,sysdate);

end;

/

2.设定job的队列总数，最大1000

SQL> show parameter job_queue_process;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     500

3.生成job

SQL> variable yujob number;

SQL> begin

  2  dbms_job.submit(:yujob,’mypro;’,sysdate,’sysdate+1/1440′);    — 每1小时运行一次  ‘SYSDATE + 1/24′

  3  end;

  4  /

4.显示job号

SQL> print yujob;

     YUJOB

———-

        41

SQL> commit;

Commit complete.

5.运行job

SQL> begin

  2  dbms_job.run(41);

  3  end;

  4  /

6.删除job

begin

dbms_job.remove(41);   –dbms_job.remove(:yujob);

end;

/

———–

实例

create or replace procedure sync_user as

begin

update shequ_subject_bak set shequ_subject_bak.nikename = (select userinfo.nickname from userinfo where shequ_subject_bak.username=userinfo.username and shequ_subject_bak.nikename!=userinfo.nickname);

end;

/

variable job_su_id number;

begin

dbms_job.submit(:job_su_id,’sync_user;’,sysdate,’SYSDATE + 1/24′);    — 每6小时运行一次  ‘SYSDATE + 6/24’

end;

/

print job_su_id;

–提交

commit;

begin

dbms_job.run(:job_su_id);

end;

/

–删除job

begin

dbms_job.remove(:job_su_id);   –dbms_job.remove(:yujob);

end;

/

本文固定链接: https://www.2hei.net/2007/11/07/oralcle-job%e7%9a%84%e5%ba%94%e7%94%a8%e5%ae%9e%e4%be%8b/ | 2hei.net