[오라클] 잡 생성

잡 생성을 해보자.

 

잡 생성 문을 그냥 모르는 입장에서 보기만 해봐도 대충 뭐가 뭘 할 것이라는 것이 눈에 들어온다.

 

job은 무시하고, 잡 번호가 자동생성 되서 들어오는 듯 하다.

what 무엇이냔데 - 잡 생성하는데 무얼 하겠냐 이거다. 당연히 실행할 것을 넣으면 되겠고,

next_date 다음 날짜 - 요건 감이 안올 수도 있겠지만, 최초 실행을 언제 할 것이냐가 되겠다. 디폴트 sysdate

interval 간격,주기 - 어느 주기로 돌릴 것이냐가 되겠고, 디폴트 null

no_parse 안 파스 - 파스 여부를 뜻하는 것 같은데, 연관 테이블이 생성 되기 전에 생성을 원하는 지를 뜻하는 것 같다. 디폴트 false

 

---

 

DECLARE
    X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
     JOB        => X
    ,WHAT       => '[object]'
    ,NEXT_DATE  => [시작일시]
    ,INTERVAL   => [시작주기]
    ,NO_PARSE   => [TRUE/FALSE]
);
END;

---

예제

DECLARE
    X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
     JOB        => X
    ,WHAT       => 'USER.PC_DAILY_BATCH'
    ,NEXT_DATE  => TRUNC(SYSDATE+1)+1/24/60 -- 내일 1분(1/24시간/60분)
    ,INTERVAL   => TRUNC(SYSDATE+1)+1/24/60
    ,NO_PARSE   => TRUE
);
END;

---

 

보통 잡 생성하려는 게 사용자 이용이 거의 없는 시간에 무슨 작업을 수행하기 위함이나 하루를 마무리 지으면서 데이터를 확정 짓거나 뭐 등등 있을 것이다.

 

그래서 next_date나 interval에 trunc가 많이 들어간다.

trunc 를 사용하면 해당 일자에 00:00:00가 나오기 때문에 +1을 넣어 다음 날로 설정하고,

 

30분 주기를 원할 때엔 30/60/24 <----- 이게 보기가 편할런지... 30분/60분/24시간

 

머리로 계산하기 편한 게 '하루를 24시간으로 쪼개고, 60분으로 쪼갠다.'라는 생각으로 그렇게 되는 것 같다.

 

잡 생성은 이렇게 끝 ~

 

 

# 잡 스크립트 한번 훑어보는 것도 좋다.

create or replace NONEDITIONABLE PACKAGE dbms_job AUTHID CURRENT_USER IS

  -- for backward compatibility. Not used anymore.
  any_instance CONSTANT BINARY_INTEGER := 0;

  -- Parameters are:
  --
  -- JOB is the number of the job being executed.
  -- WHAT is the PL/SQL procedure to execute.
  --   The job must always be a single call to a procedure.  The
  --     routine may take any number of hardcoded parameters.
  --     Special parameter values recognized are:
  --       job:       an in parameter, the number of the current job
  --       next_date: in/out, the date of the next refresh
  --       broken:    in/out, is the job broken.  The IN values is FALSE.
  --   Always remember the trailing semicolon.
  --   Some legal values of WHAT (assuming the routines exist) are
  --     'myproc( ''10-JAN-82'', next_date, broken);'
  --     'scott.emppackage.give_raise( ''JENKINS'', 30000.00);'
  --     'dbms_job.remove( job);'
  -- NEXT_DATE is the date at which the job will next be automatically run,
  --   assuming there are background processes attempting to run it.
  -- INTERVAL is a date function, evaluated immediately before the job starts
  --   executing.  If the job completes successfully, this new date is placed
  --   in NEXT_DATE.  INTERVAL is evaluated by plugging it into the statement
  --     select INTERVAL into next_date from dual;
  --   INTERVAL must evaluate to a time in the future.  Legal intervals include
  --     'sysdate + 7'                    -- execute once a week
  --     'NEXT_DAY(sysdate,''TUESDAY'')'  -- execute once every tuesday
  --     'null'                           -- only execute once
  --   If INTERVAL evaluates to null and a job completes successfully, then
  --   the job is automatically deleted from the queue.

  PROCEDURE isubmit    ( job       IN  BINARY_INTEGER,
                         what      IN  VARCHAR2,
                         next_date IN  DATE,
                         interval  IN  VARCHAR2 DEFAULT 'null',
                         no_parse  IN  BOOLEAN DEFAULT FALSE);
  -- Submit a new job with a given job number.

  PROCEDURE submit    ( job       OUT BINARY_INTEGER,
                        what      IN  VARCHAR2,
                        next_date IN  DATE DEFAULT sysdate,
                        interval  IN  VARCHAR2 DEFAULT 'null',
                        no_parse  IN  BOOLEAN DEFAULT FALSE,

                        -- Bug 1346620: replace pkg vars with constants.
                        -- Default for instance = dbms_job.any_instance.
			instance  IN  BINARY_INTEGER DEFAULT 0,
			force     IN  BOOLEAN DEFAULT FALSE );
  -- Submit a new job.  Chooses JOB from the sequence sys.jobseq.
  -- instance and force are added for jobq queue affinity
  -- If FORCE is TRUE, then any positive  integer is acceptable as the job
  -- instance. If FORCE is FALSE, then the specified instance must be running;
  -- otherwise the routine raises an exception.
  -- For example,
  --   variable x number;
  --   execute dbms_job.submit(:x,'pack.proc(''arg1'');',sysdate,'sysdate+1');

  PROCEDURE remove    ( job       IN  BINARY_INTEGER );
  -- Remove an existing job from the job queue.
  -- This currently does not stop a running job.
  --   execute dbms_job.remove(14144);

  PROCEDURE change    ( job       IN  BINARY_INTEGER,
                        what      IN  VARCHAR2,
                        next_date IN  DATE,
                        interval  IN  VARCHAR2,
			instance  IN  BINARY_INTEGER DEFAULT NULL,
			force     IN  BOOLEAN DEFAULT FALSE);
  -- Change any of the the user-settable fields in a job
  -- Parameter instance and force are added for job queue affinity
  -- If what, next_date,or interval is null, leave that value as-is.
  -- instance defaults to NULL indicates instance affinity is not changed.
  -- If FORCE is FALSE, the specified instance (to which the instance number
  -- change) must be running. Otherwise the routine raises an exception.
  -- If FORCE is TRUE, any positive  integer is acceptable as the job instance.
  --   execute dbms_job.change( 14144, null, null, 'sysdate+3');

  PROCEDURE what      ( job       IN  BINARY_INTEGER,
                        what      IN  VARCHAR2 );
  -- Change what an existing job does, and replace its environment

  PROCEDURE next_date ( job       IN  BINARY_INTEGER,
                        next_date IN  DATE     );
  -- Change when an existing job will next execute

  PROCEDURE instance ( job        IN BINARY_INTEGER,
                       instance   IN BINARY_INTEGER,
		       force      IN BOOLEAN DEFAULT FALSE);
  -- Change job instance affinity. FORCE parameter works same as in SUBMIT

  PROCEDURE interval  ( job       IN  BINARY_INTEGER,
                        interval  IN  VARCHAR2 );
  -- Change how often a job executes

  PROCEDURE broken    ( job       IN  BINARY_INTEGER,
                        broken    IN  BOOLEAN,
                        next_date IN  DATE DEFAULT SYSDATE );
  --  Set the broken flag.  Broken jobs are never run.

  PROCEDURE run       ( job       IN  BINARY_INTEGER,
			force     IN  BOOLEAN DEFAULT FALSE);
  --  Run job JOB now.  Run it even if it is broken.
  --  Running the job will recompute next_date, see view user_jobs.
  --    execute dbms_job.run(14144);
  --  Warning: this will reinitialize the current session's packages
  --  FORCE is added for job queue affinity
  --  If FORCE is TRUE, instance affinity is irrelevant for running jobs in
  --  the foreground process. If FORCE is FALSE, the job can be run in the
  --  foreground only in the specified instance. dbms_job.run will raise an
  --  exception if FORCE is FALSE and the connected instance is the wrong one.

  PROCEDURE user_export ( job    IN     BINARY_INTEGER,
                          mycall IN OUT VARCHAR2);
  --  Produce the text of a call to recreate the given job

  PROCEDURE user_export ( job     IN     BINARY_INTEGER,
                         mycall   IN OUT VARCHAR2,
			 myinst   IN OUT VARCHAR2);
  -- Procedure is added for altering instance affinity (8.1+) and perserve the
  -- compatibility

  --------------------------------------------------------------
  -- Return boolean value indicating whether execution is in background
  -- process or foreground process
  -- jobq processes are no longer background processes, background_processes
  -- will be removed in 8.3 or later
  -------------------------------------------------------------
  FUNCTION background_process RETURN BOOLEAN;
  FUNCTION is_jobq RETURN BOOLEAN;


END;