Database 기본10_TRIGGER, PROCEDURE, FUNCTION

--------------------------- 2021_03_16
 
-- TABLE 타입 선언
DECLARE
    TYPE EMP_ID_TABLE_TYPE IS TABLE OF EMPLOYEE.EMP_ID% TYPE
    INDEX BY BINARY_INTEGER;
 
    V_EMPID EMP_ID_TABLE_TYPE;
    I BINARY_INTEGER:=0;
BEGIN
    FOR K IN (SELECT EMP_ID FROM EMPLOYEE) LOOP
        I:=I+1;
        V_EMPID(I):=K.EMP_ID;
    END LOOP;
 
    FOR K IN 1..I LOOP
        DBMS_OUTPUT.PUT_LINE(V_EMPID(K));
    END LOOP;
END;
/
SET SERVEROUTPUT ON;
 
-- PL/SQL 구문에서 SELECT문 처리
-- ㄴ 반드시 INTO 사용해야 한다.
DECLARE
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE;
BEGIN
    SELECT EMP_ID
    INTO V_EMP_ID
        FROM EMPLOYEE
        WHERE EMP_NAME='&이름';
END;
/
 
SELECT * FROM INSERTEST;
CREATE TABLE INSERTEST(
 VAL VARCHAR2(500)
);
-- PL/SQL 에서 INSERT문 처리
BEGIN
    INSERT INTO INSERTEST VALUES ('PL/SQL문으로 입력하기');
    COMMIT;
end;
/
--반복문
BEGIN
    FOR K IN 1..10 LOOP
        INSERT INTO INSERTEST VALUES ('PL/SQL문으로 입력하기');
        COMMIT;
    END LOOP;
end;
/
 
--1. 사원 번호를 입력받아 받은 사원의 사원번호, 이름, 부서코드, 부서명을 출력
 
--2. 사원번호를 입력받아서 해당 사원의 정보(한 행)를 한 변수에 모두 입력받아
--  사번, 이름, 주민번호, 급여를 출력하여라
 
--3. EMPLOYEE 테이블에서 사번 마지막 번호를 구한 뒤, +1한 사번에 사용자로 부터 입력받은
-- 이름, 주민번호, 전화번호, 직급코드(J5), 급여등급(S5)를 등록하는 PL/SQL을 작성하자.
DECLARE
    ID EMPLOYEE.EMP_ID%TYPE;
BEGIN
    SELECT MAX(EMP_ID)
        INTO ID
    FROM EMPLOYEE WHERE EMP_ID LIKE'2%'; -- 200번대
    INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, PHONE, JOB_CODE, SAL_LEVEL)
        VALUES(ID+1,'&이름','&주민번호','&전화번호','&직책코드','&급여코드');
    DBMS_OUTPUT.PUT_LINE(ID);
end;
/
 
-- 사번을 입력받아 이름을 조회하고 그 이름이 선동일이면 ' 난 대표다 ! ' 출력
DECLARE
    V_EMPNAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
    SELECT EMP_NAME
    INTO V_EMPNAME
    FROM EMPLOYEE
    WHERE EMP_ID='&사원아이디'
    
    IF V_EMPNAME='선동일' 
        THEN DBMS_OUTPUT.PUT_LINE('난 대표다!');
END;
/
-- IF THEN ELSE END IF;
-- 조회된 사원이 선동일이면 난 대표다! 아니면 난 사원이다!
DECLARE
    V_EMPNAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
    SELECT EMP_NAME
    INTO V_EMPNAME
    FROM EMPLOYEE
    WHERE EMP_ID='&사원아이디';
    IF V_EMPNAME='선동일' 
        THEN DBMS_OUTPUT.PUT_LINE('난 대표다!');
    ELSE DBMS_OUTPUT.PUT_LINE('난 사원이다~');
    END IF;
END;
/
 
-- IF THEN~ ELSIF THEN ~ ELSE ~ END IF;
-- 직책코드가 J1이면 대표다! J2 면 임원이다. 그외면 사원이다 출력
SET SERVEROUTPUT ON;
 
DECLARE
    V_JOBCODE EMPLOYEE.JOB_CODE%TYPE;
BEGIN
    SELECT JOB_CODE
    INTO V_JOBCODE
    FROM EMPLOYEE
    WHERE JOB_CODE='&직책코드';
    
    IF V_JOBCODE = 'J1' THEN
        DBMS_OUTPUT.PUT_LINE('난 대표다!');
        ELSIF V_JOBCODE = 'J2' THEN
            DBMS_OUTPUT.PUT_LINE('난 임원이다');
        ELSE DBMS_OUTPUT.PUT_LINE('사원이다');
    END IF;
END;
/
 
DELETE FROM EMPLOYEE
WHERE EMP_NAME='DAS';
 
SELECT * FROM EMPLOYEE;
 
-- CASE문으로 월급 400이상이면 고액, 200이상 평타, 그 이하면 ㅠㅠ 출력
DECLARE
    V_SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT SALARY
    INTO V_SALARY
    FROM EMPLOYEE
    WHERE EMP_ID='&사번';
    
    CASE
        WHEN V_SALARY>=4000000 THEN DBMS_OUTPUT.PUT_LINE('고액');
        WHEN V_SALARY>=2000000 THEN DBMS_OUTPUT.PUT_LINE('평타');
        ELSE DBMS_OUTPUT.PUT_LINE('ㅠㅠ');
    END CASE;
END;
/
 
-- LOOP
-- LOOP~ 실행할구문 END LOOP -> 무한루프
-- 실행할 구문에서 빠져나오는 예약어 : EXIT
 
-- 1~10 출력하기
DECLARE
    I NUMBER :=1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I+1;
        IF I>10 THEN EXIT;
        END IF;
    END LOOP;
END;
/
 
-- FOR문
-- FOR 변수 IN 범위||SELECT문 LOOP
-- 실행구문
-- 범위시작숫자..끝숫자
-- END LOOP;
 
BEGIN
    FOR I IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/
 
-- ㄴ 거꾸로
BEGIN
    FOR I IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/
 
-- EMPLOYEE에 있는 10개의 ROW출력
-- EMP_ID 200~209
DECLARE
    E EMPLOYEE%ROWTYPE;
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE;
BEGIN
    V_EMP_ID:=200;
    FOR I IN 1..10 LOOP
        SELECT*
        INTO E
        FROM EMPLOYEE
        WHERE EMP_ID=V_EMP_ID;
        V_EMP_ID:=V_EMP_ID+1;
        DBMS_OUTPUT.PUT_LINE(E.EMP_ID||E.EMP_NAME);
    END LOOP;
END;
/
 
-- %TYPE 
--형식=> 변수명  테이블명.칼럼명%TYPE ;    
--(기존 테이블의 칼럼에 선언된 데이터타입과 크기를 참조하여 변수를 선언
--테이블 칼럼의 제약조건은 적용되지 않음.)
 
-- %ROWTYPE
-- 변수명   테이블명%ROWTYPE ;
-- 기존 테이블의 각 칼럼에 정의된 데이터타입과 크기를 참조
-- 칼럼 수와 동일한 복수 개의 변수가 선언,
-- 각 기억 장소의 구분은 “변수명.칼럼명”으로 구분
 
-- 프로시저 : 특정 처리 작업을 수행하는데 사용하는 저장 서브프로그램
 
-- 프로시저 생성
--CREATE [OR REPLACE] PROCEDURE 프로시저명
--IS | AS
--선언부
--BEGIN
--실행부
--END;
 
-- 프로시저 실행 
-- EXECUTE 프로시저명; (SET SERVEROUTPUT ON; 필요)
 
-- 등록된 프로시저 조회
SELECT * FROM USER_PROCEDURES;
 
-- 매개변수 있는 프로시저 생성
-- IN : 프로시저 실행 시 필요한 값을 받는 변수 (일반 매개변수 개념)
-- OUT :프로시저가 실행한 결과를 되돌려주는 변수
 
-- CREATE OR REPLACE PROCEDURE PRO_SELECT_EMP(V_EMP IN EMPLOYEE.EMP_ID%TYPE,
 
-- FUNCTION : 프로시저와 유사. RETURN 값이 있다.
CREATE OR REPLACE FUNCTION MYFUNC(STR VARCHAR2)
RETURN VARCHAR2
IS
    RESULT VARCHAR2(10000);
BEGIN
    RESULT := '앞'||STR||'뒤';
    RETURN RESULT;
END;
/
 
SELECT MYFUNC ('GOM') FROM DUAL;
 
-- EMP_ID를 전달받아 연봉을 계산해서 출력해주는 함수
CREATE OR REPLACE FUNCTION CALC(V_EMP_ID EMPLOYEE.EMP_ID%TYPE)
RETURN NUMBER
IS
    E EMPLOYEE%ROWTYPE;
    RESULT NUMBER;
BEGIN
    SELECT *
    INTO E
    FROM EMPLOYEE
    WHERE EMP_ID=V_EMP_ID;
    RESULT := (E.SALARY+E.SALARY*NVL(E.BONUS,0))*12;
    RETURN RESULT;
END;
/
 
SELECT CALC(201) FROM DUAL;
 
SELECT EMP_ID, EMP_NAME, SALARY, CALC(EMP_ID)
FROM EMPLOYEE;
 
--트리거 이용하기
-- 특정테이블에  DML구문이 실행될 때(전,후) 특정한 로직이 수행되도록하는 것
-- CREATE OR REPLCAE TRIGGER 트리거명칭
-- 실행시점(AFTER/BEFORE) DML구문(INSERT,UPDATE,DELETE)
-- FOR EACH ROW 
-- BEGIN ~~~ PL/SQL구문 작성
 
CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT
ON EMPLOYEE
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('신입사원이 입사하였습니다');
END;
/
 
INSERT INTO EMPLOYEE VALUES (908, '길성춘', '690512-1151432','gil_sj@kh.or.kr', 
                    '01035464455', 'D5', 'J3', 'S5', 3000000, 0.1, 200, SYSDATE, NULL,
                   DEFAULT);
 
-- 데이터가 조작된 전/후
-- 이전, 이후자료
--               :NEW.컬럼명(새로운값) / :OLD.컬럼명(이전값)
-- UPDATE               O                                O
-- INSERT               O                                X
-- DELETE               X                                O
 
CREATE OR REPLACE TRIGGER TRG_02
AFTER UPDATE
ON EMPLOYEE
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:OLD.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE(:OLD.SALARY);
    DBMS_OUTPUT.PUT_LINE(:NEW.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE(:NEW.SALARY);
END;
/
    
UPDATE EMPLOYEE SET EMP_NAME='김가현', SALARY=500 WHERE EMP_ID='888';
 
 
--재고관리 테이블에 트리거 적용하기
CREATE TABLE PRODUCT(
    PCODE NUMBER PRIMARY KEY,
    PNAME VARCHAR2(30),
    BRAND VARCHAR2(30),
    PRICE NUMBER,
    STOCK NUMBER DEFAULT 0
);
--상품의 입출고를 관리하는 테이블
CREATE TABLE PRODUCT_IO(
    IOCODE NUMBER PRIMARY KEY,
    PCODE NUMBER CONSTRAINT FK_PRO_NAME REFERENCES PRODUCT(PCODE),
    PDATE DATE,
    AMOUNT NUMBER,
    STATUS VARCHAR2(10) CHECK(STATUS IN('입고','출고'))
);
CREATE SEQUENCE SEQ_PRO;
CREATE SEQUENCE SEQ_IO;
 
INSERT INTO PRODUCT VALUES(SEQ_PRO.NEXTVAL,'보호필름','아르마니',500000,DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PRO.NEXTVAL,'갤럭시폴드','삼성',2300000,DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PRO.NEXTVAL,'카누','동서식품',120000,DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PRO.NEXTVAL,'콜라뽑기','불량식품',1000,DEFAULT);
 
SELECT * FROM PRODUCT;
 
--입출고테이블에 입고, 출고가 됐을때 TRIGGER를 이용해서 PRODUCT테이블을 수정해보자
CREATE OR REPLACE TRIGGER TRG_IO
AFTER INSERT
ON PRODUCT_IO
FOR EACH ROW
BEGIN 
    IF :NEW.STATUS ='입고' THEN
        UPDATE PRODUCT SET STOCK=STOCK+:NEW.AMOUNT WHERE PCODE=:NEW.PCODE;
     ELSIF :NEW.STATUS='출고' THEN
        UPDATE PRODUCT SET STOCK=STOCK-:NEW.AMOUNT WHERE PCODE=:NEW.PCODE;
    END IF;
END;
/
INSERT INTO PRODUCT_IO VALUES(SEQ_IO.NEXTVAL, 1,SYSDATE,100, '입고');
INSERT INTO PRODUCT_IO VALUES(SEQ_IO.NEXTVAL, 2,SYSDATE,5, '입고');
INSERT INTO PRODUCT_IO VALUES(SEQ_IO.NEXTVAL, 1,SYSDATE,10, '출고');
SELECT * FROM PRODUCT;
SELECT * FROM PRODUCT_IO;