Library Management Database System Procedure for Returning a Book sql programming

Library management System returning book process it has the input as Book no as number. First it get the data from the database with the specific book no. Then it will check whether book is return in the within specific data or out of date if out of date then it will calculate fine. If the book is not available then it will display the book is not belong to the library.
Source code in PL SQL programming Algorithm
SQL> CREATE OR REPLACE PROCEDURE RET(BNO NUMBER) AS
2 LIB_REC LIB%ROWTYPE;
3 BOOK_REC BOOKS%ROWTYPE;
4 STUD_REC STUDENT%ROWTYPE;
5 SUB_REC SUBSCRIPTION%ROWTYPE;
6 BOOK_NO NUMBER;
7 NO_OF_BOOKS NUMBER;
8 FINE NUMBER;
9 BEGIN
10 SELECT * INTO BOOK_REC FROM BOOKS WHERE BOOKNO=BNO;
11 IF BOOK_REC.AVAILABLE='YES' THEN
12 DBMS_OUTPUT.PUT_LINE('*************************************');
13 DBMS_OUTPUT.PUT_LINE('********** BOOK AVAILABLE **********');
14 DBMS_OUTPUT.PUT_LINE('*************************************');
15 ELSE
16 UPDATE SUBSCRIPTION SET DO_RETURN=SYSDATE WHERE BOOKN0=BNO AND STATUS='NOT RETURNED';
17 SELECT DO_RETURN-DO_SUB INTO FINE FROM SUBSCRIPTION WHERE BOOKN0=BNO AND STATUS='NOT RETURNED';
18 IF FINE>7 THEN
19 UPDATE SUBSCRIPTION SET FINE_AMOUNT=FINE WHERE BOOKN0=BNO AND STATUS='NOT RETURNED';
20 DBMS_OUTPUT.PUT_LINE('*************************************');
21 DBMS_OUTPUT.PUT_LINE('**YOU HAVE TO PAY A FINE OF**||RS||FINE||');
22 DBMS_OUTPUT.PUT_LINE('*************************************');
23 END IF;
24 UPDATE SUBSCRIPTION SET STATUS='RETURNED' WHERE BOOKN0=BNO;
25 UPDATE STUDENT SET NO_CARD=NO_CARD+1 WHERE ROLLNO=BOOK_REC.SUBSCRIBED_TO;
26 UPDATE BOOKS SET AVAILABLE='YES' WHERE BOOKNO=BNO;
27 UPDATE BOOKS SET SUBSCRIBED_TO=0 WHERE BOOKNO=BNO;
28 END IF;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 DBMS_OUTPUT.PUT_LINE('*************************************');
32 DBMS_OUTPUT.PUT_LINE('**** BOOK DOES NOT BELONG TO LIB*****');
33 DBMS_OUTPUT.PUT_LINE('*************************************');
34 END;
35 /

Output DBMS Lab CS1307 Database Management system
SQL> EXEC RET(1789);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT * FROM SUBSCRIPTION;
BOOKN0 ROLLNO DO_SUB DO_RETURN FINE_AMOUNT STATUS
------------ ----------- ------------ ----------------- --------------------- --------------------
1789 1 09-OCT-08 09-OCT-08 0 RETURNED

SQL> SELECT * FROM BOOKS;
BOOKNO BOOKNAME AVA SUBSCRIBED_TO
------------ ----------------- ------ ----------
1234 DBMS YES 0
1235 DBMS YES 0
1236 DBMS YES 0
1237 DBMS YES 0
1238 DBMS YES 0
1256 OOAD YES 0
1257 OOAD YES 0
1789 CN YES 0
1790 CN YES 0
1791 CN YES 0
1792 CN YES 0
1793 CN YES 0
1794 CN YES 0
13 ROWS SELECTED.

Related post