¿À¶óŬ Àâ¸Å´ÏÀú.. °ü·Ã | DataBase Ref 2005/09/12 16:09
http://blog.naver.com/gnpado/40017478314
.ÁÖ±âÀûÀ¸·Î ¼öÇàµÇ¾îÁö´Â JOBÀ» JOB QUEUE¸¦ »ç¿ëÇÏ¿© ½ºÄÉÁÙ¸µ ÇÒ ¼ö ÀÖ½À´Ï´Ù..
JOBÀ» ½ºÄÉÁÙ¸µ Çϱâ À§Çؼ ORACLEÀÇ DBMS_JOB ÆÐÅ°Áö¸¦ ÀÌ¿ëÇÕ´Ï´Ù..
JOB QUEUE PROCESS°¡ JOB QUEUE ¾ÈÀÇ ÀâÀ» ¼öÇàÇÕ´Ï´Ù..
JOBÀ¸·Î µî·ÏµÉ ¼ö ÀÖ´Â °ÍÀº PL/SQL ÇÁ·Î½ÃÀú ¶Ç´Â ÆÐÅ°Áö°¡ µÇ¸ç
¿¹¸¦ µé¾î¼Ò½º µðºñÀÇ Å×À̺íµé¿¡¼ Ÿ°Ù Å×À̺í·Î µ¥ÀÌÅ͸¦ ÀûÀçÇÏ´Â ÇÁ·Î½ÃÀú¸¦ »ý¼ºÇߴµ¥
1ºÐ´ÜÀ§·Î µ¥ÀÌÅ͸¦ Ÿ°Ù Å×À̺í·Î ÀûÀ縦 ÇØ¾ß ÇÒ ¶§ DBMS_JOBS¿¡ µî·ÏÇÏ¿©
½ºÄÉÁÙ¸µ ÇÒ ¼ö ÀÖ½À´Ï´Ù.
.JOB_QUEUE_PROCESSES ÆĶó¹ÌÅÍ°¡ ÀÌ¿Í °ü·ÃµÈ ÃʱâÈ ÆĶó¹ÌÅÍ·Î 0À¸·Î ¼³Á¤µÇ¸é
JOB QUEUE PROCESS´Â ½ÃÀÛµÇÁö ¾ÊÀ¸¸ç JOB QUEUEÀÇ ¾î´À Àâµµ ¼öÇàµÇÁö ¾Ê½À´Ï´Ù..
JOB_QUEUE_PROCESSES ÆĶó¹ÌÅÍÀÇ MAX°ªÀÌ ¼³Á¤µÇ¾î¾ß ¿À¶óŬ ÀνºÅϽº À§¿¡¼
µ¿½Ã¿¡ ÀâÀ» ¼öÇàÇÒ ¼ö ÀÖ´Ù. ¼³Á¤ÇÒ ¼ö ÀÖ´Â ÃÖ°í °ªÀº 1000ÀÔ´Ï´Ù..
JOB_QUEUE_PROCESSES=60 °ú °°ÀÌ ¼³Á¤ÇÒ ¼ö ÀÖ½À´Ï´Ù..
.µî·ÏµÇ°Å³ª ¼öÇàµÇ´Â Àâ¿¡ ´ëÇؼ´Â DBA_JOBS ¶Ç´Â USER_JOBS µñ¼Å³Ê¸® ºä¸¦ ÅëÇØ
È®ÀÎ ÇÒ ¼ö ÀÖ´Ù.
.JOB_QUEUE_PROCESSES ´Â ´ÙÀ̳ª¹Í ÇÏ°Ô DB¸¦ SHUTDOWNÇÏÁö ¾Ê°í ALTER SYSTEM
¸í·ÉÀ» ÀÌ¿ëÇؼ ¼³Á¤ÇÒ ¼ö ÀÖ½À´Ï´Ù.
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
.JOB QUEUE¾ÈÀÇ JOBÀ» ½ºÄÉÁÙ¸µ Çϱâ À§Çؼ´Â DBMS_JOBSÆÐÅ°Áö¸¦ »ç¿ëÇÒ ¼ö ÀÖÀ¸¸ç
JOB_QUEUE¸¦ »ç¿ëÇϱâ À§ÇØ °ü·ÃµÈ DB ±ÇÇÑÀº ¾ø´Ù.
.´ÙÀ½Àº DBMS_JOBSÀÇ ÆÐÅ°Áö¸¦ »ç¿ëÇϱâ À§ÇÑ ÆÐÅ°ÁöÀÇ ÇÁ·Î½ÃÁ®µéÀÔ´Ï´Ù.
SUBMIT - ÀâÅ¥ÀÇ ÀâÀ» µî·ÏÇÕ´Ï´Ù.
REMOVE - ÀâÅ¥ÀÇ ÀâÀ» Á¦°ÅÇÕ´Ï´Ù.
CHANGE - ÀâÅ¥ÀÇ ÀâÀ» º¯°æÇÕ´Ï´Ù.
NEXT_DATE - ÀâÀÇ ´ÙÀ½ ¼öÇà½Ã°£À» º¯°æÇÕ´Ï´Ù.
INTERVAL - Àâ ¼öÇà Áֱ⸦ º¯°æÇÕ´Ï´Ù.
WHAT - ÀâÀ¸·Î µî·ÏµÈ ÇÁ·Î½ÃÀú ¶Ç´Â ÆÐÅ°Áö¸¦ º¯°æÇÕ´Ï´Ù.
RUN - ÀâÀ» ¼öµ¿À¸·Î °Á¦·Î ¼öÇàÇÕ´Ï´Ù.
.»õ JOBÀ» JOB QUEUE¿¡ µî·ÏÇϱâ À§ÇØ »ç¿ëµÇ´Â ÆĶó¹ÌÅÍ·Î DBMS_JOB.SUBMIT()¿¡ µé¾î°¡´À
ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù.
JOB - OUTPUT ÆĶó¹ÌÅÍ·Î »ý¼ºÇÑ Àâ¿¡ ÀÇÇØ ÇÒ´çµÇ´Â ½Äº°ÀÚ ÀÔ´Ï´Ù.
WHAT - JOB QUEUE¿¡ µî·ÏµÇ´Â PL/SQL ÇÁ·Î½ÃÀú ¶Ç´Â ÆÐÅ°Áö ÀÔ´Ï´Ù.
NEXT_DATE - ÀâÀÌ ¼öÇàµÇ´Â ´ÙÀ½ ½Ã°£ÀÔ´Ï´Ù.
INTERVAL - ÀâÀÌ ¼öÇàµÇ´Â ÁÖ±â·Î ÃÊ ´ÜÀ§±îÁö ÁöÁ¤ °¡´ÉÇÕ´Ï´Ù.
JOB_QUEUE¿¡ µî·ÏÇÏ´Â ¿¹Á¦ÀÔ´Ï´Ù.
VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'SP_IN_EMP_SAL;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
END;
/
À§ÀÇ PL/SQL¹®À» SQL PLUS¿¡¼ ¼öÇàÇÕ´Ï´Ù.
ù¹ø° ÆĶó¹ÌÅÍ°¡ JOB NUMBER°¡ ºÎ¿©µÇ´Â ºÎºÐÀÌ°í
µÎ¹ø° ÆĶó¹ÌÅÍ°¡ WHATÀ¸·Î SP_IN_EMP_SALÀ̶ó´Â ÇÁ·Î½ÃÀú¸¦ µî·ÏÇß½À´Ï´Ù.
¼¼¹ø° ÆĶó¹ÌÅÍ°¡ NEXT_DATEÀ̸ç 4¹ø° ÆĶó¹ÌÅÍ°¡ Àâ ¼öÇà ÁÖ±â·Î ÇÏ·ç¿¡ Çѹø¾¿
¼öÇàÇ϶ó´Â ÀǹÌÀÔ´Ï´Ù.
DBMS_JOBÀ» ÀÌ¿ëÇϸé ƯÁ¤½Ã°£, ƯÁ¤¿äÀÏ, ƯÁ¤ÀÏ, 30ÃÊ´ÜÀ§, ¸ÅºÐ Á¤°¢, ¸Å½ÃÁ¤°¢
µî ´Ù¾çÇÏ°Ô ÀâÀ» ½ºÄÉÁÙ¸µ ÇÏ´Â °ÍÀÌ °¡´ÉÇÕ´Ï´Ù.
ETL ¼öÇà ÇÒ ¶§µµ À¯¿ëÇÏ°Ô »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
Àâ ¼öÇà °£°Ý Á¶Á¤ÀÇ ¿¹
.SYSDATE+ 7 : 7ÀÏ¿¡ Çѹø¾¿ ÀâÀÌ ¼öÇàµË´Ï´Ù.
.SYSDATE+1/24 : 1½Ã°£¿¡ Çѹø¾¿ ÀâÀÌ ¼öÇàµË´Ï´Ù.
.SYSDATE+30/86400 : 30ÃÊ¿¡ Çѹø¾¿ ÀâÀÌ ¼öÇàµË´Ï´Ù.
.ÃÖÃÊ Àâ ¼öÇà½Ã°£ÀÌ 14:02ºÐÀÏ °æ¿ì ¸Å½Ã 14:02ºÐ¿¡ ÀâÀ» ¼öÇàÇØ¾ß µÉ °æ¿ì
=>trunc(SYSDATE,'MI')+1/24
.ÃÖÁ¶ Àâ ¼öÇà½Ã°£ÀÌ 06½Ã ÀÌ°í ¸Å 8½Ã°£¸¶´Ù Á¤°¢¿¡ ÀâÀÌ ¼öÇàµÇ¾î¾ß µÉ °æ¿ì
=>trunc(SYSDATE,'MI')+8/24
.¸ÅÁÖ ¿ù¿äÀÏ Á¤°¢ 3½Ã¿¡ ÀâÀÌ ¼öÇàµÇ¾î¾ß ÇÒ °æ¿ì
=>NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25
.°¢ ºÐ±â¸¶´Ù ù¹ø° ¿ù¿äÀÏ¿¡ ÀâÀÌ ¼öÇàµÇ¾î¾ß µÉ °æ¿ì
=>NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY')
ÁÖÀÇ> dbms_jobÀ¸·Î ÀâÀ» ½ºÄÉÁÙ¸µÇÏ°Ô µÉ °æ¿ì ¿¹¸¦ µé¾î Àâ ÃÖÃÊ ¼öÇà½Ã°£ÀÌ 22ÀÏ 14:00½ÃÀÌ°í
¸Å½Ã Á¤°¢¿¡ ÀâÀÌ ¼öÇàµÇ¾î¾ß ÇÒ °æ¿ì SYSDATE+1/24·Î °£°ÝÀ» ÁÖ°Ô µÇ¸é ¸Å Á¤½Ã¿¡ µµ´Â °ÍÀÌ
¾Æ´Ï¶ó Àâ ¼öÇà ½Ã°£¿¡ µû¶ó ¾à°£¾¿ ´Ê¾îÁö°Ô µÇ¾î 14:00:04 => 15:00:07 => 16:00:10 ÀÌ·±½ÄÀ¸·Î
Àâ ¼öÇà½Ã°£ÀÌ ÀâÈ÷°Ô µË´Ï´Ù. µû¶ó¼ Á¤°¢¿¡ ¼öÇàµÇ°Ô ÇÏ·Á¸é truncÇÔ¼ö¸¦ ÀÌ¿ëÇؼ ¹«Á¶°Ç ºÐ¿¡¼
À߶󳻿© 00À¸·Î ¸¸µé¾î ÁØ´Ù. trunc(SYSDATE,'MI')
ÀâÀÇ »èÁ¦
BEGIN
DBMS_JOB.REMOVE(14443);
END;
/
14443Àº Àâ ¹øÈ£ ÀÌ´Ù. USER_JOBS µ¥ÀÌÅÍ µñ¼Å³Ê¸® ºä¸¦ º¸¸é Àâ ¹øÈ£¸¦ ¾Ë ¼ö ÀÖ½À´Ï´Ù.
ÀâÀÇ º¯°æ
BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/
ÀâÀ¸·Î µî·ÏµÈ ÇÁ·Î½ÃÀú/ÆÐÅ°Áö º¯°æ
BEGIN
DBMS_JOB.WHAT(14144,
'SP_IN_EMP_SAL;');
END;
/
Àâ ´ÙÀ½ ¼öÇà½Ã°£ º¯°æ
BEGIN
DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4);
END;
/
Àâ ¼öÇà °£°Ý º¯°æ
BEGIN
DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440);
END;
/
Àâ ¼öÇà Á¤Áö
ÀâÀÌ BROKENµÇ¸é ÀâÀº ¼öÇàµÇÁö ¾ÊÀ¸¸ç °Á¦·Î ¼öÇà ÇÒ ½Ã¿¡´Â DBMS_JOB.RUN()À» ÅëÇØ ¼öÇàÇÕ´Ï´Ù..
BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/
ÀâÅ¥ Á¤º¸ VIEWING
DBA_JOBS, USER_JOBS, ALL_JOBS¸¦ ÀÌ¿ëÇÕ´Ï´Ù.
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES B
------- --------- -------- -------- -
9125 01-JUN-01 00:00:00 4 N
14144 24-OCT-01 16:35:35 0 N
9127 01-JUN-01 00:00:00 16 Y
3 rows selected.
DBMS_JOBÀÇ È°¿ë¿¹Á¦¸¦ ÁÖ±âÀûÀ¸·Î ¼Ò½ºÅ×ÀÌºí¿¡¼ ŸÄÏ Å×À̺í·Î
ÀûÀçÇÒ ½Ã ¾î¶»°Ô »ç¿ëÇÒ ¼ö ÀÖ´ÂÁö °£´ÜÇÏ°Ô ¿¹¸¦ ¸¸µé¾ú½À´Ï´Ù
--¼öÇàµÉ ÀâÀÇ ¸ñ·ÏÀÌ µé¾î°¥ Å×À̺í
CREATE TABLE JOB_LIST
(JOB_ID VARCHAR2(2),
JOB_TYPE VARCHAR2(1),
JOB_NAME VARCHAR2(30),
JOB_EXEC_HOUR VARCHAR(2),
JOB_PARENTID VARCHAR2(2),
CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID)
USING INDEX
TABLESPACE CYS_INDEX
PCTFREE 0
STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0)
NOLOGGING)
TABLESPACE CYS_DATA
PCTFREE 0
STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0);
/
CREATE UNIQUE INDEX JOB_LIST_IDX01
ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR)
TABLESPACE CYS_INDEX
PCTFREE 0
STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0);
/
-Àâ ¼öÇà È÷½ºÅ丮 Å×À̺í·Î ÇÏ·ç¿¡ Çѹø¾¿ JOB_LIST Å×ÀÌºí¿¡¼ LOGÅ×À̺í·Î JOB_LIST°¡ º¹»çµÈ´Ù.
CREATE TABLE JOB_LOG
(JOB_ID VARCHAR2(2),
JOB_EXEC_DATE VARCHAR2(8),
JOB_START_TIME DATE,
JOB_END_TIME DATE,
JOB_DATASTART_TIME DATE,
JOB_DATAEND_TIME DATE,
JOB_STATUS VARCHAR2(1),
JOB_ERR_MSG VARCHAR2(100),
CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE)
USING INDEX
TABLESPACE CYS_INDEX
PCTFREE 0
STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0)
NOLOGGING,
CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID)
REFERENCES JOB_LIST(JOB_ID))
TABLESPACE CYS_DATA
STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
--JOB_ID¸¦ ºÎ¿©Çϱâ À§ÇÑ ½ÃÄö½º
CREATE SEQUENCE JOB_NUM
START WITH 1
INCREMENT BY 1
--ÇÏ·ç¿¡ Çѹø DBMS_JOBÀ» ÀÌ¿ëÇÏ¿© JOB_LISTÀÇ JOBµéÀ» JOB_LOG¿¡ INSERTÇϱâ À§ÇÑ ÇÁ·Î½ÃÁ®
--´ÙÀ½³¯ ¼öÇàÇÒ JOBÀ» LOG·Î ³Ö´Â´Ù.
CREATE OR REPLACE PROCEDURE
SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL)
AS
BEGIN
INSERT INTO JOB_LOG
SELECT JOB_ID,
NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')),
NULL,
NULL,
NULL,
NULL,
'N',
NULL
FROM JOB_LIST;
COMMIT;
END SP_IN_JOB_LOG;
--SP_IN_JOB_LOG ÇÁ·Î½ÃÀú¸¦ DBMS_JOB¿¡ µî·ÏÇÑ´Ù.
DECLARE
JOB_NUMBER NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB_NUMBER, --JOB¹øÈ£
'SP_IN_JOB_LOG;', --ÇÁ·Î½ÃÀú¸í
TO_DATE('20050208180000','YYYYMMDDHH24MISS'), --NEXT_DATE
'TRUNC(SYSDATE,''MI'')+1'); --Àâ ¼öÇà °£°Ý(¸ÅÀÏ Á¤°¢ 6½Ã)
END;
--JOB_LIST¿¡ ¼öÇàÇÒ ÇÁ·Î½ÃÁ®(JOB)À» µî·Ï
--DBA_USERSÅ×À̺íÀ» ÀÌ¿ëÇؼ 24°³ÀÇ ·Î¿ì¸¦ ¸¸µé¾î ³½ ÈÄ Ä«Å×½Ã¾È ÇÁ·¯´öÀ» ÀÌ¿ë
INSERT INTO JOB_LIST
SELECT JOB_NUM.NEXTVAL JOB_ID,
JOB_TYPE,
JOB_NAME,
B.CNT JOB_EXEC_HOUR,
NULL
FROM(
SELECT NULL JOB_ID,
'F' JOB_TYPE,
'SP_IN_F_SALE_SUM' JOB_NAME,
NULL JOB_EXEC_HOUR,
NULL
FROM DUAL) A,
( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES
WHERE ROWNUM<25) B
COMMIT;
--JOB_LIST¸¦ JOB_LOG·Î INSERT(ÇöÀç SP_IN_JOB_LOG °¡ ´ÙÀ½ÀÏÀ» INSERTÇϵµ·Ï µÇ¾î ÀÖÀ¸¹Ç·Î ÇØ´çÀÏÀ» ³Ö¾îÁÜ)
EXEC SP_IN_JOB_LOG('20050208');
--Á¦´ë·Î µé¾î°¬´ÂÁö È®ÀÎ
SELECT * FROM JOB_LOG;
--SOURCE Å×À̺íÀ» ½Ã°£´ÜÀ§·Î ¼¶¸Ó¸® Çؼ TARGET Å×À̺í·Î ÀûÀçÇϱâ À§ÇÑ ÇÁ·Î½ÃÁ®
--ERROR¾øÀÌ ¸Å ½Ã°£ µ¹ ¶§´Â ÆĶó¹ÌÅÍ ¾øÀÌ SP_IN_F_SALE_SUMÀ¸·Î ¼öÇà µÇ°í ¼öµ¿À¸·Î ¾î¶² µ¥ÀÌÅÍÀÇ
--¹üÀ§¸¦ ÀûÀçÇØ¾ß ÇÒ °æ¿ì ½Ã°£ÀÇ ¹üÀ§¸¦ ÆĶó¹ÌÅÍ·Î ³Ñ°ÜÁÜ
CREATE OR REPLACE PROCEDURE
SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL,
V_ENDTIME IN VARCHAR2 DEFAULT NULL)
AS
D_STARTTIME DATE;
D_ENDTIME DATE;
V_ERR_MSG VARCHAR2(100);
BEGIN
--ÇÁ·Î½ÃÀú°¡ ÆĶó¹ÌÅÍ °ªÀÌ ¾øÀÌ ¼öÇàµÉ °æ¿ì
IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN
SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24'))
INTO D_STARTTIME
FROM JOB_LOG
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24'))
AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD')
AND JOB_STATUS='Y';
D_ENDTIME:=TRUNC(SYSDATE,'HH24');
ELSE
SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS'))
INTO D_STARTTIME
FROM JOB_LOG
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8)
AND JOB_STATUS='Y';
D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS');
END IF;
--¼öÇàµÇ´Â ÇÁ·Î½ÃÀúÀÇ START½Ã°£À» Âï¾îÁÖ°í RUNNINGÀ¸·Î Ç¥½Ã
UPDATE JOB_LOG
SET JOB_START_TIME=SYSDATE,
JOB_STATUS='R'
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
-- DML------
--¼öÇàµÇ¾îÁú INSERT¹®
-- DML-----
COMMIT;
--ÇÁ·Î½ÃÀú°¡ ERROR¾øÀÌ ¼öÇàÀÌ ³¡³ª¸é END ½Ã°£°ú °¡Á®¿Â µ¥ÀÌÅÍÀÇ ¹üÀ§¸¦ Âï¾îÁÜ
UPDATE JOB_LOG
SET JOB_END_TIME=SYSDATE,
JOB_DATASTART_TIME=D_STARTTIME,
JOB_DATAEND_TIME=D_ENDTIME,
JOB_STATUS='Y'
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80);
UPDATE JOB_LOG
SET JOB_END_TIME=SYSDATE,
JOB_STATUS='E',
JOB_ERR_MSG=V_ERR_MSG
WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
WHERE JOB_NAME='SP_IN_F_SALE_SUM'
AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))
AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
COMMIT;
END SP_IN_F_SALE_SUM;
/
--SP_IN_F_SALE_SUM ÇÁ·Î½ÃÀú¸¦ DBMS_JOB¿¡ µî·ÏÇÕ´Ï´Ù.
DECLARE
JOB_NUMBER NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB_NUMBER, --JOB¹øÈ£
'SP_IN_F_SALE_SUM;', --ÇÁ·Î½ÃÀú¸í
TO_DATE('20050209000000','YYYYMMDDHH24MISS'), --NEXT_DATE
'TRUNC(SYSDATE,''MI'')+1/24'); --Àâ ¼öÇà °£°Ý(¸Å½Ã°£ Á¤°¢)
END;
JOBÀÇ ½Ã°£À̳ª °£°Ý µî·ÏµÈ ÇÁ·Î½ÃÀúµîÀ» º¯°æÇÏ°íÀÚ ÇÒ ¶§ DBMS_JOBÀÇ ´Ù¸¥ ÇÁ·Î½ÃÁ®¸¦ ÀÌ¿ëÇؼ º¯°æÇÕ´Ï´Ù.
2¹ø° Á¤¸® ºÎºÐ¿¡ ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù.
|
|
|