간단하게 테스트 해본 오라클 DBMS_JOB 테스트

QUARTS 로 했었는데... 이것도 편하군.

###################################################################
--- 테이블 생성

CREATE TABLE TB_TEST
(
 ID VARCHAR(10),
 NAME VARCHAR(10)
);

--- 패키지 헤더 생성

CREATE OR REPLACE PACKAGE PKG_TORNADO

AS

PROCEDURE UP_TEST_INSERT (v_id IN TB_TEST.ID%type, v_name IN TB_TEST.NAME%type);

END;
/

--- 패키지 바디 생성

CREATE OR REPLACE PACKAGE BODY PKG_TORNADO
AS
PROCEDURE UP_TEST_INSERT
(
 v_id IN TB_TEST.ID%type,
 v_name IN TB_TEST.NAME%type
)
IS

BEGIN

 INSERT INTO TB_TEST (ID, NAME)
 VALUES (v_id, v_name );

 commit;
END;

END;
/

--- ORACLE JOB 생성

VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT 
(:JOBNO
,'PKG_TORNADO.UP_TEST_INSERT(''HEESUNG'', ''TORNADO'');'
,SYSDATE
,'SYSDATE + 1');
COMMIT WORK;
END;
/

 

-- JOBNO 확인

PRINT JOBNO

     JOBNO
----------
        21

-- USER_JOBS 상세 확인

SELECT JOB, LAST_DATE, NEXT_DATE, BROKEN, WHAT FROM USER_JOBS;

       JOB LAST_DATE NEXT_DATE B
---------- --------- --------- -
        21           09-APR-09 N

-- JOB 시작

EXEC DBMS_JOB.RUN(21);

-- JOB 수행 결과 보기.

SELECT * FROM TB_TEST