Procedure for Taking a Book Library management Database

Library Management by through replace the sql option it will perform the taking book process it take attribute as Bname – book name as character roll no as number. First they enter card no if it does not match then it will display No card available message. If it is matches then go to the next step then check whether the book is available or not, if it is not available then it will display book is not available in library management database. it is matches the it will replace the table database the book available is no.
Source code in PL SQL programming Algorithm
SQL> CREATE OR REPLACE PROCEDURE SUB(BNAME CHAR,ROLL_NO 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 BEGIN
9 SELECT * INTO STUD_REC FROM STUDENT WHERE ROLLNO=ROLL_NO;
10 IF STUD_REC.NO_CARD=0 THEN
11 DBMS_OUTPUT.PUT_LINE('*************************************');
12 DBMS_OUTPUT.PUT_LINE('**********NO CARD AVAILABLE**********');
13 DBMS_OUTPUT.PUT_LINE('*************************************');
14 ELSE
15 SELECT COUNT(*) INTO NO_OF_BOOKS FROM BOOKS WHERE BOOKNAME=BNAME AND AVAILABLE='YES';
16 IF NO_OF_BOOKS=0 THEN
17 DBMS_OUTPUT.PUT_LINE('*************************************');
18 DBMS_OUTPUT.PUT_LINE('**********BOOK NOTAVAILABLE**********');
19 DBMS_OUTPUT.PUT_LINE('*************************************');
20 ELSE
21 SELECT MIN(BOOKNO) INTO BOOK_NO FROM BOOKS WHERE BOOKNAME=BNAME AND AVAILABLE='YES';
22 INSERT INTO SUBSCRIPTION VALUES(BOOK_NO,ROLL_NO,SYSDATE,SYSDATE+7,0,'NOT RETURNED');
23 UPDATE STUDENT SET NO_CARD=NO_CARD-1 WHERE ROLLNO=ROLL_NO;
24 UPDATE BOOKS SET AVAILABLE='NO' WHERE BOOKNO=BOOK_NO;
25 UPDATE BOOKS SET SUBSCRIBED_TO=ROLL_NO WHERE BOOKNO=BOOK_NO;
26 END IF;
27 END IF;
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 DBMS_OUTPUT.PUT_LINE('*************************************');
31 DBMS_OUTPUT.PUT_LINE('*********YOU ARE NOT A USER *********');
32 DBMS_OUTPUT.PUT_LINE('*************************************');
33 END;
34 /
Output DBMS Lab CS1307 Database Management system
PROCEDURE CREATED.

SQL> EXEC SUB('CN',1);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

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 NO 1
1790 CN YES 0
1791 CN YES 0
1792 CN YES 0
1793 CN YES 0
1794 CN YES 0
13 ROWS SELECTED.

SQL> SELECT * FROM SUBSCRIPTION;
BOOKN0 ROLLNO DO_SUB DO_RETURN FINE_AMOUNT STATUS
------------ ----------- ------------ ----------------- --------------------- --------------------
1789 1 09-OCT-08 16-OCT-08 0 NOT RETURNED