● JOB_SCHEDULER

■■■ 오라클 잡 스케줄러(JOB SCHEDULER) ■■■

■■■ 오라클 잡 스케줄러(JOB SCHEDULER) ■■■.txt

■■■ ▒ 오라클 잡 스케줄러(JOB SCHEDULER) ■■■

○ 개요

 - 데이터베이스에서 쿼리나 프로시저 등을 활용하여
   일정 주기별로 (연, 월, 일, 시간 등)
   원하는 작업을 자동으로 수행할 수 있도록 처리하는 객체.
   →▒ 오라클 잡 스케줄러(JOB SCHEDULER), 잡(JOB)

   ※ 오라클 10g 부터 지원

   - 오라클에서 프로시저라든지 특정 테이블에 데이터를 적재하는 등
     일정시간, 매일 또는 매달 실행하여 작업하는 경우가 종종 있다.
     이와 같은 경우 수동으로 특정 시간마다 등록하는 것이 아니라
     오라클에서 일정 시간마다 반복적으로 실행하도록 할 수 있다.

  - 스케줄러(SCHEDULER), 잡(JOB)에 반복하는 실행문을 등록하는 것은
    오라클 버전에 따라 크게 2가지 방법을 사용할 수 있다.

○ 『ORACLE JOB』 vs 『ORACLE SCHEDULER』
 
   - 『ORACLE JOB』은 PLSQL_BLOCK, STORED_PROCEDURE 만 구동
   - 오라클 10g 부터 『ORACLE SCHEDULER』를 지원
   - 『ORACLE SCHEDULER』 에서는 운영체제에 등록되는 SHELL 프로그램 등도 구동 가능

○ DBMS_JOB
    
  - 특정 시간 및 간격으로 특정 작업을 수행


○ DBMS_SCHEDULER
  
  - 복잡하고 정교한 수준의 스케줄 작업 가능


○ DBMS_SCHEDULER 의 장점

  - GUI(EM)을 통한 관리가 쉽다.
  - 모듈화된 스케줄 오브젝트를 통해 쉽게 공유할 수 있다.(프로그램, 스케줄, 잡)
  - TimeZone 이 지원된다.
  - (DataPump를 활용하여) 다른 데이터베이스 이동이 쉽다.


○ 오라클 스케줄러 등록을 위한 권한(기본 : SYS → SCOTT)
  
  - GRANT CREATE ANY JOB TO 계정명;
GRANT CREATE ANY JOB TO SCOTT;
-->> Grant을(를) 성공했습니다.


○ 오라클 스케줄러 등록을 위한 권한(추가 : SYS → SCOTT)

  - GRANT EXECUTE ON DBMS_SCHEDULER TO 계정명;
GRANT EXECUTE ON DBMS_SCHEDULER TO SCOTT;
--==>> Grant을(를) 성공했습니다.

  - GRANT EXECUTE ON DBMS_ISCHED TO 계정명;
GRANT EXECUTE ON DBMS_ISCHED TO SCOTT;
--==>> Grant을(를) 성공했습니다.

  - GRANT CREATE JOB, MANAGE SCHEDULER TO 계정명;
GRANT CREATE JOB, MANAGE SCHEDULER TO SCOTT;
--==>> Grant을(를) 성공했습니다.


○ 사용 절차(처리 순서)

 1. 작동할 프로그램 등록
    : 스케줄러가 돌면서 실제로 구동될 프로그램을 등록한다.
 2. 스케줄 등록
    : 주기적으로 돌아갈 스케줄을 등록한다.
 3. 잡 등록
    : 실제 수행할 업무로, 스케줄과 프로그램을 명시해준다.

 ※ 프로그램(PROGRAM)
    - 실행 가능한 프로그램(Program)을 말한다.
    - DBMS_JOB 은 PL/SQL 블록, 저장 프로시저(Stored Procedure)만 가능
    - DBMS_SCHDULER 는 외부 프로그램까지 사용 가능
      (PL/SQL 블록, 저장 프로시저(Stored Procedure), 실행 파일(Executetable, Unix Shell, Windows 실행 파일 등)
    - DBMS_SCHDULER.CREATE_PROGRAM 프로시저를 통해 등록 가능하며
      [ALL/DBA/USER]_SCHEDULER_PROGRAMS 뷰를 통해 확인 가능
    - 스케줄링에 의해 실행되는 대상(프로그램 이름, 타입, Argument 갯수 등)

 ※ 스케줄(SCHEDULE)
    - 작업을 수행할 스케줄을 말한다.
    - 작업 시작 시각, 종료 시각, 간격 등을 지정할 수 있다.
    - DBMS_JOB 에 비해 유연하다.
      ex) "FREQ=YEARLY; BYMONTH=4; BYMONTHDAY=15; BYHOUR=9; BYMINUTE=0; BTSECOND=0"
          → 매년 4월 15일 9시 정각에 작업 수행
    - DBMS_SCHDULER.CREATE_SCHECULERS 프로시저를 통해 등록할 수 있으며
      [ALL/DBA/USER]_SCHEDULER_SCHDULES 뷰를 통해 확인 가능
 
 ※ 잡(JOB)
    - 오라클에서 스케줄러를 통해 특정 작업을 수행할 때, 
      그 단위를 잡(JOB) 이라고 한다.
    - 주어진 프로그램과 스케줄에 따라 수행할 작업을 말한다.(언제, 무엇을)
    - 명시적으로 생성된 프로그램과 스케줄을 이용할 수도 있고,
      작업을 생성하면 암묵적인 프로그램과 스케줄을 생성할 수도 있다.
    - DBMS_SCHEDULER.CREATE_JOB 프로시저를 통해 등록할 수 있으며
      [ALL/DBA/USER]_SCHEDULER_JOBS 뷰를 통해 확인 가능
    - 작업이 수행되면서 남게되는 로그 데이터는 
      [ALL/DBA/USER]_SCHEDULER_JOB_LOG 뷰나
      [ALL/DBA/USER]_SCHEDULER_JOB_RUN_DETAILS 뷰를 통해 확인 가능

 ※ 잡 클래스(JOB CLASS)
    - 작업의 공통 속성을 묶어서 만든 분류를 말한다.
    - Resource Consumer Group, Service, Logging Level, Log History 의 속성을 조합하여
      하나의 클래스를 생성한다.
    - Resource Consumer Group 은 DBMS_RESOURCE_MANAGER 패키지를 통해 생성하며, 
      자원을 얼마나 사용 가능하게 할 것인지를 지정한다.
    - Service 는 작업이 특정 서비스에 대한 리소스 친화도(Resource Affinity)를 가지도록 지정한다.
      즉, Service 는 RAC에서 클래스터 내의 여러 노드를 묶은 논리적인 그룹이다.
    - Logging Level 은 작업 실행에 대한 로그 데이터의 레벨을 지정한다.
    - Log History 는 로그 데이터를 얼마나 저장할지를 지정한다.
    - 같은 작업 클래스에 속하는 작업은 같은 속성을 공유하기 때문에
      관리 상의 편의성을 제공한다.
    - 하나의 JOB 은 하나의 JOB CLASS 에만 소속된다.


 ※ 윈도우(WINDOW)
    - 특정 리소스 플랜(Resource Plan)을 적용하는 시간 단위를 의미한다.
    - 리소스 플랜은 오라클이 자원을 관리하는 단위로
      작업의 종료에 따라 CPU 등의 자원을 얼마나 부여할지를 지정하는 역할을 한다.
      (윈도우를 지정하게 되면 해당 윈도우 안에서 실행되는 작업은
       윈도우 생성 시 지정한 리소스 플랜을 사용하게 된다.)

※ 체인(CHAIN)
   - 프로그램의 집합을 의미한다.
   - 일련의 프로그램들을 순서대로 수행해야 할 때 사용한다.
   - 체인은 ORACLE 10g R2 에서 추가된 기능이다.
   - 스케줄 작업과 RAC Instnace
   - DBMS_JOB => 작업을 수행할 인스턴스 번호를 지정(하나의 노드로 지정하면 편의성 향상)
   - DBMS_SCHEDULER => Instance_Stickness 라는 개념을 통해 지능적으로 인스턴스를 할당
   - INSTANCE_STICKNESS => TRUE(DBMS_SCHEDULER.SET_ATTRIBUTE) 의미
   - 작업 수행 시 현재 가장 부하가 적은 인스턴스 작업을 분배한다.
     이후 작업 수행 시에는 가능한 최초에 지정된 인스턴스에서 작업.
   - 이와 같은 성격을 인스턴스 접착도라고 하며 리소스 친화도를 구현하는 방법
   - 최초 지정된 인스턴스가 다운되거나, 부하가 심해 작업을 수행할 수 없을 때
     다른 인스턴스에서 작업을 수행하게 된다.
   - INSTANCE_STICKNESS 속성 값이 FALSE 이면, 오라클은 인스턴스 순서대로 작업을 수행한다.
     즉, DBMS_JOB 패키지를 이용해 작업을 수행하되
     인스턴스 번호를 지정하지 않은 경우와 거의 같은 방식으로 작동한다.

○ 제거
 
  1. 잡 제거
  2. 프로그램 제거
  3. 스케줄 제거

  ※ JOB 를 먼저 지우고 PROGRAM 을 지운다.
     PROGRAM 을 먼저 제거하게 되면 종속된 객체이기 때문에 에러 발생한다.
     스케줄도 같은 방법으로 처리한다.

○ 오라클 스케줄러 명령 프로시저

 - DBMS_SCHEULER.CREATE_PROCEDURE
   : 잡이 스케줄러의 설정대로 구동하며
     실제 동작하는 프로그램(외부 SHELL, SP, PLSQL_BLOCK 등)을 등록한다.

 - DBMS_SCHEDULER.CREATE_SCHEDULER
   : 주기적으로 돌아갈 스케줄을 등록한다.

 - DBMS_SCHEDULER.CREATE_JOB
   : 수행할 작업을 등록한다.

 - DBMS_SCHEDULER.DROP_JOB
   : 스케줄을 삭제한다.

 - DBMS_SCHEDULER.ENABLE
   : 스케줄을 시작한다.

 - DBMS_SCHEDULER.DISABLE
   : 스케줄을 정지한다.


○ 오라클 잡 명령 프로시저

  - DBMS_JOB.SUBMIT
    : 잡을 등록한다.

  - DBMS_JOB.REMOVE
    : 잡을 제거한다.

  - DBMS_JOB.CHANGE
    : 잡의 FIELD 를 변경한다.
 
  - DBMS_JOB.NEXT_DATE
    : 잡의 다음 수행시간(자동으로 실행될 때)을 변경한다.

 - DBMS_JOB.INTERVAL
   : 잡의 실행 반복 주기(CYCLE) 파라미터를 변경한다.

 - DBMS_JOB.WHAT
   : 잡의 수행 업무로 등록된 객체(OBJECT)를 변경한다.

 - DBMS_JOB.RUN
   : 잡을 수동으로 실행(현재 SESSION 에서 즉시 실행)한다.

 - DBMS_JOB.JOB_BROKEN
   : 잡의 상태를 정상 또는 BROKEN 상태로 설정한다.


○ 등록 정보 확인(스케줄러 관련 DBMS 조회 구문)

  SELECT *
  FROM USER_SCHEDULER_JOBS; -- 등록된 JOB

  SELECT *
  FROM USER_SCHDULER_JOB_ARGS; -- JOB 의 매개변수(ARGUMENTS)

  SELECT *
  FROM USER_SCHEDULER_RUNNING_JOBS; -- 구동중인(RUNNING) JOB 들의 정보

  SELECT *
  FROM USER_SCHEDULER_JOB_LOG; -- JOB 의 LOG

  SELECT *
  FROM USER_SCHEDULER_JOB_RUN_DETAILS; -- JOB 의 수행된 정보 및 에러 정보

  SELECT *
  FROM USER_SCHEDULER_PROGRAMS; -- 등록된 PROGRAM

  SELECT *
  FROM USER_SCHEDULER_PROGRAMS_ARGS; -- PROGRAM 의 매개변수(ARGUMENTS)

  SELECT *
  FROM USER_SCHEDULER_SCHEDULES; -- 등록된 스케줄러(SCHEDULER)

 
○ INTERVAL 설정 방법(WHEN / HOW MANY 에 대한 정의)

 Type A. - PL/SQL 표현 방법(USING PL/SQL EXPRESSION)
    REPEAT_INTERVAL => 'SYSDATE+36/24'

 Type B. 달력 표현 방법(USING CALENDAR EXPRESSION)
    REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=4'
    REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDATE=15'
 
ex)

'SYSDATE + 1'
-- 매일(Execute daily)
--  → 현재 시간으로부터 다음나 현재 시간에 실행
--     즉, 현재 시간이 오후 3시면 매일 오후 3시에 실행

'SYSDATE + 7'
-- 일주일에 한 번(Execute once per week)

'SYSDATE + 1/24'
-- 한 시간에 한 번(Execute hourly)

'SYSDATE + 10/1440'
'SYSDATE + 1/2416'
-- 10분에 한 번(Execute every 10 min)

'SYSDATE + 30/86400'
-- 30초에 한 번(Execute every 30 sec)

'SYSDATE + 1/1440'
-- 1분에 한 번

NULL
-- 다시 실행하지 않음(Do not re-execute)

'TRUNC(SYSDATE) + 1 + 5 / 24'
-- 매일 새벽 5시에 실행

'TRUNC(SYSDATE) + 22 / 24'
-- 매일 밤 10시에 실행

JOB_SCHEDULER_sys.sql

SELECT USER
FROM DUAL;
--==>> SYS

-- 권한 부여(SYS → SCOTT)
GRANT CREATE ANY JOB TO SCOTT;
-->> Grant을(를) 성공했습니다.

GRANT EXECUTE ON DBMS_SCHEDULER TO SCOTT;
--==>> Grant을(를) 성공했습니다.

GRANT EXECUTE ON DBMS_ISCHED TO SCOTT;
--==>> Grant을(를) 성공했습니다.

GRANT CREATE JOB, MANAGE SCHEDULER TO SCOTT;
--==>> Grant을(를) 성공했습니다.

JOB_SCHEDULER_A_sys.sql

SELECT USER
FROM DUAL;
--==>> SCOTT

-- 스케줄러 정상 작동여부 확인을 위한
-- 테이블 및 프로시저 생성

--○ 실습 테이블 생성(TBL_TEST)
CREATE TABLE TBL_TEST
( NOW_COL   VARCHAR2(30)
);
--==>> Table TBL_TEST이(가) 생성되었습니다.

--○ 실습 프로시저 생성(PRC_TEST)
CREATE OR REPLACE PROCEDURE PRC_TEST
(P_NOW_COL IN VARCHAR2
)
IS
BEGIN
    INSERT INTO TBL_TEST(NOW_COL) VALUES(P_NOW_COL);
    COMMIT;
END;
--==>> Procedure PRC_TEST이(가) 컴파일되었습니다.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.

--○ 테스트(확인)
SELECT TRUNC(SYSDATE) + 12/24
FROM DUAL;
--==>> 2024-02-19 12:00:00

--○ 스케줄러 잡 등록
BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    ( JOB_NAME => 'JOB_PRC_TEST'
    -- 잡 이름은 동록된 잡 이름들 중 유니크하기만 하면 되고,
    -- 스케줄러에서 잡을 삭제할 때 잡 이름을 파라미터로 입력하여 삭제하게 된다.
    , START_DATE => TRUNC(SYSDATE) + 12/24
    , REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1'
    -- START_DATE 와 REPEAT_INTERVAL 은 스케줄러에 등록한 잡이
    -- 일정 시간마다 반복되도록 하는 옵션으로 서로 연관 관계가 있다.
    -- START DATE 는 잡이 최초로 실행되는 시간을 지정하게 되며
    -- REPEATE_INTERVAL 은 반복되는 형식을 지정하게 된다.
    -- 만약, 위에서 START_DATE 에  TRUNC(SYSDATE+1) + 12/24 와 같이 지정했다면
    -- TRUNC(SYSDATE+1) 은 내일을 의미한다.
    -- 즉, 『SELECT TRUNC(SYSDATE+1) FROM DUAL』과 같이 쿼리문을 실행하면
    -- 내일 일자가 조회되는 것을 확인할 수 있다.
    -- 『12/24』는 정오를 의미하는 값이 된다.
    , END_DATE => SYSDATE + 5
    -- END_DATE 는 잡의 만료 시간을 의미한다.
    -- 영구적인 반복 실행을 설정하려면 NULL 을 지정하게 된다.
    -- 시간과 관계없이 일주일만 실행되기를 바란다면
    -- 『TRUNC(SYSDATE+7)』과 같이 등록하게 된다.
    --, JOB_CLASS => 'DEFAULT_JOB_CLASS'
    , JOB_TYPE => 'PLSQL_BLOCK'
    , JOB_ACTION => 'BEGIN PRC_TEST(TO_CHAR(SYSDATE, ''YYYY-MM-DD HH24:MI:SS''));END;'
    , COMMENTS => 'JOB 등록 실습'
    );
    
    DBMS_SCHEDULER.ENABLE('JOB_PRC_TEST');
END;
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 스케줄러 잡 등록 내역 확인
SELECT *
FROM USER_SCHEDULER_JOBS
WHERE JOB_NAME = 'JOB_PRC_TEST' ;
--==>> 
/*
JOB_PRC_TEST		REGULAR	SCOTT					PLSQL_BLOCK	BEGIN PRC_TEST(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));END;	0			CALENDAR	24/02/19 12:00:00.000000000 +09:00	FREQ=DAILY; INTERVAL=1								24/02/24 11:11:16.000000000 +09:00	DEFAULT_JOB_CLASS	TRUE	TRUE	FALSE	SCHEDULED	3	0		0		0			24/02/19 12:00:00.000000000 +09:00			OFF	FALSE	TRUE		FALSE	1	NLS_LANGUAGE='KOREAN' NLS_TERRITORY='KOREA' NLS_CURRENCY='￦' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH24:MI:SSXFF' NLS_TIMESTAMP_FORMAT='RR/MM/DD HH24:MI:SSXFF' NLS_TIME_TZ_FORMAT='HH24:MI:SSXFF TZR' NLS_TIMESTAMP_TZ_FORMAT='RR/MM/DD HH24:MI:SSXFF TZR' NLS_DUAL_CURRENCY='￦' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'		1						FALSE	FALSE	JOB 등록 실습	133168
*/

--○ 지정한 시간에 스케줄러 잡이 정상적으로 실행되었는지 확인
SELECT *
FROM USER_SCHEDULER_JOB_LOG
WHERE JOB_NAME = 'JOB_PRC_TEST';
--( 11:24 기준 )
--==>> 조회 결과 없음
--( 12:22 기준 )
--==>>
/*
8992	24/02/19 12:21:48.099000000 +09:00	SCOTT	JOB_PRC_TEST		DEFAULT_JOB_CLASS	RUN	SUCCEEDED								
*/


SELECT *
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'JOB_PRC_TEST';
--( 11:25 기준 )
--==>> 조회 결과 없음
--( 12:22 기준 )
--==>>
/*
8992	24/02/19 12:21:48.114000000 +09:00	SCOTT	JOB_PRC_TEST		SUCCEEDED	0	24/02/19 12:00:00.000000000 +09:00	24/02/19 12:21:48.099000000 +09:00	+00 00:00:00.000000	1	74,645	10832	+00 00:00:00.000000					
*/

--※ ALL...DETAILS, DBA...DETAILS, USER...DETAILS 와 같이
--   『DETAILS』가 붙은 스케줄러 잡 로그 딕셔너리 뷰는 
--   실행 실패 관련 에러 내용을 조회할 수 있다.

SELECT *
FROM TBL_TEST;
--( 11:28 기준 )
--==>> 조회 결과 없음
--( 12:23)
--==>>
/*
2024-02-19 12:21:48
*/


--※ 잡 삭제 구문
EXEC DBMS_SCHEDULER.DROP_JOB('JOB_PRC_TEST');

--※ 프로시저 삭제 구문
DROP PROCEDURE PRC_TEST;

--※ 테이블 삭제 구문
DROP TABLE TBL_TEST;

JOB_SCHEDULER_B_scott.sql

SELECT USER
FROM DUAL;
--==>> SCOTT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.

SET SERVEROUTPUT ON;
--==>> 작업이 완료되었습니다.(0.031초)



--○ 실습 테이블 생성(TBL_JOBTEST)
CREATE TABLE TBL_JOBTEST
( SID       NUMBER
, NAME      VARCHAR2(30)
, TEL       VARCHAR2(40)
, REG_DATE  DATE        DEFAULT SYSDATE
, CONSTRAINT JOBTEST_SID_PK PRIMARY KEY(SID)
);
--==>> Table TBL_JOBTEST이(가) 생성되었습니다.


--○ 시퀀스 생성(JOBTESTSEQ)
CREATE SEQUENCE JOBTESTSEQ
NOCACHE;
--==>> Sequence JOBTESTSEQ이(가) 생성되었습니다.


--○ 프로시저 생성(PRC_JOBTEST)
CREATE OR REPLACE PROCEDURE PRC_JOBTEST
IS
    V_NUM   NUMBER := 0;
BEGIN
    V_NUM := V_NUM + 1;
    
    INSERT INTO TBL_JOBTEST
    VALUES(JOBTESTSEQ.NEXTVAL, '확인'||V_NUM, '010-1212-3434', DEFAULT);
    
    COMMIT;
    
    EXCEPTION
        WHEN OTHERS THEN ROLLBACK;
        
END;
--==>> Procedure PRC_JOBTEST이(가) 컴파일되었습니다.

--○ 프로그램 등록
BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM
    ( PROGRAM_NAME => 'TEST_PRG'
    , PROGRAM_ACTION
    )
END;

JOB_SCHEDULER_C_scott.sql

SELECT USER
FROM DUAL;
--==>> SCOTT

-- FINAL PROJECT 일부 테이블 샘플링
-- (그룹초대, 직위 72시간 지나면 무응답 / 그룹초대)

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.

SET SERVEROUTPUT ON;

-- 회원 테이블 생성
CREATE TABLE USERS
(US_CODE NUMBER PRIMARY KEY
,US_ID VARCHAR2(30)     NOT NULL UNIQUE
,US_PWD VARCHAR2(40)    NOT NULL
,US_SIGNUP  DATE    DEFAULT SYSDATE NOT NULL
,US_PROFILE VARCHAR2(300)
);
--==>> Table USERS이(가) 생성되었습니다.

DROP TABLE USERS;


INSERT INTO USERS(US_CODE, US_ID, US_PWD) 
VALUES(1, 'USER1', '1234567');
INSERT INTO USERS(US_CODE, US_ID, US_PWD) 
VALUES(2, 'USER2', '2345678');

INSERT INTO USERS(US_CODE, US_ID, US_PWD) 
VALUES(3, 'USER3', '3345678');
INSERT INTO USERS(US_CODE, US_ID, US_PWD) 
VALUES(4, 'USER4', '4445678');
INSERT INTO USERS(US_CODE, US_ID, US_PWD) 
VALUES(5, 'USER5', '5545678');

SELECT *
FROM USERS;

COMMIT;
--==>> 커밋 완료.

--친구 테이블 생성
CREATE TABLE FRIENDS
(FR_CODE NUMBER PRIMARY KEY
 ,US_CODE1 NUMBER   NOT NULL
 ,US_CODE2 NUMBER   NOT NULL
 ,FR_DATE DATE DEFAULT SYSDATE  NOT NULL
 ,CONSTRAINT US_CODE1_FK FOREIGN KEY(US_CODE1)
             REFERENCES USERS(US_CODE)
 , CONSTRAINT US_CODE2_FK FOREIGN KEY(US_CODE2)
              REFERENCES USERS(US_CODE)
);
--==>> Table FRIENDS이(가) 생성되었습니다.

DROP TABLE FRIENDS;

INSERT INTO FRIENDS(FR_CODE, US_CODE1, US_CODE2)
VALUES(1, 1, 2);
INSERT INTO FRIENDS(FR_CODE, US_CODE1, US_CODE2)
VALUES(2, 1, 3);
INSERT INTO FRIENDS(FR_CODE, US_CODE1, US_CODE2)
VALUES(3, 1, 4);
INSERT INTO FRIENDS(FR_CODE, US_CODE1, US_CODE2)
VALUES(4, 1, 5);

SELECT *
FROM FRIENDS;
--==>> 1	1	2	2024-02-20 10:53:48

COMMIT;

--그롭 카테고리 테이블 생성
CREATE TABLE GROUP_CATEGORY
(GC_CODE NUMBER PRIMARY KEY
,GC_NAME VARCHAR2(100) NOT NULL
);
--==>> Table GROUP_CATEGORY이(가) 생성되었습니다.

INSERT INTO GROUP_CATEGORY(GC_CODE, GC_NAME)
VALUES (1, '스포츠');
--==>> 1 행 이(가) 삽입되었습니다.

DROP TABLE GROUP_CATEGORY;

-- 그룹 개설 테이블 생성
CREATE TABLE CREATE_GROUP
(CG_CODE NUMBER PRIMARY KEY
,CG_NAME VARCHAR2(50)   NOT NULL
,GC_CODE NUMBER         NOT NULL
,CG_INTRO VARCHAR2(100)
,CG_DATE DATE   DEFAULT SYSDATE NOT NULL
,CG_PROFILE VARCHAR2(300)
,US_CODE NUMBER NOT NULL
,CONSTRAINT GC_CODE_FK FOREIGN KEY(GC_CODE)
            REFERENCES GROUP_CATEGORY(GC_CODE)
,CONSTRAINT US_CODE_FK FOREIGN KEY(US_CODE)
            REFERENCES USERS(US_CODE) 
);
--==>> Table CREATE_GROUP이(가) 생성되었습니다.

INSERT INTO CREATE_GROUP(CG_CODE, CG_NAME, GC_CODE, US_CODE)
VALUES(1, '그룹1', 1, 1);
--==>> 1 행 이(가) 삽입되었습니다.

COMMIT;
--==> 커밋 완료.

DROP TABLE CREATE_GROUP;

CREATE TABLE RESPONSE
(RS_CODE NUMBER PRIMARY KEY
,RS_STATUS VARCHAR2(30) NOT NULL
);
--==>> Table RESPONSE이(가) 생성되었습니다.

INSERT INTO RESPONSE(RS_CODE, RS_STATUS)
VALUES(1, '수락');
INSERT INTO RESPONSE(RS_CODE, RS_STATUS)
VALUES(2, '거절');
INSERT INTO RESPONSE(RS_CODE, RS_STATUS)
VALUES(3, '무응답');
INSERT INTO RESPONSE(RS_CODE, RS_STATUS)
VALUES(4, '요청중');



SELECT *
FROM RESPONSE;

DROP TABLE RESPONSE;

CREATE TABLE GROUP_INVITE
(GI_CODE NUMBER PRIMARY KEY
,FR_CODE NUMBER NOT NULL
,CG_CODE NUMBER NOT NULL
,GI_REQUEST DATE DEFAULT SYSDATE
,GI_RESPONSE DATE 
,RS_CODE NUMBER
,CONSTRAINT FR_CODE_FK FOREIGN KEY(FR_CODE)
            REFERENCES FRIENDS(FR_CODE)
,CONSTRAINT CG_CODE_FK FOREIGN KEY(CG_CODE)
            REFERENCES CREATE_GROUP(CG_CODE)
,CONSTRAINT RS_CODE_FK FOREIGN KEY(RS_CODE)
            REFERENCES RESPONSE(RS_CODE)
);
--==>> Table GROUP_INVITE이(가) 생성되었습니다.

INSERT INTO GROUP_INVITE(GI_CODE, FR_CODE, CG_CODE, RS_CODE)
VALUES(1, 1, 1, 4);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO GROUP_INVITE(GI_CODE, FR_CODE, CG_CODE, RS_CODE)
VALUES(2, 2, 1, 4);
INSERT INTO GROUP_INVITE(GI_CODE, FR_CODE, CG_CODE, RS_CODE)
VALUES(3, 3, 1, 4);
INSERT INTO GROUP_INVITE(GI_CODE, FR_CODE, CG_CODE, RS_CODE)
VALUES(4, 4, 1, 4);

UPDATE GROUP_INVITE
SET GI_REQUEST = TO_DATE('2024-02-23 10:29:23', 'YYYY-MM-DD HH24:MI:SS')
WHERE GI_CODE = 4;

DROP TABLE GROUP_INVITE;

DELETE
FROM GROUP_INVITE
WHERE GI_CODE=1;

COMMIT;

SELECT *
FROM GROUP_INVITE;
--==>> 1	1	1	2024-02-20 12:47:56		4


-- 프로시저 구성
CREATE OR REPLACE PROCEDURE PRC_GROUP_INVITE
IS
    V_GI_REQUEST GROUP_INVITE.GI_REQUEST%TYPE;
    V_GI_CODE GROUP_INVITE.GI_CODE%TYPE;
    
    
    CURSOR CUR_GROUP_INVITE
    IS 
    SELECT GI_CODE
    FROM GROUP_INVITE
    WHERE GI_RESPONSE IS NULL;
    
BEGIN
    
    OPEN CUR_GROUP_INVITE;
    
        LOOP
            FETCH CUR_GROUP_INVITE INTO V_GI_CODE;
            
            EXIT WHEN CUR_GROUP_INVITE%NOTFOUND;
            
            SELECT GI_REQUEST INTO V_GI_REQUEST
            FROM GROUP_INVITE
            WHERE GI_CODE = V_GI_CODE;
            
            IF (TRUNC(V_GI_REQUEST + 3) <= SYSDATE)  THEN
            
            UPDATE GROUP_INVITE
            SET RS_CODE = 3 , GI_RESPONSE = SYSDATE
            WHERE GI_CODE = V_GI_CODE;
            
            END IF;
                    
        END LOOP;
    
    CLOSE CUR_GROUP_INVITE;
    
    COMMIT;
    
    EXCEPTION
        WHEN OTHERS THEN ROLLBACK;
END;
--==>> Procedure PRC_GROUP_INVITE이(가) 컴파일되었습니다.

DROP PROCEDURE PRC_GROUP_INVITE;



-- 자동화 업무 수행
BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM
    ( PROGRAM_NAME => 'GROUP_INVITE_PRG'
    , PROGRAM_ACTION => 'PRC_GROUP_INVITE'
    , PROGRAM_TYPE => 'STORED_PROCEDURE'
    , COMMENTS => 'GROUP_INVITE_PROGRAM'
    , ENABLED => TRUE
    );
END;

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE
    ( SCHEDULE_NAME => 'SCHEDULE_2_DAY'
    , START_DATE => TO_DATE('2024-02-25 10:31:00', 'YYYY-MM-DD HH24:MI:SS')
    , END_DATE => NULL
    , REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1'
    , COMMENTS => 'Every 1 day'
    );
END;


BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (  JOB_NAME => 'GROUP_INVITE_JOB'
     , PROGRAM_NAME => 'GROUP_INVITE_PRG'
     , SCHEDULE_NAME => 'SCHEDULE_2_DAY'
     , ENABLED => TRUE
    );
END;



--※ 삭제 처리 구문(프로그램 삭제)
EXECUTE DBMS_SCHEDULER.DROP_PROGRAM('GROUP_INVITE_PRG', FALSE);
--※ 삭제 처리 구문(스케줄 삭제)
EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('SCHEDULE_2_DAY', FALSE);
--※ 삭제 처리 구문(잡 삭제)
EXECUTE DBMS_SCHEDULER.DROP_JOB('GROUP_INVITE_JOB', FALSE);


SELECT *
FROM GROUP_INVITE;

-- 테스트 및 확인
SELECT *
FROM USER_SCHEDULER_JOBS;
--==>>

SELECT *
FROM USER_SCHEDULER_RUNNING_JOBS;
--==>> 조회 결과 없음

SELECT *
FROM USER_SCHEDULER_JOB_RUN_DETAILS;


--※ JOB의 LOG 정보 확인
SELECT *
FROM USER_SCHEDULER_JOB_LOG;
--==>>