ORACLE - 2019.11.29

SET SERVEROUTPUT ON;

DROP PROCEDURE  PYB_PRO;


CREATE OR REPLACE PROCEDURE PYB_PRO
IS    

    VEMP_SAL VARCHAR2(2);
    
    VEMP_ENAME pyb_emp.ename%TYPE;
    VJOB_NAME pyb_job.name%TYPE;
    VDEPT_NAME pyb_dept.name%TYPE;
    
    CURSOR C1 IS
        SELECT A.ENAME, SAL_RANK(A.SAL) , B.NAME, C.NAME 
        FROM PYB_EMP A
        INNER JOIN PYB_JOB B
        ON A.JOB_CODE = B.JOB_ID
        INNER JOIN PYB_DEPT C
        ON A.DEPTNO = C.deptno;
    
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 
        INTO VEMP_ENAME, VEMP_SAL, VJOB_NAME, VDEPT_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VDEPT_NAME||' 소속 '||VEMP_ENAME||' 의 역할은 '||VJOB_NAME||' 이다.');
        DBMS_OUTPUT.PUT_LINE('연봉등급은 '||VEMP_SAL||' 이다.');
    END LOOP;
END;
/

EXEC PYB_PRO;


--4.1 FUNCTION CREATE
DROP FUNCTION SAL_RANK;

CREATE FUNCTION SAL_RANK(
    VSAL pyb_emp.sal%TYPE
)
RETURN VARCHAR2
IS 
    SAL_RANK VARCHAR2(2);
BEGIN
SELECT
    CASE
        WHEN VSAL <= 2000 THEN 'A'
        WHEN VSAL <= 4000 THEN 'B'
        WHEN VSAL <= 6000 THEN 'C'
        WHEN VSAL >= 6001 THEN 'D'
        ELSE 'F'
        END AS SAL_R INTO SAL_RANK
      FROM DUAL;
      RETURN SAL_RANK;
END;
/




--TRIGGER CRETE
DROP TRIGGER SAL_TRIGGER;

CREATE TRIGGER SAL_TRIGGER
AFTER UPDATE  OF SAL ON PYB_EMP
FOR EACH ROW
DECLARE
BEGIN
    IF UPDATING THEN
            CASE
                WHEN :NEW.SAL <= 2000 THEN
                INSERT INTO pyb_sal_log VALUES (:OLD.SAL, :NEW.SAL + (:NEW.SAL * 0.2));
                WHEN :NEW.SAL <= 4000 THEN
                INSERT INTO pyb_sal_log VALUES (:OLD.SAL, :NEW.SAL + (:NEW.SAL * 0.1));
                WHEN :NEW.SAL <= 6000 THEN
                INSERT INTO pyb_sal_log VALUES (:OLD.SAL, :NEW.SAL + (:NEW.SAL * 0.07));
                WHEN :NEW.SAL >= 6001 THEN
                INSERT INTO pyb_sal_log VALUES (:OLD.SAL, :NEW.SAL + (:NEW.SAL * 0.05));
            END CASE;
    END IF;
END;
/


--3-2. TRIGGER TEST
UPDATE PYB_EMP SET SAL = 4000 WHERE EMPNO = 1377;

COMMIT;

SELECT * FROM PYB_EMP;