-- JOB의 조회

SELECT * FROM USER_JOBS;

-- JOB의 등록

EX 1)

DECLARE

job_no number;

BEGIN

DBMS_JOB.SUBMIT(job_no,'update  arreo_sms set snd_msg =''test''

where cmp_msg_id =''001'';', SYSDATE,

'SYSDATE + 1');

END;

EX 2)

DECLARE

X number;

BEGIN

DBMS_JOB.SUBMIT(X,'MERGE INTO TB_SDR_SDREPORT_BPLC A

USING (SELECT BPLC_ID, PROCESS_YM_WEEK, CLOS_SE_CODE

       FROM TB_TMP_NEXTWEEK_REPORT

       WHERE CONF_YN = ''Y'') B

ON (A.BPLC_ID = B.BPLC_ID

AND  A.PROCESS_YM_WEEK = B.PROCESS_YM_WEEK

AND  A.CLOS_SE_CODE = B.CLOS_SE_CODE)

WHEN MATCHED THEN 

UPDATE SET REPORT_AT =''N'', UPDUSR_ID = ''JOB44'', UPDT_DT =SYSDATE, UPDT_IP =''127.0.0.1''

WHERE REPORT_AT = ''T'';', SYSDATE,

'NEXT_DAY(TRUNC(SYSDATE),''토'')');

END;

// DBMS_JOB.SUBMIT(잡넘버,PROCEDURE 내용, 최초실행시간,실행주기);

// WHAT 내용안에 PROCEDURE로 등록 된 내용이 아닌 직접 쿼리를 입력시 ' -> '' 로 변경 하여야 한다.

-- JOB의 변경

EXECUTE DBMS_JOB.NEXT_DATE(잡넘버, TRUNC(SYSDATE)+1+1/24/60);

COMMIT;

-- JOB의 삭제

EXECUTE DBMS_JOB.REMOVE(잡넘버);

COMMIT;

-- JOB의 정지

EXECUTE DBMS_JOB.BROKEN(잡넘버,FALSE);

COMMIT;

-- JOB 실행

EXECUTE DBMS_JOB.RUN(잡넘버);

COMMIT;

-- 날짜 계산 표시