<이공계전문기술연수> 6. DB (INDEX , DECLARE)

@INDEX
-> SQL 명령문의 처리 속도를 향상시키기 위해서 칼럼에 대해 생성하는 오라클 객체

 

@INDEX 의 장점
-> 검색속도가 빨라지고, 시스템에 걸리는 부하가 줄어들어 전체 성능 향상

 

@INDEX 의 단점
-> 인덱스를 위한 추가 저장공간이 필요하고, 인덱스 생성시 시간이 걸림
-> 기존데이터에 대해 알고리즘을 이용하여 INDEX를 생성하였는데 데이터가 변화되면
다시 새롭게 알고리즘 적용하여 INDEX를 생성해야함.
(데이터가 변하지 않을때 사용하면 좋음)
-> 데이터변경작업(INSERT ,  UPDATE, DELETE) 이 자주 일어나는 테이블에 INDEX를 생성하면
오히려 성능저하가 발생 할 수 있다.

 

@효율적인 INDEX 사용 예
-> 전체 데이터 중 10 ~ 15% 이내의 데이터를 조회할 때 
-> 두개 이상의 컬럼이 WHERE 절이나 JOIN 조건으로 자주 사용 되는 경우
-> 한번 입력된 데이터의 변경이 자주 일어나지 않을때
-> 한 테이블에 저장된 데이터 용량이 매우 클 경우

 

@SYNONYM(동의어)
->EMPLOYEE -> EMP
->DEPARTMENT -> DEPT

 

@ PL / SQL
-> Procedural Language extention to SQL의 약자
-> 오라클 자체에 내장되어있는 절차적 언어로써, SQL 의 단점을 보완하여 SQL 문장내에서
변수의 정의, 조건처리, 반복처리등을 지원

 

@ PL/SQL 유형
1. 익명 블록 : 이름 없는 블록으로 불리며, 간단한 블록 수행시 사용됨
2. 프로시져 : 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형으로 단독으로 실행되거나

다른 프로시저나 다른 툴 등에 호출되어 실행됨
3. 함수 : 프로시져와 수행되는 결과가 유사하나 값 반환여부로 구분함 -> 함수는 반환값이 있음!!!

 

@ PL/SQL 블록 문법

 

DECLARE (선택)
             [선언부] : 변수나 상수를 선언하는 부분
BEGIN (필수)
             [실행부] : 제어문, 반복문, 함수정의 등 로직
EXCEPTION(선택)
             [예외처리부] : 예외상황 발생시 해결
END;  : 블록 종료
/ : PL/SQL 종료 및 실행

PL/SQL 변수

초기값 입력 가능 :=
DECLARE 
             ENAME VARCHAR2(20);
             TEST NUMBER :=10;
BEGIN
             ENAME :='선동일';

 

@PL/SQL 선택 문

-> 모든 문장들이 기술한 순서대로 순차적으로 수행됨
-> 문장을 선택적으로 수행하려면 IF문을 사용

# IF ~ THEN ~ END IF문
             -> 조건에따라 어떤 명령을 선택적으로 처리 하기 위한 가장 기본구문
             IF 조건 THEN 
                          조건 만족시 처리 구문
             END IF

IF,   IF ~ ELSE,   IF ~ ELSE IF ~ ELSE

IF 조건 1 THEN 수행구문1
ELSEIF 조건2 THEN 수행구문2
ELSEIF 조건3 THEN 수행구문3
ELSE 그외 수행구문
END IF;

 

@@CASE

 -> 자바 SWITCH 

CASE 변수
             WHEN 값1 TEHN 실행문1;
             WHEN 값2 THEN 실행문2;
             ELSE 아무것도 아니면 
END CASE;

 

<<실습>>

(1)

--8월26일
SET SERVEROUTPUT ON;
--프로시져를 사용하여 출력하는 내용을 화면에 보여주는 설정

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
    
END;
/


DECLARE
    ID NUMBER;  -- 변수선언 / 데이터타입 NUMBER
BEGIN
    SELECT EMP_ID
    INTO ID     -- 조회결과를 선언해둔 변수에 넣는 과정
    FROM EMPLOYEE
    WHERE EMP_NAME = '선동일1';
    DBMS_OUTPUT.PUT_LINE(ID); --DB 출력구문 // ID 출력
EXCEPTION
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA!');
        --  에 러 명
END;
/

---

(2)

DECLARE 
    NAME VARCHAR2(20);
    D_CODE VARCHAR2(20);
    J_CODE VARCHAR2(20);
BEGIN
    SELECT EMP_NAME, DEPT_CODE, JOB_CODE
    INTO NAME, D_CODE, J_CODE -- 순서 맞아야됨
    FROM EMPLOYEE
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE(NAME);
    DBMS_OUTPUT.PUT_LINE(D_CODE);
    DBMS_OUTPUT.PUT_LINE(J_CODE);
    
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN DBMS_OUTPUT.PUT_LINE('데이터가 없네요ㅠㅠ');

END;
/
--------------------------------------------------------------------------------
--해당 사원의 사번 입력시
--이름, 부서명, 직급명이 출력되도록

DECLARE 
     NAME VARCHAR2(20);
     D_TITLE VARCHAR2(20);
     J_NAME VARCHAR2(20);
BEGIN 
    SELECT EMP_NAME, DEPT_TITLE, JOB_NAME
    INTO NAME, D_TITLE, J_NAME
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
    JOIN JOB USING (JOB_CODE)
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE(NAME);
    DBMS_OUTPUT.PUT_LINE(D_TITLE);
    DBMS_OUTPUT.PUT_LINE(J_NAME);
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN DBMS_OUTPUT.PUT_LINE('데이터가 없어요ㅠㅠ');
END;
/
SELECT * FROM JOB;
--------------------------------------------------------------------------------

DECLARE
    ENAME VARCHAR2(20);
    TEST1 CONSTANT NUMBER := 200; --상수 바꿀수 없는값
    TEST NUMBER := 10;
    
BEGIN
    ENAME := '선동일';
    
    DBMS_OUTPUT.PUT_LINE(ENAME||'   '||TEST||'  '||TEST1);
END;
/
-------------참조형 변수
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE; -->> 해당 테이블의 같은 타입으로 똑같이 만들어짐
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    
BEGIN
    SELECT EMP_ID, EMP_NAME
    INTO EID,ENAME
    FROM EMPLOYEE
    WHERE EMP_NAME = '선동일';
    DBMS_OUTPUT.PUT_LINE(EID||' '||ENAME);
END;
/

--  %ROWTYPE : %TYPE과 유사하긴 하지만, %TYPE은 1개 컬럼의
--          데이터타입을 참조한다면
--          %ROWTYPE 여러개의 컬럼값을 자동으로 가져옴


DECLARE
    MYROW EMPLOYEE%ROWTYPE; -- 한줄에서 얻을수있는 모든 컬럼의 타입을 다 가져옴
BEGIN
    SELECT EMP_ID, EMP_NAME, EMP_NO, EMAIL
    INTO MYROW.EMP_ID, MYROW.EMP_NAME, MYROW.EMP_NO, MYROW.EMAIL  
    FROM EMPLOYEE
    WHERE EMP_NAME = '선동일';    
    DBMS_OUTPUT.PUT_LINE(MYROW.EMP_ID);
    DBMS_OUTPUT.PUT_LINE(MYROW.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE(MYROW.EMP_NO);
    DBMS_OUTPUT.PUT_LINE(MYROW.EMAIL);
END;
/

-----레코드(RECORD)변수 : %ROWTYPE 참조할 테이블의 컬럼 데이터 타입을
--                       자동으로 모두 가져오는 변수라면
--                      RECORD 직접적으로 컬럼 타입을 지정해서 사용

DECLARE
        --변 수 이 름
    TYPE MY_RECORD_TYPE IS RECORD(
         MY_ID EMPLOYEE.EMP_ID%TYPE,
        MY_NAME EMPLOYEE.EMP_NAME%TYPE,
        MY_NO EMPLOYEE.EMP_NO%TYPE,
        MY_EMAIL EMPLOYEE.EMAIL%TYPE
    );    
    MYRECORD MY_RECORD_TYPE;
    --실제 사용 할 변수 생성!!! 위에는 변수만 생성!!
BEGIN
    SELECT EMP_ID, EMP_NAME, EMP_NO, EMAIL
    INTO MYRECORD
    FROM EMPLOYEE
    WHERE EMP_NAME = '선동일';
    DBMS_OUTPUT.PUT_LINE(MYRECORD.MY_ID);
    DBMS_OUTPUT.PUT_LINE(MYRECORD.MY_NAME);
    DBMS_OUTPUT.PUT_LINE(MYRECORD.MY_NO);
    DBMS_OUTPUT.PUT_LINE(MYRECORD.MY_EMAIL);
END;
/

---

(3)

--@실습문제1
--사번, 사원명, 직급코드, 급여를 담을수 있는 참조변수를 통해서 (%TYPE)
--송종기사원의 사번, 사원명, 직급코드,급여를 익명블럭을 통해 스크립트 출력하세요.
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    JCODE EMPLOYEE.JOB_CODE%TYPE;
    SAL EMPLOYEE.SALARY%TYPE; 
BEGIN
    SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
    INTO EID, ENAME, JCODE, SAL
    FROM EMPLOYEE
    WHERE EMP_NAME = '송종기';
    DBMS_OUTPUT.PUT_LINE(EID);
    DBMS_OUTPUT.PUT_LINE(ENAME);
    DBMS_OUTPUT.PUT_LINE(JCODE);
    DBMS_OUTPUT.PUT_LINE(SAL);
END;
/


--@실습문제2
--사번, 사원명, 부서명,직급명을 담을수 있는 참조변수를 통해서 (record)
-- 사원명을 검색하여 해당 사원의 사번, 사원명, 부서명,직급명을 
-- 익명블럭을 통해 스크립트 출력하세요.

DECLARE
    TYPE RECORD_TYPE IS RECORD(
    EID EMPLOYEE.EMP_ID%TYPE,
    ENAME EMPLOYEE.EMP_NAME%TYPE,
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE,
    JNAME JOB.JOB_NAME%TYPE
);
    RECORDTYPE RECORD_TYPE; 
 BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
    INTO RECORDTYPE
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING (JOB_CODE)
    WHERE EMP_NAME = '&사원명';
    DBMS_OUTPUT.PUT_LINE(RECORDTYPE.EID);
    DBMS_OUTPUT.PUT_LINE(RECORDTYPE.ENAME);
    DBMS_OUTPUT.PUT_LINE(RECORDTYPE.DTITLE);
    DBMS_OUTPUT.PUT_LINE(RECORDTYPE.JNAME);
 END;
 /
 
 --@실습문제3
-- 사원번호를 입력하여 해당 사원을 찾아  (%ROWTYPE을 사용)
-- 사원명, 주민번호, 입사일, 부서명을 
-- 익명블럭을 통해 스크립트 출력하세요.

DECLARE
    RTYPE EMPLOYEE%ROWTYPE;    
    RRTYPE DEPARTMENT%ROWTYPE;
BEGIN
    SELECT EMP_NAME, EMP_NO, HIRE_DATE, DEPT_TITLE
    INTO RTYPE.EMP_NAME, RTYPE.EMP_NO, RTYPE.HIRE_DATE, RRTYPE.DEPT_TITLE
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE(RTYPE.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE(RTYPE.EMP_NO);
    DBMS_OUTPUT.PUT_LINE(RTYPE.HIRE_DATE);
    DBMS_OUTPUT.PUT_LINE(RRTYPE.DEPT_TITLE);
END;
/


--@실습문제4
--사번, 사원명, 직급명을 담을수 있는 참조변수를 통해서 (%type, %rowtype, record)
--송종기사원의 사번, 사원명, 직급명을 익명블럭을 통해 스크립트 출력하세요.

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    JNAME JOB.JOB_NAME%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, JOB_NAME
    INTO EID, ENAME, JNAME
    FROM EMPLOYEE
    JOIN JOB USING (JOB_CODE)
    WHERE EMP_NAME = '송종기';
    DBMS_OUTPUT.PUT_LINE(EID);
    DBMS_OUTPUT.PUT_LINE(ENAME); 
    DBMS_OUTPUT.PUT_LINE(JNAME);
END;
/

SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
SELECT * FROM JOB;

---

(4)

DECLARE
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_NAME, SALARY, NVL(BONUS,0)
    INTO ENAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = '&EMP_ID';
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
   
    
    IF(BONUS = 0)
    THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다');
    ELSE DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100||'%');
    END IF;
END;
/

----예제 사번을 입력 받았을때 사번, 이름, 부서코드, 부서명, 소속값
-- (소속값 (J1 , J2) : 임원진, 그 외에는 일반직원)


SELECT * FROM EMPLOYEE;
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DCODE EMPLOYEE.DEPT_CODE%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    JCODE EMPLOYEE.JOB_CODE%TYPE;    
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE, JOB_CODE
    INTO EID , ENAME, DCODE, DTITLE, JCODE
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    WHERE EMP_ID = '&사번입력';
    DBMS_OUTPUT.PUT_LINE(EID);
    DBMS_OUTPUT.PUT_LINE(ENAME);
    DBMS_OUTPUT.PUT_LINE(DCODE);
    DBMS_OUTPUT.PUT_LINE(DTITLE);
    DBMS_OUTPUT.PUT_LINE(JCODE);
    
    IF(JCODE = 'J1' OR JCODE = 'J2')
    THEN DBMS_OUTPUT.PUT_LINE('임원진 입니다');
    ELSE 
    DBMS_OUTPUT.PUT_LINE('일반직원 입니다');
    END IF;
    
END;
/
--사원 번호를 가지고 해당 사원을 조회
--이때 사원명, 부서명 을 출력하여라.
--만약 부서가 없다면 부서명을 출력하지 않고,
--'부서가 없는 사원입니다'를 출력하고
--부서가 있다면 부서명을 출력하여라.
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    INTO EID, ENAME, DTITLE
    FROM EMPLOYEE
    FULL JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE(ENAME);
    IF(DTITLE IS NULL)
    THEN DBMS_OUTPUT.PUT_LINE('부서가 없는 사원입니다');
    ELSE DBMS_OUTPUT.PUT_LINE(DTITLE);
    END IF; 
END;
/
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;

SET SERVEROUTPUT ON; -- 출력

---

(5)

--## 실습 문제 ##
--사번을 입력 받은 후 급여에 따라 등급을 나누어 출력하도록 하시오 
--그때 출력 값은 사번,이름,급여,급여등급을 출력하시오

--0만원 ~ 99만원 : F
--100만원 ~ 199만원 : E
--200만원 ~ 299만원 : D
--300만원 ~ 399만원 : C
--400만원 ~ 499만원 : B
--500만원 이상(그외) : A

--EX) 200 입력 시
--사번 : 200
--이름 : 선동일
--급여 : 8000000
--등급 : A


--IF
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;

BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO EID, ENAME, SALARY
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
    
    IF(SALARY >= 0 AND SALARY <= 990000)
    THEN DBMS_OUTPUT.PUT_LINE('등급 : F');
    ELSIF (SALARY >= 1000000 AND SALARY <=1990000 )
    THEN DBMS_OUTPUT.PUT_LINE('등급 : E');
    ELSIF (SALARY >= 2000000 AND SALARY <=2990000 )
    THEN DBMS_OUTPUT.PUT_LINE('등급 : D'); 
    ELSIF (SALARY >= 3000000 AND SALARY <=3990000)
    THEN DBMS_OUTPUT.PUT_LINE('등급 : C'); 
    ELSIF (SALARY >= 4000000 AND SALARY <=4990000)
    THEN DBMS_OUTPUT.PUT_LINE('등급 : B'); 
    ELSIF (SALARY >= 5000000 AND SALARY <=5990000)
    THEN DBMS_OUTPUT.PUT_LINE('등급 : A'); 
    END IF;
END;
/

----CASE

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;

BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO EID, ENAME, SALARY
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
    
    CASE FLOOR(SALARY/1000000)
        WHEN 0
            THEN DBMS_OUTPUT.PUT_LINE('등급 : F');
        WHEN 1
            THEN DBMS_OUTPUT.PUT_LINE('등급 : E');
        WHEN 2
            THEN DBMS_OUTPUT.PUT_LINE('등급 : D');
        WHEN 3
            THEN DBMS_OUTPUT.PUT_LINE('등급 : C');
        WHEN 4
            THEN DBMS_OUTPUT.PUT_LINE('등급 : B');
        ELSE DBMS_OUTPUT.PUT_LINE('등급 : A');
        
    END CASE;
END;
/

---

(6)

CREATE TABLE M_TBL(
    USERID VARCHAR2(20) PRIMARY KEY,
    USERPWD VARCHAR2(20) NOT NULL,
    USERNAME VARCHAR2(20) NOT NULL,
    ENROLL_DATE DATE
);
INSERT INTO M_TBL VALUES('jian' , 1234, '임지안', sysdate); 
INSERT INTO M_TBL VALUES('sangchul' , 4567, '박상철', sysdate); 
INSERT INTO M_TBL VALUES('sunghyen' , 7894, '유성현', sysdate); 
INSERT INTO M_TBL VALUES('jian2' , 1234, '임지안2', sysdate); 


CREATE TABLE D_TBL(
    USERID VARCHAR2(20) PRIMARY KEY,
    USERNAME VARCHAR2(20),
    ENROLL_DATE DATE,
    OUT_DATE DATE
);

CREATE OR REPLACE TRIGGER M_TBL_TRG
AFTER DELETE
ON M_TBL
FOR EACH ROW
BEGIN 
    INSERT INTO D_TBL VALUES(
        :OLD.USERID,:OLD.USERNAME,:OLD.ENROLL_DATE,SYSDATE
        );
END;
/

DELETE FROM M_TBL WHERE USERID='sangchul';
SELECT * FROM M_TBL;
SELECT * FROM D_TBL;

CREATE OR REPLACE TRIGGER M_TBL_INSET_TRG
AFTER INSERT
ON M_TBL
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:NEW.USERNAME||'님이 신규가입 하셨습니다.');
END;
/
INSERT INTO M_TBL VALUES('jian4' ,1234, '임지안3', sysdate); 
COMMIT;

---

(7)

--LOG_TBL 테이블
--USERID 사용자 아이디
--CONTENT 이름변경내용
--MODIFY_DATE 변경시간
--EX) ID 가 USER01이라는 사람의 이름을 변경했다면
--USER01 -> 사용자1
--USER01 -> 사용자2

--LOG_TBL
--
--USERID    CONTENT         MODIFY_DATE
--USER01    사용자1->사용자2  오늘

CREATE TABLE LOG_TBL(
    USERID VARCHAR2(20),
    CONTENT VARCHAR2(100),
    MODIFY_DATE DATE
);

CREATE OR REPLACE TRIGGER M_TBL_MODIFY_TRG
AFTER UPDATE
ON M_TBL
FOR EACH ROW
BEGIN 
    INSERT INTO LOG_TBL VALUES(
        :OLD.USERID,:OLD.USERNAME||'->'||:NEW.USERNAME,SYSDATE
        );
END;
/

UPDATE M_TBL SET USERNAME='임지안' WHERE USERID='jian';
SELECT * FROM LOG_TBL;


SELECT * FROM M_TBL; 

---

(8)

--제품이 입/출고 될때마다 상품재고 테이블의 수치를 관리자가 수동으로 관리하면 불편함
--이때 트리거를 이용해서 재고관리를 해보자
--아래 테이블 생성 후 트리거 제작

--##제품 테이블
--PRODUCT
--PCODE	제품코드	PRIMARY KEY -> 시퀀스사용
--PNAME	제품이름
--BRAND	제조회사
--PRICE	가격
--STOCK	재고량		기본값 0

--#입출고 테이블
--PRO_DETAIL
--DCODE	입출고관리번호	PRIMARY KEY -> 시퀀스사용
--PCODE	제품코드	PRODUCT 외래키적용
--PDATE	입출고날짜
--AMOUNT	입고/출고 수량
--STATUS	입고/출고구분	'입고'OR'출고' 만 가능


--입출고 테이블에 데이터가입력되면(입고/출고가 발생하면) 제품테이블의 STOCK의 갯수가 자동으로 적용되어야 함
--입고 시 : STOCK 증가
--출고 시 : STOCK 감소
--HINT) TRIGGER의 BEGIN문에도 IF 사용이 가능함

CREATE TABLE PRODUCT(
    PCODE NUMBER PRIMARY KEY,
    PNAME VARCHAR2(20), 
    BRAND VARCHAR2(20),
    PRICE VARCHAR2(20),
    STOCK NUMBER DEFAULT 0    
);
CREATE SEQUENCE PRODUCT_PCODE
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
NOCYCLE
NOCACHE;

INSERT INTO PRODUCT VALUES(PRODUCT_PCODE.NEXTVAL , '딸기우유' , '빙그레', 1000, DEFAULT);
INSERT INTO PRODUCT VALUES(PRODUCT_PCODE.NEXTVAL , '초코우유' , '서울', 1000, DEFAULT);
INSERT INTO PRODUCT VALUES(PRODUCT_PCODE.NEXTVAL , '바나나우유' , '롯데', 2000, DEFAULT);
INSERT INTO PRODUCT VALUES(PRODUCT_PCODE.NEXTVAL , '메론우유' , '매일', 3000, DEFAULT);
INSERT INTO PRODUCT VALUES(PRODUCT_PCODE.NEXTVAL , '복숭아우유' , '매일', 2000, DEFAULT);
INSERT INTO PRODUCT VALUES(PRODUCT_PCODE.NEXTVAL , '블루베리우유' , '서울', 2000, DEFAULT);


SELECT * FROM PRODUCT;

CREATE TABLE PRO_DETAIL(
    
    DCODE NUMBER PRIMARY KEY,
    PCODE NUMBER,
    PDATE DATE,
    AMOUNT NUMBER,
    STATUS VARCHAR(10) check (STATUS in('입고','출고')),
    FOREIGN KEY (PCODE) references PRODUCT(PCODE) 
);

CREATE SEQUENCE PRO_DETAIL_DCODE
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
NOCYCLE
NOCACHE;


CREATE OR REPLACE TRIGGER PRO_DETAIL_MODIFY_TRG
AFTER INSERT
ON PRO_DETAIL
FOR EACH ROW
BEGIN 
    IF(:NEW.STATUS = '입고')
    THEN UPDATE PRODUCT SET STOCK = STOCK + :NEW.AMOUNT WHERE PCODE = :NEW.PCODE;
    ELSE
    UPDATE PRODUCT SET STOCK = STOCK - :NEW.AMOUNT WHERE PCODE = :NEW.PCODE;
    END IF;
END;
/

SELECT * FROM PRODUCT;

SELECT * FROM PRO_DETAIL;

INSERT INTO PRO_DETAIL VALUES(PRO_DETAIL_DCODE.NEXTVAL, 1 , SYSDATE , 5 , '입고');
INSERT INTO PRO_DETAIL VALUES(PRO_DETAIL_DCODE.NEXTVAL, 1 , SYSDATE , 4 , '출고');