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;