ERDCLOUD
CREATE SEQUENCE MEMBER_SEQ;
CREATE SEQUENCE BOOK_SEQ;
CREATE SEQUENCE RENTAL_SEQ;
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
);
INSERT INTO MEMBER_TBL VALUES(MEMBER_SEQ.NEXTVAL, '회원1', '010-111-1111','user1@naver.com','서울시',default);
INSERT INTO MEMBER_TBL VALUES(MEMBER_SEQ.NEXTVAL, '회원2', '010-111-2222','user2@naver.com','부산시',default);
INSERT INTO MEMBER_TBL VALUES(MEMBER_SEQ.NEXTVAL, '회원3', '010-111-3333','user3@naver.com','뉴욕시',default);
INSERT INTO MEMBER_TBL VALUES(MEMBER_SEQ.NEXTVAL, '회원4', '010-111-4444','user4@naver.com','호치민시',default);
INSERT INTO MEMBER_TBL VALUES(MEMBER_SEQ.NEXTVAL, '회원5', '010-111-5555','user5@naver.com','로마시',default);
CREATE TABLE BOOK_RENTAL(
RENTAL_NO NUMBER PRIMARY KEY,
START_DATE DATE NOT NULL, -- 대여 시작일
END_DATE DATE NOT NULL, -- 반납 예정일
LATE_FEE NUMBER NOT NULL,
RENTAL_STATUS NUMBER NOT NULL, -- 대여상태(0:대여중/1:반납)
MEMBER_NO NUMBER REFERENCES MEMBER_TBL NOT NULL,
BOOK_NO NUMBER NOT NULL REFERENCES BOOK
);
CREATE TABLE BOOK(
BOOK_NO NUMBER PRIMARY KEY,
BOOK_TITLE VARCHAR2(300) NOT NULL,
BOOK_WRITER VARCHAR2(100) NOT NULL,
BOOK_GENRE VARCHAR2(100) NOT NULL,
BOOK_COMPANY VARCHAR2(100) NOT NULL,
RENTAL_PRICE NUMBER NOT NULL,
BOOK_STOCK NUMBER NOT NULL
);
INSERT INTO BOOK VALUES(BOOK_SEQ.NEXTVAL,'해리포터','조앤K롤링','판타지','출판사1',10,10);
INSERT INTO BOOK VALUES(BOOK_SEQ.NEXTVAL,'반지의제왕','작가2','판타지','출판사2',10,10);
INSERT INTO BOOK VALUES(BOOK_SEQ.NEXTVAL,'듄','작가3','SF','출판사3',10,10);
INSERT INTO BOOK VALUES(BOOK_SEQ.NEXTVAL,'아바타','작가4','SF','출판사4',10,10);
INSERT INTO BOOK VALUES(BOOK_SEQ.NEXTVAL,'브리짓존스의일기','작가5','로맨스','출판사5',10,10);
--트랜젝션. 1-2-3 이 세트로 작동돼야한다. 그래서 우리는 이것을 TRIGGER로 만들어줄 수 있다.
--여기서는 우선 풀어서 써보자.
--1. RENTAL 테이블 INSERT
INSERT INTO BOOK_RENTAL VALUES(RENTAL_SEQ.NEXTVAL, SYSDATE, SYSDATE+3, 0,0,15,10);
INSERT INTO BOOK_RENTAL VALUES(RENTAL_SEQ.NEXTVAL, SYSDATE, SYSDATE+3, 0,0,14,9);
--2. 회원 대여가능 도서수 수정
UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT-1 WHERE MEMBER_NO=15;
UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT-1 WHERE MEMBER_NO=14;
--3. 책 보유수 수정
UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK-1 WHERE BOOK_NO=10;
UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK-1 WHERE BOOK_NO=9;
COMMIT;
---위의 1-2-3 트랜젝션으로 일일이 하지말고 아래처럼 TRIGGER를 만들어 간편하게 관리한다.
CREATE OR REPLACE TRIGGER RENTAL_BOOK_TRG
AFTER INSERT ON BOOK_RENTAL
FOR EACH ROW
BEGIN
UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT-1 WHERE MEMBER_NO= :NEW.MEMBER_NO;
UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK-1 WHERE BOOK_NO = :NEW.BOOK_NO;
END;
/
--TRIGGER가 잘 작동되는지 INSERT해서 확인해보자
INSERT INTO BOOK_RENTAL VALUES(RENTAL_SEQ.NEXTVAL, SYSDATE, SYSDATE+3, 0,0,14,9);
SELECT FROM MEMBER_TBL;
SELECT FROM BOOK;
SELECT * FROM BOOK_RENTAL;
-- BOOK_RENTAL 테이블에서 RENTAL_STATUS값이 1로 변경
UPDATE BOOK_RENTAL SET RENTAL_STATUS = 1 WHERE RENTAL_NO= 1;
-- 반납한 회원 대출가능도서수 +1
UPDATE MEMBER_TBL SET RENTAL_COUNT = RENTAL_COUNT+1 WHERE MEMBER_NO=15;
-- 반납된 책 재고 +1
UPDATE BOOK SET BOOK_STOCK = BOOK_STOCK+1 WHERE BOOK_NO=10;
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;
/
------------------------------------조회하기--------------------------------------
SELECT
RENTAL_NO,
TO_CHAR(START_DATE, 'YYYY-MM-DD') 대여일,
TO_CHAR(END_DATE,'YYYY-MM-DD') 반납예정일,
LATE_FEE 연체료,
DECODE(RENTAL_STATUS,0,'대여중','1','반납완료') 대여상태,
MEMBER_NAME,
BOOK_TITLE
FROM BOOK_RENTAL
JOIN MEMBER_TBL USING(MEMBER_NO)
JOIN BOOK USING(BOOK_NO);
