oracle 11g job创建,oracle 11g 创建 job

--创建一次执行的匿名块任务，成功调用一次后job消失

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name                 =>  'my_new_job2',

job_type                 =>  'PLSQL_BLOCK',

job_action               =>

'BEGIN

for i in 1 .. 5 loop

insert into t values (i);

end loop;

commit;

END;'

);

END;

--创建存储过程代替匿名块里面的内容，对于数据量大的代码很方便

create or replace procedure p_insert_t

as

BEGIN

for i in 1 .. 5 loop

insert into t values (i);

end loop;

commit;

END;

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name                 =>  'my_new_job2',

job_type                 =>  'PLSQL_BLOCK',

job_action               =>  'BEGIN p_insert_t; END;'

);

END;

--查看job的状态

SELECT job_name, owner, program_name, program_owner, state, enabled

FROM dba_scheduler_jobs

WHERE owner NOT IN ('SYS', 'SYSTEM');

SELECT *

FROM dba_scheduler_jobs

WHERE owner NOT IN ('SYS', 'SYSTEM');

--执行job, enable job和run job两种方式都可以调用job

--enable job之后自动执行job

BEGIN

DBMS_SCHEDULER.ENABLE ('my_new_job2');

END;

--run job

BEGIN

DBMS_SCHEDULER.RUN_JOB(

JOB_NAME            => 'my_new_job2',

USE_CURRENT_SESSION => FALSE);

END;

--停止 job

BEGIN

DBMS_SCHEDULER.STOP_JOB('MY_NEW_JOB2');

END;

/

--删除JOB

BEGIN

DBMS_SCHEDULER.DROP_JOB ('MY_NEW_JOB2');

END;

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