ORACLE - 19.11.22

--0. scott의 tabs(all table)조회
--1. emp table 생성
--2. emp table copy
--3. sal : 0~1000 -> 10%
--       : 1001~2000 -> 7%
--       : 2001~ -> 5%
--       단, 5000인 SAL -> 20%
--4. 부서별 급여(SAL) 평균 출력 -> FUNCTION 사용
-- MAX(SAL) - MIN((SAL) = TOTAL


--0. scott의 tabs(all table)조회
SELECT *
FROM TABS
;

--1. EMP_COPY TABLE 생성
--DROP TABLE EMP_COPY;

CREATE TABLE EMP_COPY AS
SELECT * FROM EMP
WHERE 1=1;

--1-1. EMP_BONUS TABLE 생성
CREATE TABLE EMP_BONUS AS
SELECT * FROM EMP_COPY
WHERE 1=1;

--1-2. BONUS 값 받을 칼럼생성
ALTER TABLE EMP_BONUS ADD
BONUS NUMBER(7,2);

--3. sal : 0~1000 -> 10%
--       : 1001~2000 -> 7%
--       : 2001~ -> 5%
--       단, 5000인 SAL -> 20%

-- 3-1. SAL_TRIGGER 생성
DROP TRIGGER SAL_TRIGGER;

CREATE TRIGGER SAL_TRIGGER
AFTER INSERT OR DELETE OR UPDATE OF SAL ON EMP_COPY
FOR EACH ROW
DECLARE
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('INSERTING');
        IF :NEW.MGR IS NOT NULL THEN
            CASE
                WHEN :NEW.SAL <= 1000 THEN
                  INSERT INTO EMP_BONUS(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, BONUS) 
                  VALUES(:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO, :NEW.SAL * 1.1);
                WHEN :NEW.SAL <= 2000 THEN
                  INSERT INTO EMP_BONUS(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, BONUS) 
                  VALUES(:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO, :NEW.SAL * 1.7);
                ELSE
                  INSERT INTO EMP_BONUS(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, BONUS) 
                  VALUES(:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO, :NEW.SAL + (:NEW.SAL * 0.2));
            END CASE;
            
         ELSIF :NEW.MGR IS NULL THEN
              INSERT INTO EMP_BONUS(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, BONUS) 
              VALUES(:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO, :NEW.SAL + (:NEW.SAL * 0.2));
        END IF;
    END IF;
    
    IF UPDATING AND :OLD.SAL != :NEW.SAL OR DELETING THEN
        DBMS_OUTPUT.PUT_LINE('UPDATING OR DELETING');
            CASE
                WHEN :NEW.SAL <= 1000 THEN
                    UPDATE EMP_BONUS SET SAL = :NEW.SAL, BONUS = :NEW.SAL * 1.1 WHERE EMPNO = :NEW.EMPNO;
                WHEN :NEW.SAL <= 2000 THEN
                    UPDATE EMP_BONUS SET SAL = :NEW.SAL, BONUS = :NEW.SAL * 1.7 WHERE EMPNO = :NEW.EMPNO;
                WHEN :NEW.SAL >= 5000 THEN
                    UPDATE EMP_BONUS SET SAL = :NEW.SAL, BONUS = :NEW.SAL + (:NEW.SAL * 0.2) WHERE EMPNO = :NEW.EMPNO;
            END CASE;
    END IF;
END;
/


--3-2. TRIGGER TEST
SET SERVEROUTPUT ON;
INSERT INTO emp_copy VALUES(9000, 'TESTER', 'TEST', 7902, '80/12/17', 700, 300, 20);
UPDATE emp_copy SET SAL = 700 WHERE EMPNO = 7369;

COMMIT;

SELECT * FROM EMP_COPY WHERE EMPNO = 8000;
SELECT * FROM EMP_BONUS WHERE EMPNO = 9000;


--4. 부서별 급여(SAL) 평균 출력 -> FUNCTION 사용
-- MAX(SAL) - MIN((SAL) = RESULT

--4.1 FUNCTION CREATE
--DROP FUNCTION SAL_AVERAGE;

CREATE FUNCTION SAL_AVERAGE
RETURN NUMBER
IS 
    AVERAGE EMP_COPY.SAL%TYPE;
BEGIN
    SELECT MAX(SAL) - MIN(SAL) INTO AVERAGE FROM EMP_COPY;
    RETURN AVERAGE;
END;
/

--4-2. TEST
SELECT SAL_AVERAGE FROM DUAL;