使用DBMS_JOB包创建删除schema下的job

CREATE OR REPLACE PROCEDURE p_test AS
BEGIN
  NULL;
END p_test;

HR@joydb:~>select text from user_source where name=upper('p_test');

TEXT
-----------------------
procedure p_test as
begin
  null;
end p_test;

HR@joydb:~>select t.OBJECT_NAME,
  2  t.status,
  3  to_char(t.CREATED, 'yyyy-mm-dd hh24:mi:ss') dt
  4  from user_objects t
  5  where t.OBJECT_TYPE = 'PROCEDURE'
  6  and t.OBJECT_NAME = 'P_TEST';

OBJECT_NAME STATUS  DT
----------- ------- -------------------
P_TEST      VALID   2011-12-26 10:32:03

为HR创建一个job,每两分钟执行一次

DECLARE
  JOB PLS_INTEGER;
BEGIN

  SYS.DBMS_JOB.SUBMIT(JOB       => JOB,
                      WHAT      => 'P_TEST;',
                      NEXT_DATE => SYSDATE,
                      INTERVAL  => 'SYSDATE+1/24/30');
  COMMIT;
END;

HR@joydb:~>select t.SCHEMA_USER,t.INTERVAL,t.WHAT from user_jobs t;

SCHEMA_USER INTERVAL           WHAT
----------- ------------------ -------
HR          SYSDATE+1/24/30    P_TEST;

使用PL/SQL删除这个job

DECLARE
  V_COUNT PLS_INTEGER;
  V_JOB   PLS_INTEGER;
BEGIN
  SELECT COUNT(*) INTO V_COUNT FROM USER_JOBS S WHERE S.WHAT = 'P_TEST;';
  IF V_COUNT = 1 THEN
    SELECT S.JOB INTO V_JOB FROM USER_JOBS S WHERE S.WHAT = 'P_TEST;';
    SYS.DBMS_JOB.REMOVE(V_JOB);
    COMMIT;
  END IF;
END;

HR@joydb:~>select t.SCHEMA_USER,t.INTERVAL,t.WHAT from user_jobs t;

no rows selected

也可以写在一起,根据P_TEST判断如果该job存在就删除,否则创建

DECLARE
  JOB     PLS_INTEGER;
  V_COUNT PLS_INTEGER;
  V_JOB   PLS_INTEGER;
BEGIN
  SELECT COUNT(*) INTO V_COUNT FROM USER_JOBS S WHERE S.WHAT = 'P_TEST;';
  IF V_COUNT = 1 THEN
    SELECT S.JOB INTO V_JOB FROM USER_JOBS S WHERE S.WHAT = 'P_TEST;';
    SYS.DBMS_JOB.REMOVE(V_JOB);
    COMMIT;
  END IF;
  SYS.DBMS_JOB.SUBMIT(JOB       => JOB,
                      WHAT      => 'P_TEST;',
                      NEXT_DATE => SYSDATE,
                      INTERVAL  => 'SYSDATE+1/24/30');
  COMMIT;
END;