24일차 : PL/SQL / DB모델링

20221229

 

[ 1. PL / SQL ] 

어제 했던 PL/SQL에 이어서 1. 익명블록 / 2. 프로시져 / 3. 함수 중 함수에 대한 코드 

 

1 - 1 ) FUCTION  프로시져와 거의 유사한용도로 사용 / 프로시져와의 차이점은 반드시 반환값이 존재    

< 사용법 > 

CREATE FUNCTION 함수이름(매개변수)
RETURN 되돌려줄 자료형;
IS
      변수선언
BEGIN
      실행코드
RETURN 데이터;
END;
/

-- 사번을 매개변수로 전달하면 보너스를 포함한 연봉을 계산해서 리턴하는 함수
-- 매개변수에 참조형가능, 직접 선언을 해야할 때는 (E_ID NUMBER)
-- 단, 매개변수에 자료형선언 시 문자타입의 경우 길이를 명시X (E_ID VATECHAR2)
CREATE OR REPLACE FUNCTION YEAR_SAL(E_ID EMPLOYEE.EMP_ID%TYPE) 
RETURN NUMBER
IS
    SAL         EMPLOYEE.SALARY%TYPE; -- 조회된 급여 저장
    BO          EMPLOYEE.BONUS%TYPE;  -- 조회된 보너스 저장
    CALC_SAL    NUMBER;               -- 연봉계산 후 저장용 변수
BEGIN 
    SELECT SALARY, NVL(BONUS, 0)
    INTO SAL, BO
    FROM EMPLOYEE
    WHERE EMP_ID = E_ID;
    CALC_SAL := (SAL + (SAL*BO))*12;
    RETURN CALC_SAL;
END;
/

-- 생성한 함수 사용하기
SELECT YEAR_SAL(201) FROM DUAL;
SELECT EMP_ID, EMP_NAME, SALARY, BONUS, YEAR_SAL(EMP_ID) FROM EMPLOYEE;

-- 함수 삭제하기
DROP FUNCTION YEAR_SAL;

 함수, 프로시져는 중간중간 수정할 일이 많이 생김(로직이 바뀌는경우가 있기때문에)
 생성 시 CREATE OR REPLACE FUNCTION 으로 생성
- CREATE : 생성 
- REPLACE : 같은 함수가 이미 있을 경우 그 함수의 내용을수정(프로시져, 뷰 도가능)
                    (테이블, 시퀀스 같은애들은 안된다)

 

1 - 2 ) TRIGGER

    : 테이블이나 뷰 INSERT, UPDATE, DELETE에 의한 데이터가 입력, 수정, 삭제
      즉, 테이블 OR 뷰에 변화가 생길 경우 자동으로 실행되는기능

    < 트리거를 만드는 기준 >
    트리거 실행시점
        INSERT / UPDATE / DELETE 발생 전/후 트리거 동작 시점 지정
    트리거 이벤트
        INSERT / UPDATE / DELETE 중 어떤 DML이 동작했을 때 발생시킬지
    유형
        FOR EACH ROW : 적용된 행단위로 트리거동작
        FOR EACH ROW키워드가 없으면 DML한번에 여러행이 변경되어도 트리거는 한번
    
    BIND 변수
    :NEW - DML수행기준으로 새로들어온데이터(범위:해당테이블)
    :OLD - EML수행기준으로 이전에 있던 데이터(범위:해당테이블)

-- MEMBER_TBL에서 DELETE를 통해 멤버삭제를하면 
-- 삭제내역을 DEL_MEMBER_TBL 테이블에 INSERT하는 트리거
CREATE OR REPLACE TRIGGER DEL_MEMBER_TRG
AFTER DELETE
ON MEMBER_TBL -- 동작 시점 : MEMBER_TBL에 DELETE라는 DML이 동작한 이후에 실행
-- 변수를 선언한 필요가 없으므로 DECLARE X
FOR EACH ROW -- DELETE를 한번만 실행하더라도 반영된 행의 갯수만큼 실행
BEGIN
    INSERT INTO DEL_MEMBER_TBL
 -- VALUES(DEL_MEMBER_SEQ.NEXTVAL, 조회된회원번호, 조회된이름, 조회된가입일, SYSDATE)
 -- DELETE 기준으로 데이터는 기존에 있던 데이터이므로, :OLD.컬럼명 으로 불러올 수있다.
    VALUES(DEL_MEMBER_SEQ.NEXTVAL, :OLD.MEMBER_NO,:OLD.MEMBER_ID, :OLD.MEMBER_NAME,
                   :OLD.JOIN_DATE, SYSDATE);
END;
/

-- 삭제 후 프로시져를 사용하지 않아도 TRIGGER가 자동으로 실행되므로 
-- DEL_MEMBER_TBL에 INSERT가 되어있음.
-- 단, MEMBER_NO 가아니라 다른컬럼을 조건으로 둿을 때도 실행됨
-- DELETE가 일어나기만 하면 TRIGGER 실행 / 한번에 여러개를 지워도 가능!
DELETE FROM MEMBER_TBL WHERE MEMBER_NO < 10;
SELECT * FROM MEMBER_TBL;
SELECT * FROM DEL_MEMBER_TBL;

 

비밀번호 변경 시 로그를 넣을 테이블

CREATE TABLE PW_CHANGE_LOG(
    MEMBER_ID   VARCHAR2(30),
    CHANGE_LOG  VARCHAR2(100),
    CHANGE_DATE DATE
);

 

UPDATE 시 위 테이블에 자동으로 INSERT해주는 TRIGGER

-- MEMBER_TBL에서 UPDATE를 통해서 비밀번호를 변경하면, 
-- 변경내역을 PW_CHANGE_LOG 테이블에 INSERT하는 트리거
CREATE OR REPLACE TRIGGER PW_CHANGE_TRG
AFTER UPDATE
ON MEMBER_TBL
FOR EACH ROW
BEGIN
    INSERT INTO PW_CHANGE_LOG VALUES
    (:OLD.MEMBER_ID, :OLD.MEMBER_PW||' -> '||:NEW.MEMBER_PW, SYSDATE);
    -- UPDATE를 기준으로 OLD는 이전비밀번호 NEW는 새비밀번호
END;
/

SELECT * FROM MEMBER_TBL;
UPDATE MEMBER_TBL SET MEMBER_PW = '1111' WHERE MEMBER_NO = 12;
SELECT * FROM PW_CHANGE_LOG;

-- BIND변수 기준으로 봤을 때
-- INSERT는 새로운게 들어오는거니까 :NEW만 사용
-- UPDATE는 기존값과 새로운값이 모두 있으므로 :OLD / :NEW를 사용가능
-- DELETE는 기존값만 존재하므로 :OLD를 사용
-- 해당하는 값을 보고싶을 경우에만 BIND변수를 사용하는 것(꼭 사용해야하는게 아니다)

 

-- [ 실습 ] --

제품의 입고 출고가 INSERT되면 제품테이블의 STOCK이 입고만큼 +되고, 출고만큼 -되는 트리거만들기

 

제품 테이블 / 시퀀스

CREATE TABLE PRODUCT(
    PCODE   NUMBER          PRIMARY KEY,  -- 상품번호
    PNAME   VARCHAR2(30)    NOT NULL,     -- 상품이름
    BRAND   VARCHAR2(30)    NOT NULL,     -- 제조회사
    PRICE   NUMBER,                       -- 가격
    STOCK   NUMBER  DEFAULT 0             -- 재고수량
);

CREATE SEQUENCE PRODUCT_SEQ;

제품의 입고/출고 기록을 입력할 테이블

CREATE TABLE IN_OUT_TBL(
    MANAGE_NO   NUMBER  PRIMARY KEY,
    PCODE       NUMBER  REFERENCES PRODUCT(PCODE),
    IN_OUT_DATE DATE,
    AMOUNT      NUMBER,
    STATUS      CHAR(6) CHECK(STATUS IN('입고', '출고'))
);
CREATE SEQUENCE IN_OUT_SEQ;

SELECT * FROM USER_SEQUENCES; -- 생성한 시퀀스 확인

 

트리거가 없을 경우 로직

INSERT INTO IN_OUT_TBL VALUES(IN_OUT_SEQ.NEXTVAL, 1, SYSDATE, 10, '입고');
INSERT INTO IN_OUT_TBL VALUES(IN_OUT_SEQ.NEXTVAL, 2, SYSDATE, 20, '입고');
UPDATE PRODUCT SET STOCK = 10 WHERE PCODE = 1;
UPDATE PRODUCT SET STOCK = 30 WHERE PCODE = 2;

IN_OUT_TBL 에 INSERT를 함과 동시에 PRODUCT 테이블의 STOCK컬럼을 수동으로 추가/감소 해줘야함

 

위 로직을 자동으로 이루어지게 해주는 TRIGGER생성

CREATE OR REPLACE TRIGGER PRO_INOUT_TRG
AFTER INSERT
ON IN_OUT_TBL
FOR EACH ROW
BEGIN
    IF( :NEW.STATUS LIKE '입고')
    -- 그냥 작성하면 출고가 발생해도 입고가 수행되므로 IF문 필요(출고또한 INSERT이므로)
    THEN 
        UPDATE PRODUCT SET STOCK = STOCK + :NEW.AMOUNT WHERE PCODE = :NEW.PCODE;
    ELSIF( :NEW.STATUS LIKE '출고')
    THEN
        UPDATE PRODUCT SET STOCK = STOCK - :NEW.AMOUNT WHERE PCODE = :NEW.PCODE;
    -- CHECK 제약조건으로 값을 제한해주었기 때문에 ELSE로만 적어도 된다.
    END IF;
END;
/

 

[ 2. DB모델링 ]

 

엔티티(Entity) : 테이블
속성(Attribute) : 컬럼 
인스턴스(instace) : 행(ROW)

DB모델링을 할 때 엔티티 / 속성 / 인스턴스를 어떤걸 어떻게(자료형) 지정해줄 건지 잘 생각해야한다.

 

2 - 1 ) ERDCLOUD 사용하기 : https://www.erdcloud.com/

새 ERD생성 시 SETTING에서 해줘야 할 것

테이블 생성

외래키 설정 시 PK로 사용할건지 선택

최종 ERD

 

-- ERD를 참고하여 테이블만들기 

-- 회원
CREATE TABLE MEMBER_TBL(
    MEMBER_NO       NUMBER          PRIMARY KEY,
    MEMBER_NAME     VARCHAR2(15)    NOT NULL,
    MEMBER_PHONE    CHAR(13)        NOT NULL,
    MEMBER_EMAIL    VARCHAR2(50)    NOT NULL,
    MEMBER_ADDR     VARCHAR2(100)   NOT NULL,
    RENTAL_COUNT    NUMBER  DEFAULT 5 NOT NULL 
);
CREATE SEQUENCE MEMBER_SEQ;

--도서
CREATE TABLE BOOK(
    BOOK_NO         NUMBER          PRIMARY KEY,
    BOOK_TITLE      VARCHAR2(300)   NOT NULL,
    BOOK_WRITE      VARCHAR2(100)   NOT NULL,
    BOOK_GENRE      VARCHAR2(100)   NOT NULL,
    BOOK_COMP       VARCHAR2(100)   NOT NULL,
    RENTAL_PRICE    NUMBER          NOT NULL,
    BOOK_STOCK      NUMBER          NOT NULL
);
CREATE SEQUENCE BOOK_SEQ;

--대출
CREATE TABLE BOOK_RENTAL(
    RENTAL_NO       NUMBER      PRIMARY KEY,
    START_DATE      DATE        NOT NULL,   -- 대여 시작일
    END_DATE        DATE        NOT NULL,   -- 반납 예정일
    RENTAL_STATUS   NUMBER      NOT NULL,   -- 대여상태(0:대여중/1:반납)
    LATE_FEE        NUMBER      NOT NULL,
    MEMBER_NO       NUMBER  REFERENCES MEMBER_TBL(MEMBER_NO) NOT NULL,
    BOOK_NO         NUMBER  REFERENCES BOOK(BOOK_NO) NOT NULL
    -- 외래키주의점 : 제약조건 NOT NULL을 넣어줫기때문에 
    -- 외래키 옵션 중, ON DELETE SET NULL 을 적으면 안된다. 에러발생
);
CREATE SEQUENCE RENTAL_SEQ;

 

MEMBER_TBL 과 BOOK 에 INSERT

INSERT INTO MEMBER_TBL
VALUES(MEMBER_SEQ.NEXTVAL, '회원1', '010-1111-1111', 'abc@naver.com', '서울시 영등포구', DEFAULT);
INSERT INTO MEMBER_TBL
VALUES(MEMBER_SEQ.NEXTVAL, '회원2', '010-2222-2222', 'aaa@naver.com', '서울시 영등포구', DEFAULT);
INSERT INTO MEMBER_TBL
VALUES(MEMBER_SEQ.NEXTVAL, '회원3', '010-3333-3333', 'bbb@naver.com', '서울시 영등포구', DEFAULT);
INSERT INTO MEMBER_TBL
VALUES(MEMBER_SEQ.NEXTVAL, '회원4', '010-4444-4444', 'ccc@naver.com', '서울시 영등포구', DEFAULT);
INSERT INTO MEMBER_TBL
VALUES(MEMBER_SEQ.NEXTVAL, '회원5', '010-5555-5555', 'ddd@naver.com', '서울시 영등포구', DEFAULT);

INSERT INTO BOOK 
VALUES(BOOK_SEQ.NEXTVAL, '어린왕자', '생텍쥐페리', '동화', '출판사1', 10, 10);
INSERT INTO BOOK 
VALUES(BOOK_SEQ.NEXTVAL, '무서운이야기', '삼저자', '스릴러', '출판사2', 20, 20);
INSERT INTO BOOK 
VALUES(BOOK_SEQ.NEXTVAL, '문제집', '오저자', '학습', '출판사3', 10, 100);

 

[ 대여로직 ]

BOOK_RENTAL(도서대여) 테이블에 INSERT를 해주면 MEMBER_TBL과 BOOK도 수정해줘야 할 사항이 생긴다.

1. 도서대여테이블에 INSERT를 하고

2. 회원테이블의 대여가능수 - 1 (UPDATE) 

3. 책 테이블의 책보유수 - 1(UPDATE)

-- 회원3이 어린왕자(1번도서)를 대여 / BOOK_RENTAL 테이블INSERT
INSERT INTO BOOK_RENTAL 
VALUES(RENTAL_SEQ.NEXTVAL, SYSDATE, SYSDATE+3, 0, 0, 3, 1);
-- BOOK_RENTAL이 되면 수행되어야하는 로직
-- 1. 회원의 대여가능 도서수 수정, MEMBER_TBL의 RENTAL_COUNT - 1
UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT - 1 WHERE MEMBER_NO = 3;
-- 2. 책의 보유수 수정, BOOK의 BOOK_STOCK - 1
UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK - 1 WHERE BOOK_NO = 1;
COMMIT; 
-- INSERT / UPDATE / UPDATE 현재 이 세 작업이 하나의 트랜잭션 - 데이터가 같이 움직여야함
-- 이 트랜잭션이 끝난 후에 COMMIT을 해줘야한다.(하나라도 잘못되면 되돌아가야하니까)

이 세 작업이 하나의 트랜잭션 -> 트리거를 생성해주면 좋다.

 

대여트리거 생성

CREATE OR REPLACE TRIGGER RENTAL_BOOK_TRG
AFTER INSERT ON BOOK_RENTAL
FOR EACH ROW
BEGIN   
    UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK - 1 WHERE BOOK_NO = :NEW.BOOK_NO;   
    UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT - 1 WHERE MEMBER_NO = :NEW.MEMBER_NO;
END;
/

 

[ 반납 로직 ]

1. BOOK_RENTAL 테이블에서 RENTAL_STATUS값이 1로변경(UPDATE)
2. 반납한 회원 대출가능 도서수 + 1(UPDATE)
3. 반납된 책 재고 + 1(UPDATE)

-- 회원3이 어린왕자(1번도서)를 대여한걸 반납
-- 1. BOOK_RENTAL 테이블에서 RENTAL_STATUS값이 1로변경
UPDATE BOOK_RENTAL SET RENTAL_STATUS = 1 WHERE RENTAL_NO = 1;
-- 2. 반납한 회원 대출가능 도서수 + 1
UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT+1 WHERE MEMBER_NO = 3;
-- 3. 반납된 책 재고 + 1
UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK+1 WHERE BOOK_NO = 1;

위 로직을 트리거로 만들기

반납트리거 생성

CREATE OR REPLACE TRIGGER RETURN_BOOK_TRG
AFTER UPDATE ON BOOK_RENTAL
FOR EACH ROW
BEGIN
    UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT+1 WHERE MEMBER_NO = :OLD.MEMBER_NO;
    UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK+1 WHERE BOOK_NO = :OLD.BOOK_NO;
END;
/

 

+) 추가내용

 MEMBER_TBL의 RENTAL_COUNT(대여가능도서수) 컬럼을 삭제하고, 아래의 방법처럼 서브쿼리를 사용하는것도 하나의방법이다. 이 방식을 사용하게되면, 트리거를 만들 때 신경써야할 부분이 한군데 줄어드므로 도움이 됨

SELECT MEMBER_NAME,
       (SELECT COUNT(*) 
        FROM BOOK_RENTAL BR 
        WHERE BR.MEMBER_NO = M.MEMBER_NO AND RENTAL_STATUS = 0) 대여한책수
FROM MEMBER_TBL M;