当前位置: 首页 > others > 正文

oralcle job的应用实例

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;

/

本文固定链接: http://2hei.net/oralcle-job%e7%9a%84%e5%ba%94%e7%94%a8%e5%ae%9e%e4%be%8b.html | 2hei's site

该日志由 2hei 于2007年11月07日发表在 others 分类下,
原创文章转载请注明: oralcle job的应用实例 | 2hei's site

报歉!评论已关闭.