CREATION
SQL> create table lib ( bookname varchar(15),author varchar(15), publicationvarchar(10), noofcopies number);
Table created.
SQL> create table student ( rollno number, name varchar(20), no_card number );
Table created.
SQL> create table books ( bookno number, bookname varchar(15),
available varchar(10),subscribed_to number );
Table created.
SQL> create table subscription ( bookno number, rollno number, do_sub date,
do_return date, fineamount number,status varchar(10) );
Table created.SQL> desc lib;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOKNAME VARCHAR2(15)
AUTHOR VARCHAR2(15)
PUBLICATION VARCHAR2(10)
NOOFCOPIES NUMBER
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NUMBER
NAME VARCHAR2(20)
NO_CARD NUMBER
SQL> desc books;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOKNO NUMBER
BOOKNAME VARCHAR2(15)
AVAILABLE VARCHAR2(10)
SUBSCRIBED_TO NUMBER
SQL> desc subscription;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOKNO NUMBER
ROLLNO NUMBER
DO_SUB DATE
DO_RETURN DATE
FINEAMOUNT NUMBER
STATUS VARCHAR2(10)
INSERTION
SQL> begin
2 insert into student values(&rollno,'&name',2);
3 end;
4 /
Enter value for rollno: 5058
Enter value for name: pavi
old 2: insert into student values(&rollno,'&name',2);
new 2: insert into student values(5058,'pavi',2);
PL/SQL procedure successfully completed.
SQL> /
Enter value for rollno: 5100
Enter value for name: vidhya
old 2: insert into student values(&rollno,'&name',2);
new 2: insert into student values(5100,'vidhya',2);
PL/SQL procedure successfully completed.
SQL> /
Enter value for rollno: 5097
Enter value for name: anitha
old 2: insert into student values(&rollno,'&name',2);
new 2: insert into student values(5097,'anitha',2);
PL/SQL procedure successfully completed.
SQL> /
Enter value for rollno: 5066
Enter value for name: hari
old 2: insert into student values(&rollno,'&name',2);
new 2: insert into student values(5066,'hari',2);
PL/SQL procedure successfully completed.
SQL> /
Enter value for rollno: 5056
Enter value for name: suresh
old 2: insert into student values(&rollno,'&name',2);
new 2: insert into student values(5056,'suresh',2);
PL/SQL procedure successfully completed.
SQL> ed ins;
bookno number;
bname varchar(15);
noc number;
noc1 number;
author varchar(15);
publication varchar(10);
nobooks number;
lib_rec lib%rowtype;
begin
bookno:=&bookno;
bname:='&bookname';
author:='&author';
publication:='&publication';
noc:=&noofcopies;
noc1:=noc;
select count(*) into nobooks from lib where bookname=bname;
if nobooks=0 then
insert into lib values(bname,author,publication,noc);
else
update lib set noofcopies=noofcopies+noc where bookname=bname;
end if;
while noc!=0
loop
insert into books values(bookno,bname,'yes',0);
noc:=noc-1;
bookno:=bookno+1;
end loop;
end;
SQL> @ ins;
30 /
Enter value for bookno: 23
old 11: bookno:=&bookno;
new 11: bookno:=23;
Enter value for bookname: cn
old 12: bname:='&bookname';
new 12: bname:='cn';
Enter value for author: forozan
old 13: author:='&author';
new 13: author:='forozan';
Enter value for publication: tata
old 14: publication:='&publication';
new 14: publication:='tata';
Enter value for noofcopies: 4
old 15: noc:=&noofcopies;
new 15: noc:=4;
PL/SQL procedure successfully completed.
SQL> /
Enter value for bookno: 28
old 11: bookno:=&bookno;
new 11: bookno:=28;
Enter value for bookname: ooad
old 12: bname:='&bookname';
new 12: bname:='ooad';
Enter value for author: scilber
old 13: author:='&author';
new 13: author:='scilber';
Enter value for publication: tata
old 14: publication:='&publication';
new 14: publication:='tata';
Enter value for noofcopies: 4
old 15: noc:=&noofcopies;
new 15: noc:=4;
PL/SQL procedure successfully completed.
SQL> /
Enter value for bookno: 32
old 11: bookno:=&bookno;
new 11: bookno:=32;
Enter value for bookname: dbms
old 12: bname:='&bookname';
new 12: bname:='dbms';
Enter value for author: elmasri
old 13: author:='&author';
new 13: author:='elmasri';
Enter value for publication: tata
old 14: publication:='&publication';
new 14: publication:='tata';
Enter value for noofcopies: 4
old 15: noc:=&noofcopies;
new 15: noc:=4;
PL/SQL procedure successfully completed.
SQL> /
Enter value for bookno: 37
old 11: bookno:=&bookno;
new 11: bookno:=37;
Enter value for bookname: evs
old 12: bname:='&bookname';
new 12: bname:='evs';
Enter value for author: ravi
old 13: author:='&author';
new 13: author:='ravi';
Enter value for publication: scitech
old 14: publication:='&publication';
new 14: publication:='scitech';
Enter value for noofcopies: 1
old 15: noc:=&noofcopies;
new 15: noc:=1;
PL/SQL procedure successfully completed.
DIDPLAY
SQL> select * from lib;
BOOKNAME AUTHOR PUBLICATION NOOFCOPIES
--------------- --------------- ---------- -------------------------------------------------
cn forozan tata 4
ooad scilber tata 4
dbms elmasri tata 4
evs ravi scitech 1
SQL> select * from books;
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- -------------------------------------------------
23 cn yes 0
24 cn yes 0
25 cn yes 0
26 cn yes 0
28 ooad yes 0
29 ooad yes 0
30 ooad yes 0
31 ooad yes 0
32 dbms yes 0
33 dbms yes 0
34 dbms yes 0
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- ------------------------------------------------------
35 dbms yes 0
37 evs yes 0
13 rows selected.
SQL> select * from student;
ROLLNO NAME NO_CARD
---------- -------------------- -----------------------------
5058 pavi 2
5100 vidhya 2
5097 anitha 2
5066 hari 2
5056 suresh 2
PROCEDURE TO TAKE A BOOK
SQL> ed take;
create or replace procedure sub(bname char,roll_no number)as
lib_rec lib%rowtype;
book_rec books%rowtype;
stud_rec student%rowtype;
sub_rec subscription%rowtype;
book_no number;
no_of_books number;
begin
select * into stud_rec from student where rollno=roll_no;
if stud_rec.no_card=0 then
dbms_output.put_line('***************');
dbms_output.put_line('no card available');
dbms_output.put_line('***************');
else
select count(*) into no_of_books from books where bookname=bname and available='yes';
if no_of_books=0 then
dbms_output.put_line('***************');
dbms_output.put_line('book not available');
dbms_output.put_line('***************');
else
select min(bookno) into book_no from books where bookname=bname and available='yes';
insert into subscription values(book_no,roll_no,sysdate,sysdate+7,0,'ntreturned');
update student set no_card=no_card-1 where rollno=roll_no;
update books set available='no' where bookno=book_no;
update books set subscribed_to=roll_no where bookno=book_no;
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('***************');
dbms_output.put_line('u are not a user');
dbms_output.put_line('***************');
end;
SQL> @ take;
35 /
Procedure created.
SQL> exec sub('evs',5058);
PL/SQL procedure successfully completed.
SQL> select * from books;
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- ------------------------------------------------------------
23 cn yes 0
24 cn yes 0
25 cn yes 0
26 cn yes 0
28 ooad yes 0
29 ooad yes 0
30 ooad yes 0
31 ooad yes 0
32 dbms yes 0
33 dbms yes 0
34 dbms yes 0
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- ------------------------------------------------------
35 dbms yes 0
37 evs no 5058
13 rows selected.
SQL> select * from subscription;
BOOKNO ROLLNO DO_SUB DO_RETURN FINEAMOUNT STATUS
---------- ---------- --------- --------- ---------- -------------------------------------------------------
37 5058 21-SEP-09 28-SEP-09 0 ntreturned
SQL> select * from student;
ROLLNO NAME NO_CARD
---------- -------------------- ------------------------
5058 pavi 1
5100 vidhya 2
5097 anitha 2
5066 hari 2
5056 suresh 2
SQL> exec sub('evs',5100);
***************
book not available
***************
PL/SQL procedure successfully completed.
SQL> exec sub('ooad',5058);
PL/SQL procedure successfully completed.
SQL> select * from books;
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- -----------------------------------------------------
23 cn yes 0
24 cn yes 0
25 cn yes 0
26 cn yes 0
28 ooad no 5058
29 ooad yes 0
30 ooad yes 0
31 ooad yes 0
32 dbms yes 0
33 dbms yes 0
34 dbms yes 0
35 dbms yes 0
37 evs no 5058
SQL> select * from subscription;
BOOKNO ROLLNO DO_SUB DO_RETURN FINEAMOUNT STATUS
---------- ---------- --------- --------- ---------- --------------------------------------------------------
37 5058 21-SEP-09 28-SEP-09 0 ntreturned
28 5058 21-SEP-09 28-SEP-09 0 ntreturned
SQL> select * from student;
ROLLNO NAME NO_CARD
---------- -------------------- ------------------------
5058 pavi 0
5100 vidhya 2
5097 anitha 2
5066 hari 2
5056 suresh 2
SQL> exec sub('dbms',5058);
***************
no card available
***************
PL/SQL procedure successfully completed.
Procedure created.
SQL> ed ret;
create or replace procedure ret(bno number)as
lib_rec lib%rowtype;
book_rec books%rowtype;
stud_rec student%rowtype;
sub_rec subscription%rowtype;
book_no number;
no_of_books number;
fine number;
begin
select * into book_rec from books where bookno=bno;
if book_rec.available='yes' then
dbms_output.put_line('$$$$$$$$$$$$');
dbms_output.put_line('book available');
dbms_output.put_line('$$$$$$$$$$$$');
else
update subscription set do_return=sysdate where bookno=bno and status='ntreturned';
select do_return-do_sub into fine from subscription where bookno=bno and status='ntreturned';
if fine>7 then
update subscription set fineamount=fine where bookno=bno and status='ntreturned';
dbms_output.put_line('$$$$$$$$$$$$');
dbms_output.put_line('u have to pay afine of||rs||fine||');
dbms_output.put_line('$$$$$$$$$$$$');
end if;
update subscription set status='returned' where bookno=bno;
update student set no_card=no_card+1 where rollno=book_rec.subscribed_to;
update books set available='yes' where bookno=bno;
update books set subscribed_to=0 where bookno=bno;
end if;
exception
when no_data_found then
dbms_output.put_line('$$$$$$$$$$$$');
dbms_output.put_line('book doesnot belong to library');
dbms_output.put_line('$$$$$$$$$$$$');
end;
SQL> @ ret;
304 /
Procedure created.
SQL> exec ret(28);
PL/SQL procedure successfully completed.
SQL> select * from subscription;
BOOKNO ROLLNO DO_SUB DO_RETURN FINEAMOUNT STATUS
---------- ---------- --------- --------- ---------- -----------------------------------------------------
37 5058 21-SEP-09 28-SEP-09 0 returned
28 5058 21-SEP-09 21-SEP-09 0 returned
SQL> select * from books;
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- --------------------------------------------------------
23 cn yes 0
24 cn yes 0
25 cn yes 0
26 cn yes 0
28 ooad yes 0
29 ooad yes 0
30 ooad yes 0
31 ooad yes 0
32 dbms yes 0
33 dbms yes 0
34 dbms yes 0
BOOKNO BOOKNAME AVAILABLE SUBSCRIBED_TO
---------- --------------- ---------- ----------------------------------------------------------
35 dbms yes 0
37 evs no 5058
13 rows selected.
SQL> select * from student;
ROLLNO NAME NO_CARD
---------- -------------------- ------------------------
5058 pavi 1
5100 vidhya 2
5097 anitha 2
5066 hari 2
5056 suresh 2
SQL> exec ret(345);
$$$$$$$$$$$$
book doesnot belong to library
$$$$$$$$$$$$
EmoticonEmoticon