-- 의사 결정 트리 모델로 퇴사자 예측 모델 만들기
-- 데이터셋 : https://www.kaggle.com/datasets/pankeshpatel/hrcommasep
-- 기존에 있을지도 모를 HR 데이터 삭제
-- 퇴사 여부 예측 (LEFT)
 
SELECT COUNT(*) FROM HR_DATA_MAIN;
-- 14999

-- 훈련 데이터와 테스트 데이터로 분리
DROP TABLE HR_DATA_TRAINING; 
CREATE TABLE HR_DATA_TRAINING
AS
   SELECT *
     FROM HR_DATA_MAIN
     WHERE EMP_ID < 10500;

DROP TABLE HR_DATA_TEST;

CREATE TABLE HR_DATA_TEST
AS
   SELECT *
     FROM HR_DATA_MAIN
     WHERE EMP_ID >= 10500;

-- 머신 러닝 모델의 환경설정을 위한 정보가 들어있는 테이블을 생성합니다. 
-- URL : https://docs.oracle.com/database/121/ARPLS/d_datmin.htm#ARPLS192
DROP TABLE DTSETTINGS;
CREATE TABLE DTSETTINGS
AS
SELECT *
  FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
  WHERE SETTING_NAME LIKE '%GLM%';

BEGIN
  INSERT INTO DTSETTINGS
    VALUES ('ALGO_NAME', 'ALGO_DECISION_TREE');

  INSERT INTO DTSETTINGS
     VALUES (DBMS_DATA_MINING.TREE_IMPURITY_METRIC, 'TREE_IMPURITY_ENTROPY'); -- 모델의 핵심엔진은 엔트로피로 설정
COMMIT;
END;
/

-- 머신 러닝 모델을 생성합니다.
BEGIN
  DBMS_DATA_MINING.DROP_MODEL('DT_MODEL');
END;
/

BEGIN
   DBMS_DATA_MINING.CREATE_MODEL (
      MODEL_NAME            => 'DT_MODEL',
      MINING_FUNCTION       => DBMS_DATA_MINING.CLASSIFICATION,
      DATA_TABLE_NAME       => 'HR_DATA_TRAINING',
      CASE_ID_COLUMN_NAME   => 'EMP_ID',
      TARGET_COLUMN_NAME    => 'LEFT',
      SETTINGS_TABLE_NAME   => 'DTSETTINGS');
END;
/

-- 5. 생성된 모델을 확인합니다.
SELECT MODEL_NAME,
          ALGORITHM,
          MINING_FUNCTION
  FROM ALL_MINING_MODELS
  WHERE MODEL_NAME = 'DT_MODEL';

-- 6. 생성된 모델의 환경설정 내용을 확인합니다. 
SELECT SETTING_NAME, SETTING_VALUE
  FROM ALL_MINING_MODEL_SETTINGS
  WHERE MODEL_NAME = 'DT_MODEL';

-- 7. 실제 값과 예측 값과 예측 확률을 출력합니다. 
SELECT EMP_ID, T.LEFT 실제값,
          PREDICTION (DT_MODEL USING *) 예측값,
          PREDICTION_PROBABILITY (DT_MODEL USING *) "모델이 예측한 확률"
  FROM HR_DATA_TEST T;

-- 8. 학습한 머신러닝 모델의 성능을 확인합니다.
DROP TABLE HR_DATA_TEST_MATRIX_2;
CREATE OR REPLACE VIEW   VIEW_HR_DATA_TEST
AS
SELECT EMP_ID, PREDICTION(DT_MODEL USING *) PREDICTED_VALUE,
          PREDICTION_PROBABILITY(DT_MODEL USING * ) PROBABILITY
  FROM HR_DATA_TEST;
  
SET SERVEROUTPUT ON 

DECLARE
   V_ACCURACY NUMBER;
BEGIN
   DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
      ACCURACY           => V_ACCURACY,
      APPLY_RESULT_TABLE_NAME      => 'VIEW_HR_DATA_TEST',
      TARGET_TABLE_NAME       => 'HR_DATA_TEST',
      CASE_ID_COLUMN_NAME       => 'EMP_ID',
      TARGET_COLUMN_NAME       => 'LEFT',
      CONFUSION_MATRIX_TABLE_NAME => 'HR_DATA_TEST_MATRIX_2',
      SCORE_COLUMN_NAME       => 'PREDICTED_VALUE',
      SCORE_CRITERION_COLUMN_NAME => 'PROBABILITY',
      COST_MATRIX_TABLE_NAME      => NULL,
      APPLY_RESULT_SCHEMA_NAME    => NULL,
      TARGET_SCHEMA_NAME       => NULL,
      COST_MATRIX_SCHEMA_NAME     => NULL,
      SCORE_CRITERION_TYPE       => 'PROBABILITY');
   DBMS_OUTPUT.PUT_LINE('**** MODEL ACCURACY ****: ' || ROUND(V_ACCURACY,4));
END;
/

-- 프로시저
-- 함수와 다르게 로직 처리만 수행 / 결괏값 반환 (X)
-- p.290
SELECT * FROM JOBS;

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE
 , p_max_sal IN JOBS.MAX_SALARY%TYPE
)
IS
BEGIN
    INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
    VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    
    COMMIT;
END;

-- 프로시저 실행
-- exec
EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 1000, 5000);

-- 확인
SELECT * 
FROM jobs
WHERE job_id = 'SM_JOB1';

-- 프로시저 업데이트 
-- 끊기면 안되고, 계속 실행이 되어야 함

CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE
 , p_max_sal IN JOBS.MAX_SALARY%TYPE
)
IS
    vn_cnt NUMBER := 0;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = SYSDATE
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;

-- 프로시저 실행
-- exec
EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 1000, 5000);

SELECT * 
FROM jobs
WHERE job_id = 'SM_JOB1';

-- p.293
-- 한줄로 실행. (이유 모름)
EXECUTE my_new_job_proc (p_job_id => 'SM_JOB1', p_job_title => 'Sample JOB1', p_min_sal => 2000, p_max_sal => 7000);

SELECT * FROM jobs WHERE job_id = 'SM_JOB1';

-- 매개변수 디폴트 설정
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE := 10
 , p_max_sal IN JOBS.MAX_SALARY%TYPE := 100
)
IS
    vn_cnt NUMBER := 0;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = SYSDATE
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;

EXECUTE my_new_job_proc('SM_JOB1', 'Sample JOB1');
SELECT * FROM jobs WHERE job_id = 'SM_JOB1';

-- OUT, IN OUT 매개변수
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE := 10
 , p_max_sal IN JOBS.MAX_SALARY%TYPE := 100
 , p_upd_date OUT JOBS.UPDATE_DATE%TYPE -- 갱신일자 값을 반환한다! 
)
IS
    vn_cnt NUMBER := 0;
    vn_cur_date JOBS.UPDATE_DATE%TYPE := SYSDATE;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = SYSDATE
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;

-- OUT, IN OUT 매개변수
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE := 10
 , p_max_sal IN JOBS.MAX_SALARY%TYPE := 100
 , p_upd_date OUT JOBS.UPDATE_DATE%TYPE -- 갱신일자 값을 반환한다! 
)
IS
    vn_cnt NUMBER := 0;
    vn_cur_date JOBS.UPDATE_DATE%TYPE := SYSDATE;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, vn_cur_date, vn_cur_date);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = vn_cur_date
        WHERE job_id = p_job_id;
    END IF;
    
    -- OUT 매개변수에 갱신 일자 할당
    p_upd_date := vn_cur_date;
    COMMIT;
END;

SET SERVEROUTPUT ON
DECLARE 
    vd_cur_date JOBS.UPDATE_DATE%TYPE; 
BEGIN 
    my_new_job_proc('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);
    DBMS_OUTPUT.PUT_LINE(vd_cur_date);
END;

-- IF문

SET SERVEROUTPUT ON
DECLARE
    vn_num1 NUMBER := 1;
    vn_num2 NUMBER := 2;
BEGIN 
    IF vn_num1 >= vn_num2 THEN
        DBMS_OUTPUT.PUT_LINE(vn_num1 || '이 큰 수');
    ELSE
        DBMS_OUTPUT.PUT_LINE(vn_num2 || '이 큰 수');
    END IF;
END;

DECLARE 
    vn_salary NUMBER := 0; -- 변수 초기화 
    vn_department_id NUMBER := 0; -- 변수 초기화 
BEGIN
    vn_department_id := ROUND(DBMS_RANDOM.VALUE (10, 120), -1);
    SELECT salary 
        INTO vn_salary
    FROM employees
    WHERE department_id = vn_department_id
        AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(vn_salary);
    
    IF vn_salary BETWEEN 1 AND 3000 THEN 
        DBMS_OUTPUT.PUT_LINE('낮음');
    ELSIF vn_salary BETWEEN 3001 AND 6000 THEN 
        DBMS_OUTPUT.PUT_LINE('중간');
    ELSIF vn_salary BETWEEN 6001 AND 10000 THEN 
        DBMS_OUTPUT.PUT_LINE('높음');
    ELSE
        DBMS_OUTPUT.PUT_LINE('최상위');
    END IF;
END;

-- 중첩 IF문
DECLARE 
    vn_salary NUMBER := 0;
    vn_department_id NUMBER := 0;
    vn_commission NUMBER := 0;
BEGIN 
    vn_department_id := ROUND(DBMS_RANDOM.VALUE (10,120), -1);
    SELECT salary, commission_pct
        INTO vn_salary, vn_commission
    FROM employees
    WHERE department_id = vn_department_id
        AND ROWNUM = 1;
    DBMS_OUTPUT.PUT_LINE(vn_salary);
    
    IF vn_commission > 0 THEN 
        IF vn_commission > 0.15 THEN 
            DBMS_OUTPUT.PUT_LINE(vn_salary * vn_commission);
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE(vn_salary);
    END IF;
END;

-- CASE (p.277)
DECLARE 
    vn_salary NUMBER := 0; -- 변수 초기화 
    vn_department_id NUMBER := 0; -- 변수 초기화 
BEGIN
    vn_department_id := ROUND(DBMS_RANDOM.VALUE (10, 120), -1);
    SELECT salary 
        INTO vn_salary
    FROM employees
    WHERE department_id = vn_department_id
        AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(vn_salary);
    
    CASE WHEN vn_salary BETWEEN 1 AND 3000 THEN 
            DBMS_OUTPUT.PUT_LINE('낮음');
        WHEN vn_salary BETWEEN 3001 AND 6000 THEN 
            DBMS_OUTPUT.PUT_LINE('낮음');
        WHEN vn_salary BETWEEN 6001 AND 10000 THEN 
            DBMS_OUTPUT.PUT_LINE('높음');
        ELSE
            DBMS_OUTPUT.PUT_LINE('최상위');
    END CASE;
END;

-- LOOP
-- 3단
DECLARE 
    vn_base_num NUMBER := 3;
    vn_cnt      NUMBER := 1;
BEGIN 
    LOOP
        DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || vn_cnt || '= ' 
                             || vn_base_num * vn_cnt);
        vn_cnt := vn_cnt + 1;
        
        EXIT WHEN vn_cnt > 9; -- vn_cnt가 9보다 크면 루프 종료
    END LOOP;
END;

-- WHILE 
DECLARE 
    vn_base_num NUMBER := 3;
    vn_cnt      NUMBER := 1;
BEGIN
-- 조건식이 참일 때만 실행되는 것
-- 조건식이 거짓이 되면 실행 중지
WHILE vn_cnt <= 9 -- vn_cnt가 9보다 크면 루프 종료
    LOOP
        DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || vn_cnt || '= ' 
                             || vn_base_num * vn_cnt);
        vn_cnt := vn_cnt + 1;
    END LOOP;
END;

-- WHILE (EXIT)
-- WHILE 
DECLARE 
    vn_base_num NUMBER := 3;
    vn_cnt      NUMBER := 1;
BEGIN
-- 조건식이 참일 때만 실행되는 것
-- 조건식이 거짓이 되면 실행 중지
WHILE vn_cnt <= 9 -- vn_cnt가 9보다 크면 루프 종료
    LOOP
        DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || vn_cnt || '= ' 
                             || vn_base_num * vn_cnt);
                             
        -- Break (= EXIT)
        EXIT WHEN vn_cnt = 5; -- vn_cnt값이 5가 되면 루프 종료
        vn_cnt := vn_cnt + 1;
    END LOOP;
END;

-- FOR-LOOP 
-- 인덱스값
DECLARE
    vn_base_num NUMBER := 3;
BEGIN 
    FOR i IN 1..9
    LOOP 
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
    END LOOP;
END;

-- CONTINUE
DECLARE
    vn_base_num NUMBER := 3;
BEGIN 
    FOR i IN 1..9
    LOOP
        CONTINUE WHEN i = 5;
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
    END LOOP;
END;

-- GOTO문
-- 사용자 정의 함수와 약간 비슷

DECLARE 
    vn_base_num NUMBER := 3;
BEGIN 
    <<third>>
    FOR i IN 1..9
    LOOP
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
        IF i = 3 THEN
            GOTO fourth;
        END IF;
    END LOOP;
    
    <<fourth>>
    vn_base_num := 4;
    FOR i IN 1..9
    LOOP
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
    END LOOP;
END;

-- NULL문
-- 검색 찾기 링크 참조

-- PL/SQL
-- 블록 단위로 진행
-- 선언부
SET SERVEROUTPUT ON
SET TIMING ON       -- 경과시간 확인
DECLARE
    vi_num NUMBER;  -- 변수 선언
BEGIN
    -- 실행 (코드 실행)
    vi_num := 100;
    DBMS_OUTPUT.PUT_LINE(vi_num);
END; -- 블록 종료
-- / PL/SQL 자체가 종료

-- p.264
DECLARE
    a INTEGER := 2**2*3**2; -- 4 * 9
BEGIN
    DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;

-- DM문
-- PL/SQL 테이블과 연동해서 특정 로직을 처리하는 것

DECLARE
    vs_emp_name VARCHAR2(80); -- 사원명 변수
    vs_dep_name VARCHAR2(80); -- 부원명 변수
BEGIN
    SELECT a.emp_name, b.department_name
        INTO vs_emp_name, vs_dep_name
    FROM employees a
        , departments b
    WHERE a.department_id = b.department_id
        AND a.employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(vs_emp_name || ' - ' || vs_dep_name);
END;

DECLARE
    vs_emp_name employees.emp_name%TYPE; -- 사원명 변수
    vs_dep_name departments.department_name%TYPE; -- 부원명 변수
BEGIN
    SELECT a.emp_name, b.department_name
        INTO vs_emp_name, vs_dep_name
    FROM employees a
        , departments b
    WHERE a.department_id = b.department_id
        AND a.employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(vs_emp_name || ' - ' || vs_dep_name);
END;

-- SQL과 PL/SQL에서 사용할 수 있는 데이터 크기는 다름
-- PL/SQL > SQL보다 더 큰 크기로 사용 가능
DROP TABLE ch08_varchar2;
CREATE TABLE ch08_varchar2(
    VAR1 VARCHAR2(4000)
);

INSERT INTO ch08_varchar2 (VAR1)
VALUES ('tQbADHDjqtRCvosYCLwzbyKKrQCdJubDPTHnzqvjRwGxhQJtrVbXsLNlgeeMCemGMYpvfoHUHDxIPTDjleABGoowxlzCVipeVwsMFRNzZYgHfQUSIeOITaCKJpxAWwydApVUlQiKDgJlFIOGPOKoJsoemqNbOLdZOBcQhDcMLXuYjRQZDIpgpmImgiwzcLkSilCmLrSbmFNsKEEpzCHDylMvkYPKPNeuJxLvJiApNCYzrMcflECbxwNTKSxaEwVvCYnTnFfMFgDqxobWcSmMJrNTQIVOeWlPaMTfRHsrlFSukppmljmOojPSgJiSbQcgtWWOwUNNYFGtgCGBsIcTGAiHWBxtYVXecoJgJCAJptIVmVTZSKliRLoPYTIUpksBuQaqFHLhCkosWChoMjbqgLtBIRBynsKjKiLrdeHVvZanNVElDjLWwlCDhbpsAVQMTzjzhoKIJBdthynMBMVjeNmsKAjdAYhPZKmuKOuMloQdkqPjoKbfjDEeATciMrXiMQorMhYmBlMODBbyLLIkbmtZdPcWGSuxFEUwXnWpvnunEgcLelSneRIpgRNTzTkHqgLbpxoHzCYgSWlIAvKljCnmWiPWGGwlUFOudRSdoqUxntyhNYEiVXtMObywEltTImawnElpmeiWwlTjGTFceqyjhNqiDLxwduubykWzDmFSJNvVvDZibrCpAReqQjlQZcxuVqjKGKvoDuEcQPQeDzmdMYSOTIQdPDNfDffCOUWflHSQhvVTiYumBQIoyznWNITGZkefknJpGEutUnhBgLPQTWTBeTYccqlLrxvRjfJpdpfVDqqfKCngemIEDDHNdvBxCqKDTrrJAumXMKgpWLIHctQuACeNaKnffpYXiioLxZDrxpuZPPUGpRsCtoQuBfogkKuusVATkMyajKTPSyTQbfhZepRjNdrhkymqKvsAcThYbMSMnkKcLWFPAMeGysBVKkQtFMPvRBoDszlSZcMYzwxkKQwJnuVnDxShYiHFlzgDWqhZoqeypyFVBNDtHkiVzHkQisYLbsbVneJyHbHdtaIFLVbfTqbkGQTEjFlPiGUddPUIoLWALrbKcLwBizwhJvaXkvOphcGWpdNAhxgehCvjcQFSFhxrBuANKjyWncWAUpKKJcfQCsQlLfpqdMhjWGkAMMWUaDfCrGtmtkiIZOdNapEnvfFKiHAhBhejgKSuyKXFQXyCaLwwvonHsceJKgjtnYVZvBCYYBSqNCqVqCGewootJJsqrCnmiteMZBbyMPnIrdcielnGUYmwiOPmEqKGvxDmDRTDRumnSRcnvgxLbaiQIuzdslEIMquvvwmvgaumqPkduNyfRtXErCPvDYLelhjNNOjbGryRpTtDHxIJebMEtKryUyZRIdADeTEBExwHMRHzAYFizYiesaMhNIsOUzUTmyEMuFQrsUEtjwhUWIvADNlrcxPZwRazPMMvdVZssmXbXuCkRoPYNGLPwUmrWrrIgQoMSGMPvTcbHnbtleyKYmOMgymANQBZDMoqAOzMHrAVunIiykCudFVNObNgXOoyfQRICbFsWygSZXufipvrWWmRnBWYdoKmIRewOObUjiNDdQsxQIXtlbPSSngfQPfeQKOolVASXIuAmeODKtSOPaEaFKcedGzzsbrPlsPnRRuYFeVdhyufpjFVVrTPczSQkmPYXercLMmVEaDmJXKTqEVNSKeOshDCDJwdINFsLhAuKIIfOdjSEndDwumQLvePVjzNoIfUELOANeshoNgwVhFADjtUIjIhQAIyRnzSoxSRSWklITMgdjQZTthwsnBVLWyfSsAdLzOnEqmMCGBlTYGjtqvKbBoATRwkPkOTSbUhZClVzjiLLIFEMuptuodeRKXUaBfUhVTtasFsZdVnKtEfLldJYsxjlrBADRqhEBEmBKxlXKgEhiKcwAdztcETMUteJwadfaZLEBRjwJOGaIMhsfAxtuBQWyQLGXPDlFQmkcMsKsGUlQBEAubDqbuBYqXLZgmhPftLkYaCYGReLCVXssOxzJFJwnxKJzaaYzfVpbHYBtiBeQZRilJZqrrMTrVtYAcwGxAAddwtlxzdZebfZHjzqRmrrBPNbkVHqjCHtVKUjIDPVSrtyEsPRPoyyPOFOSBcgClTzlAIPmPMkdlpFHctzKGpyQMInMwPKojVErCOrHbCsZoEXqyOcHReSybmxwYabyioVnDxPEvskutVHLWQTNudmKICoaoSGKqONrBmvtGNBKAaJxCRKTDOIqrJOsQVOmGxmuIDEddVYvDwILTyushOAiXbkRIKgNLnFJdOagmiOHKRBKIIkxkOUeZWMRNlqpJdFgKjrGhIzrgBtgjVOtZAskKRbqzRVwLUoUAtRpRkoRQNLIrbLmmjZTugXJBNCscnMguKVAFDKpODtCsmdlBvQGALeBGUitYBxLYhJxeVcAnTWmTAvCITzdzqiBfEudEIBmkDAXIFmoOmsTMZDOnhXYrgMDlDbjednYWWJbGhrXFrxMQmQSmRBwoOqWGbGmjZNlJCvSHvmtZUkIScWXVdfSsdvdyQNpGFIOuteXhCMLmmEHrMucEmFbCIOHTJINAuIUOPfAfijIPkZjppGCCSRJNXWNCmliwUgABkHWuelUWeLsyVKVcZWOSeiQBQibCQJQUgGkTrXZxdBLsgjeMIwOyORDBpywuvlrLScRNhvaCYaKKRvOZeqBebUWWFhNnIRJvedFNfFPgWZJgNRaUpyYWFNiXJfAqNjyCEQYwAdFBQKKolwrufmJOfrToJFEsoNjaphcNvfWGIjKrKZSoSJEsbRqNVcoprpcGrnBgcNAnWUFpRldcPJkPfaoLKRCmVyMAWMXmnScodKisCTqllZEWQQSCFETxLNntgdcFEFRsTSIhuewwrHIlOeCcRqkzgQhKnKyHZHdFsMEKvPywLbjaspVxUMEkVzCGcGoTmaBjUMwJuAYdSTaYGDHHWDrvGgMVTtehpzfgofkmqtamffJbCKOzJgPsHNEnFarjADJGyKLwwitCiBXIraUdZtZwNjUtGbWqxksepVYztIBrimByoYQfUQgOndzFmhnuSmhYWvHliWUHgbvBIkYasDElNsjcCLtMvjQEhJjWvlnAscPwOYfelrfgfRAZGBxdFlMNkfYEWLbkfUhbRPHoDZsaAQdoKhAAWzOcHoAkkHPQMNIxgHNJaqEFBqCuMYEtLpMnIiMCWWEPnBYgYrxlXFGYpQWUNFevwcEUvUzDeSZNrdmahAfjeLSAGjHVnqyTzJkiVXjDJXzOiszXQCErQwwDMMqjLxWebJwNAVdrXeyMDRYXmLMDnuWLVaShVGhlgvbjOdOnhCDTNVazYDnzstqxjOuWbLcDaavRumKUOQXBQwKtdFgOzXiQKWFporrIcylIHlTmTKAIpBqNUbkajLTlwAHieCcqPIJYhegwQhWpYZdfxpQXDKtYzsrmnvdiTKgXfXKlIHPHlxQtqXGhMVPOBAKVZJfkrDNEwnQFwgfoHJSqQxTzRswVLrtFgpVzKcLilgznElWUfhERyeUrCcFCuGJddlFHJrXsqRdUjqUwaBmJVNwjRbCFiVMOSFuNctNVzhmhUpoddsMPUFMvNIMsMjHIWYiLjhSajZqpDkMvUOUCbYKfNHGpdUeWGUtDXHDNSCEXqYrhWhvnISnjfoBMCwwptksarPImRZaRxBMjoBdlmRGlIuQZDzCLnxxioATnGVFFTATUpeypOCaCeJAvPLxEXYzlCgXvXirGSZFyZPPSCdOSHxeELRsetFrWgqPNNpwgbgBEYPOSpLWeVdqOxPaQnidyPVMmELzeJPWgNsWBdPJPjhkdGpeAYZfrBNqdbOwzbtLiWMPafjgWQNcWKqmcleWLcMJoGSAEIUyFuzElZKXonHOMDdGMtSKEFUWdfPfnDecKNhIjAKRYmkXgpPAzlKIOpViZPkZdozzAoWwDnXkfDikvkXcQaoBtzKkcRhNpJRYaGTkdnlfotsJZsLqpYaWoK');

commit;
    
DECLARE
   vs_sql_varchar2   VARCHAR2(4000);
   vs_plsql_varchar2 VARCHAR2(32767);
BEGIN

    -- ch08_varchar2 테이블의 값을 변수에 담는다. 
    SELECT VAR1 
        INTO vs_sql_varchar2
    FROM ch08_varchar2;
     
    -- PL/SQL 변수에 4000 BYTE 이상 크기의 값을 넣는다. 
    vs_plsql_varchar2 := vs_sql_varchar2 || ' - ' || vs_sql_varchar2 || ' - ' || vs_sql_varchar2;
  
    -- 각 변수 크기를 출력한다. 
    DBMS_OUTPUT.PUT_LINE('SQL VARCHAR2 길이 : ' || LENGTHB(vs_sql_varchar2)); 
    DBMS_OUTPUT.PUT_LINE('PL/SQL VARCHAR2 길이 : ' || LENGTHB(vs_plsql_varchar2)); 
END;

-- 두 수의 합
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~ '
accept p_num2 prompt '두번째 숫자를 입력하세요 ~ '

DECLARE
    v_sum number(10);
BEGIN
    v_sum := &p_num1 + &p_num2;
    DBMS_OUTPUT.PUT_LINE('TOtal : ' || v_sum);
END;

DROP table emp;
DROP table dept;

CREATE TABLE DEPT
       (DEPTNO number(10),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               VARCHAR2(10),
 JOB                 VARCHAR2(9),
 MGR                 NUMBER(4) ,
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-11',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);

commit;

drop  table  salgrade;

create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(10) );

insert into salgrade  values(1,700,1200);
insert into salgrade  values(2,1201,1400);
insert into salgrade  values(3,1401,2000);
insert into salgrade  values(4,2001,3000);
insert into salgrade  values(5,3001,9999);

commit;

-- 사원번호를 찾아라
-- 사원번호를 입력하면 해당 사원의 급여가 나오도록 출력하세요!
-- 7782
ACCEPT p_empno prompt '사원 번호를 입력하세요 ~'
DECLARE
    v_sal number(10);
BEGIN
    SELECT SAL INTO v_sal
    FROM emp
    WHERE empno = &p_empno;
    
    DBMS_OUTPUT.PUT_LINE('월급은 '|| v_sal);
END;

-- 조건절 & 반복문
-- 사원 이름을 입력합니다. 받는 급여가 고소득자인지, 중간 소득자, 저 소득자
-- KING, SCOTT
SELECT * FROM emp;

ACCEPT p_ename prompt '사원 이름을 입력하세요 ~ '
DECLARE 
    -- 변수 선언
    v_ename emp.ename%TYPE := upper('&p_ename');
    v_sal emp.sal%TYPE;
BEGIN
    SELECT sal into v_sal
    FROM emp
    WHERE ename = v_ename;
    DBMS_OUTPUT.PUT_LINE('급여 ' || v_sal);
    
    -- 조건식
    IF v_sal >= 3500 THEN 
        DBMS_OUTPUT.PUT_LINE('고 소득자');
    ELSIF v_sal >= 2000 THEN 
        DBMS_OUTPUT.PUT_LINE('중간 소득자');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('저 소득자');
    END IF;
END;

-- 반복문 
-- 구구단! 
2 x 1 = 2 = 2 x 1 
2 x 2 = 4 = 2 x 2
DECLARE 
    v_count number(10) := 0;
BEGIN 
    LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('2 x ' || v_count || ' = ' || 2 * v_count);
        EXIT WHEN v_count = 9;
    END LOOP;
END;

SELECT *
FROM populations
WHERE life_expectancy > (SELECT AVG(life_expectancy)
                         FROM populations
                         WHERE year = 2015 )
       AND year=2015;

SELECT 
    name
    , country_code
    , urbanarea_pop
FROM
    cities
WHERE name IN
    (SELECT capital
    FROM sub_countries)
ORDER BY urbanarea_pop DESC;

SELECT 
    code
    , inflation_rate
    , unemployment_rate
FROM
    economics
WHERE year = 2015 
    AND code NOT IN (SELECT code
                     FROM sub_countries
                     WHERE (gov_form='Republic' OR gov_form LIKE '%Republic%')
                     )
ORDER BY inflation_rate;

SELECT sc.country_name, sc.continent, ec.inflation_rate
FROM sub_countries sc
    INNER JOIN economics ec
    ON sc.code = ec.code
WHERE year = 2015
    AND inflation_rate IN(
        SELECT MAX(inflation_rate) AS max_inf
        FROM(
            SELECT sc.country_name, sc.continent, ec.inflation_rate
            FROM sub_countries sc
            INNER JOIN economics ec
            -- USING(code) 대신 ON 쿼리를 작성
            ON sc.code = ec.code
            WHERE year = 2015)
        GROUP BY continent
    );

SELECT
    ROWNUM as row_n
    , sm.*
FROM summer_medals sm;

SELECT
    year,
    ROW_NUMBER() OVER(ORDER BY YEAR) AS Row_N
FROM(
    SELECT DISTINCT Year
    FROM summer_medals
)Years;

WITH medals AS(
    SELECT
        Athlete
        , COUNT(*) AS Medals
    FROM summer_medals
    GROUP BY Athlete)
SELECT
    medals
    , Athlete
    , ROW_NUMBER() OVER (ORDER BY Medals DESC) AS ROW_N
FROM medals
ORDER BY Medals DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

WITH Weightlifting_Gold AS (
    SELECT
    -- Return each year's champions' countries
        Year
        , Country AS champion
    FROM summer_medals
    WHERE
        Discipline = 'Weightlifting' AND
        Event = '69KG' AND
        GENDER = 'Men' AND
        Medal = 'Gold')
SELECT
    Year
    , Champion
    , LAG(Champion) OVER (ORDER BY Year ASC) AS Last_Champion
FROM Weightlifting_Gold
ORDER BY Year ASC;