-- 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;
-- 날짜 계산 표시
|
Description |
Date Expression |
|
Now |
SYSDATE |
|
Tomorrow/ next day |
SYSDATE + 1 |
|
Seve days from now |
SYSDATE + 7 |
|
One hour from now |
SYSDATE + 1/24 |
|
Three hours from now |
SYSDATE + 3/24 |
|
An half hour from now |
SYSDATE + 1/48 |
|
10 minutes from now |
SYSDATE + 10/1440 |
|
30 seconds from now |
SYSDATE + 30/86400 |
|
Tomorrow at 12 midnight |
TRUNC(SYSDATE + 1) |
|
Tomorrow at 8 AM |
TRUNC(SYSDATE + 1) + 8/24 |
|
Next Monday at 12:00 noon |
NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+12/24 |
|
First day of the month at 12 midnight |
TRUNC(LAST_DAY(SYSDATE)+1) |
|
The next Monday, Wendesday or Friday at 9 a.m |
TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY'' ), NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24) [출처] [오라클] 날짜관련 계산하기(1)|작성자 자바킹
|
[출처] tibero JOB 등록 및 해제|작성자 룰루랄라
