총괄적 PL/SQL 실습-도서대여점

--------------------------------------------------------------------------------============---

ID YEARPAY POINT TOTRENTSU(총빌려간 갯수) CURRENTRENTSU(현재빌려간 갯수)

--------------------------------------------------------------------------------=============---

ALTER TABLE TBLHUMAN

ADD TOTRENTSU NUMBER DEFAULT 0;

ALTER TABLE TBLHUMAN

ADD CURRENTRENTSU NUMBER DEFAULT 0;

ALTER TABLE TBLHUMAN

ADD POINT NUMBER DEFAULT 0;

SELECT *

FROM TBLHUMAN;

--------------------------------------------------------------------------------=============

도서관련 정보 테이블을 생성한다.

CREATE TABLE TBLBOOKINFO

(BOOKID VARCHAR2(20)

,BOOKNAME VARCHAR2(20)

,PUBLISHDAY VARCHAR2(10)

,RENTYN CHAR(1) DEFAULT 'N'--RENTYN(현재대여여부)

,LEASEDANGA NUMBER

);

TBLBOOKINFO 테이블에 도서정보들을 기입한다.

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('YKSS01','연금술사','2007-12-01',1000);

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('YKSS02','연금술사','2007-12-01',1000);

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('YKSS03','연금술사','2007-12-01',1000);

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('SDLL01','신데렐라','2008-03-01',5000);

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('BITSK01','비트신권','2008-09-03',200);

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('BITSK02','비트신권','2008-09-03',200);

INSERT INTO TBLBOOKINFO(BOOKID,BOOKNAME,PUBLISHDAY,LEASEDANGA)

VALUES('SING01','싱','2008-04-03',300);

SELECT *

FROM TBLBOOKINFO;

--------------------------------------------------------------------------------

도서 대여관련정보 테이블을 생성한다.

CREATE TABLE TBLCURRENTRENT

(ID NUMBER

,BOOKID VARCHAR2(20)

,RENTDAY DATE DEFAULT SYSDATE

,RETURNYJDAY DATE

);

--사장님이 제일 궁금해하는 매출액은 SELECT * FROM TBLBOOKINFO; SELECT * FROM TBLCURRNETRENT; 의 특정 컬럼들을 쪼인하면 될 것 같지만, 단가가 바뀌면 문제가 발생하게된다. 즉 1년전보다 책의 대여 단가가 오른 상태에서 예전의 매출을 조회하게 되면, 오류가 발생하게 된다. 이러한 문제들을 방지하기 위해 매출액에 관련된 하나의 table을 따로 만드는 것이 좋다.

RETURNYJDAY에 DEFAULT 값이 없는 것은 신간/구간의 대여기간이 다르기 때문이다.

CREATE TABLE TBLCURRENTRENT_HISTORY

(ID NUMBER

,BOOKID VARCHAR2(20)

,RENTDAY DATE

,RETURNYJDAY DATE

,RETURNDAY DATE DEFAULT SYSDATE

,DELAYPAY NUMBER

);

--DELAYPAY(연체료),RETURNYJDAY(반납예정일),RETURNDAY(반납일)

--SYSDATE를 기준으로 1년 이내는 신간, 1년 이상이라면 구간으로 본다.

매출액 관련 정보 테이블을 생성한다.

--매출액 테이블생성

CREATE TABLE TBLMAECHUL

(BOOKID VARCHAR2(20)

,BOOKNAME VARCHAR2(20)

,RENTDAY DATE

,LEASEDANGA NUMBER

);

--위에서 기입한 정보들을 총체적으로 한번 점검한다.

SELECT *

FROM TBLCURRENTRENT;

SELECT *

FROM TBLHUMAN;

SELECT *

FROM TBLBOOKINFO;

SELECT *

FROM TBLMAECHUL;

1.회원가입을 하고자 한다.
->insert
 insert,update 하기 전에 주민번호와 암호를 규칙에 맞는지 아닌지를 검사하도록 한다.
 (=Before Row Trigger)

2.도서대여 테이블에 INSERT를 하기전에 검사해야 할 내용
Before trigger
 --회원 테이블의 currnetrentsu 컬럼에 값이 3 미만이라면 insert를 허락해주지만 3이상 값이면 insert가 불가능해야 한다.
--도서 테이블의 RENTYN 컬럼의 값이 'N' 이라면 INSERT를 허락해주지만, 'Y'라면 INSERT를 불허한다.

3.도서대여 테이블에 insert를 한 이후에 변경되어야 할 내용을 알아보자.
 (=After Trigger)
 1) 회원테이블에서 변경되어야 할 사항
   (TOTRENTSU , CURRENTRENTSU +1씩 증가 <단,CURRENTRENTSU의 MAX값은 3이다>
   & POINT는 도서 LEASEDANGA의 1%를 적립한다.)
 2) 도서테이블에 변경되어야 할 사항
  --RENTYN에 'Y'로 변경 (도서의 대여 상태 유무 확인 Coloum)
 3) 도서가 대여되면, 매출 테이블에 새롭게 대여된 도서를 INSERT 하여 준다.

5.Before/After trigger를 작성한다.

--BEFORE TRIGGER 생성 CREATE OR REPLACE TRIGGER BTRI_TBLCURRENTRENT_INS BEFORE INSERT ON TBLCURRENTRENT FOR EACH ROW DECLARE V_CURRENTRENTSU TBLHUMAN.CURRENTRENTSU%TYPE; V_RENTYN TBLBOOKINFO.RENTYN%TYPE; CURRENTRENTSUOVER EXCEPTION; RENTING EXCEPTION; BEGIN --반납되지 않은 책 확인 SELECT CURRENTRENTSU INTO V_CURRENTRENTSU FROM TBLHUMAN WHERE ID = :NEW.ID; --빌려가려는 책이 대여중인지 아닌지 확인 SELECT RENTYN INTO V_RENTYN FROM TBLBOOKINFO WHERE BOOKID = :NEW.BOOKID; --현재 총대여 중인 책이 3권이상이면 'CURRENTRENTSUOVER' 발생 IF V_CURRENTRENTSU = 3 THEN RAISE CURRENTRENTSUOVER; ELSIF V_RENTYN IN('Y','y') THEN RAISE RENTING; END IF; EXCEPTION WHEN CURRENTRENTSUOVER THEN RAISE_APPLICATION_ERROR(-20004,'미반납 도서가 3권이므로 대여불가!!!'); WHEN RENTING THEN RAISE_APPLICATION_ERROR(-20005,'해당도서는 현재 대여중이므로 대여불가!!!'); END; / --AFTER TRIGGER 생성 (TBLCURRENT) CREATE OR REPLACE TRIGGER ATRI_TBLCURRENTRENT_INS AFTER INSERT ON TBLCURRENTRENT FOR EACH ROW DECLARE V_LEASEDANGA TBLBOOKINFO.LEASEDANGA%TYPE; V_BOOKNAME TBLBOOKINFO.BOOKNAME%TYPE; BEGIN -- 1. 해당 고객의 TOTRENTSU, CURRENTRENTSU 을 1 증가 -- YEARPAY 는 대여해간 도서의 단가액 만큼 감한다. -- POINT 는 대여해간 도서의 단가액의 1% 만큼 적립해준다. SELECT LEASEDANGA, BOOKNAME INTO V_LEASEDANGA, V_BOOKNAME FROM TBLBOOKINFO WHERE BOOKID = :NEW.BOOKID; UPDATE TBLHUMAN SET TOTRENTSU = TOTRENTSU + 1, CURRENTRENTSU = CURRENTRENTSU + 1, YEARPAY = YEARPAY - V_LEASEDANGA, POINT = POINT + (V_LEASEDANGA * 0.01) WHERE ID = :NEW.ID; -- 2. 도서정보 테이블에 해당 도서는 대여중이라고 표시해야함. UPDATE TBLBOOKINFO SET RENTYN = 'Y' WHERE BOOKID = :NEW.BOOKID; -- 3. 매출테이블에 기록해준다. INSERT INTO TBLMAECHUL VALUES (:NEW.BOOKID, V_BOOKNAME, SYSDATE, V_LEASEDANGA); END; /

--대여하기

--id 2인 송미리가 책들을 빌린다.

INSERT INTO TBLCURRENTRENT

VALUES(2,'SDLL01',SYSDATE,SYSDATE+2);

INSERT INTO TBLCURRENTRENT

VALUES(2,'YKSS02',SYSDATE,SYSDATE+2);

INSERT INTO TBLCURRENTRENT

VALUES(2,'BITSK01',SYSDATE,SYSDATE+2);

--한번에 빌려갈 수 있는 도서 3권을 다 빌려감

--다시 빌려보면

INSERT INTO TBLCURRENTRENT

VALUES(2,'SING01',SYSDATE,SYSDATE+2);

--ORA-20004: 미반납 도서가 3권이므로 대여불가!!!

--ORA-06512: "SCOTT.BTRI_TBLCURRENTRENT_INS", 줄 26에서

--ORA-04088: 트리거 'SCOTT.BTRI_TBLCURRENTRENT_INS'의 수행시 오류

--다른 사용자(최민성)이 이미 빌려간 도서 '신데렐라를 빌려가려 시도해보자

INSERT INTO TBLCURRENTRENT

VALUES(1,'SDLL01',SYSDATE,SYSDATE+2);

--ORA-20005: 해당도서는 현재 대여중이므로 대여불가!!!

--ORA-06512: "SCOTT.BTRI_TBLCURRENTRENT_INS", 줄 28에서

--ORA-04088: 트리거 'SCOTT.BTRI_TBLCURRENTRENT_INS'의 수행시 오류