>>>>>>>>>>>>>>>>>>>>CREATE TABLE<<<<<<<<<<<<<<<<<<<<
create table book_rec(book_name char(15) primary key,author char(15),total_copies number(5),available_copies number(5));
create table membership_rec(mem_id number(5) primary key,mem_name char(15),no_of_books_taken number(2));
create table book_details(book_id number(5) primary key,book_name char(15) references book_rec(book_name),mem_id number(5));
create table circulation_rec(book_id number(5) references book_details(book_id),mem_id number(5) references membership_rec(mem_id),issue_date date,return_date date);
>>>>>>>>>>>>>>>>>>>>ADD A NEW BOOK<<<<<<<<<<<<<<<<<<<<
declare
bknam char(15);
auth char(15);
tot number(5);
id number(5);
no number(5);
i number(3);
begin
bknam:='&bknam';
auth:='&auth';
tot:=&tot;
insert into book_rec values(bknam,auth,tot,tot);
for i in 1..tot
loop
select MAX(book_id) into no from book_details;
if no is not null then
id:=no+1;
else
id:=1;
end if;
insert into book_details values(id,bknam,null);
end loop;
end;
/
Enter value for bknam: DCD
old 9: bknam:='&bknam';
new 9: bknam:='DCD';
Enter value for auth: Mano
old 10: auth:='&auth';
new 10: auth:='Mano';
Enter value for tot: 3
old 11: tot:=&tot;
new 11: tot:=3;
PL/SQL procedure successfully completed.
SQL> /
Enter value for bknam: OS
old 9: bknam:='&bknam';
new 9: bknam:='OS';
Enter value for auth: Silber
old 10: auth:='&auth';
new 10: auth:='Silber';
Enter value for tot: 2
old 11: tot:=&tot;
new 11: tot:=2;
PL/SQL procedure successfully completed.
SQL> select * from book_rec;
BOOK_NAME AUTHOR TOTAL_COPIES AVAILABLE_COPIES
--------------------------------------------------------------------------------------------------------------
DCD Mano 3 3
OS Silber 2 2
SQL> select * from book_details;
BOOK_ID BOOK_NAME MEM_ID
--------------------------------------------------------------
1 DCD
2 DCD
3 DCD
4 OS
5 OS
>>>>>>>>>>>>>>>>>>>>CREATE A NEW MEMBER<<<<<<<<<<<<<<<<<<<<
declare
nam char(15);
id number(5);
no number(5);
i number(3);
begin
nam:='&nam';
select MAX(mem_id) into no from membership_rec;
if no is not null then
id:=no+1;
else
id:=1;
end if;
insert into membership_rec values(id,nam,null);
dbms_output.put_line('Mr/Mrs/Miss. '||nam||', your membership id is '||id);
end;
/
Enter value for nam: Tintumon
old 7: nam:='&nam';
new 7: nam:='Tintumon';
Mr/Mrs/Miss. Tintumon , your membership id is 1
PL/SQL procedure successfully completed.
SQL> /
Enter value for nam: Kuttoos
old 7: nam:='&nam';
new 7: nam:='Kuttoos';
Mr/Mrs/Miss. Kuttoos , your membership id is 2
PL/SQL procedure successfully completed.
SQL> select * from membership_rec;
MEM_ID MEM_NAME NO_OF_BOOKS_TAKEN
--------------------------------------------------------------------------------------
1 Tintumon
2 Kuttoos
>>>>>>>>>>>>>>>>>>>>ISSUE A BOOK<<<<<<<<<<<<<<<<<<<<
declare
bknam char(15);
mid number(5);
bid number(5);
dat date;
nam char(15);
n number(5);
begin
bknam:='&bknam';
select MIN(book_id) into bid from book_details where book_name=bknam and mem_id is null;
mid:=∣
select mem_name into nam from membership_rec where mem_id=mid;
update book_details set mem_id=mid where book_id=bid;
select no_of_books_taken into n from membership_rec where mem_id=mid;
if n is null then
n:=0;
end if;
update membership_rec set no_of_books_taken=n+1 where mem_id=mid;
update book_rec set available_copies=available_copies-1 where book_name=bknam;
select ADD_months(sysdate,1) into dat from dual;
insert into circulation_rec values(bid,mid,sysdate,dat);
end;
/
Enter value for bknam: DCD
old 8: bknam:='&bknam';
new 8: bknam:='DCD';
Enter value for mid: 2
old 10: mid:=∣
new 10: mid:=2;
PL/SQL procedure successfully completed.
SQL> /
Enter value for bknam: OS
old 8: bknam:='&bknam';
new 8: bknam:='OS';
Enter value for mid: 1
old 10: mid:=∣
new 10: mid:=1;
PL/SQL procedure successfully completed.
SQL>/
Enter value for bknam: DCD
old 9: bknam:='&bknam';
new 9: bknam:='DCD';
Enter value for mid: 1
old 11: mid:=∣
new 11: mid:=1;
PL/SQL procedure successfully completed.
SQL> select * from book_details;
BOOK_ID BOOK_NAME MEM_ID
------------------------------------------------------------
1 DCD 2
2 DCD 1
3 DCD
4 OS 1
5 OS
SQL> select * from book_rec;
BOOK_NAME AUTHOR TOTAL_COPIES AVAILABLE_COPIES
-------------------------------------------------------------------------------------------------------------
DCD Mano 3 1
OS Silber 2 1
SQL> select * from membership_rec;
MEM_ID MEM_NAME NO_OF_BOOKS_TAKEN
------------------------------------------------------------------------------------
1 Tintumon 2
2 Kuttoos 1
SQL> select * from circulation_rec;
BOOK_ID MEM_ID ISSUE_DAT RETURN_DA
------------------------------------------------------------------------------
1 2 08-JAN-10 08-FEB-10
4 1 08-JAN-10 08-FEB-10
2 1 08-JAN-10 08-FEB-10
>>>>>>>>>>>>>>>>>>>>RETURN A BOOK<<<<<<<<<<<<<<<<<<<<
declare
bknam char(15);
id number(5);
bid number(5);
tim number(7,2);
dat date;
begin
bknam:='&bknam';
id:=&id;
select book_id into bid from book_details where book_name=bknam and mem_id=id;
update book_details set mem_id=null where book_id=bid;
update membership_rec set no_of_books_taken=no_of_books_taken-1 where mem_id=id;
update book_rec set available_copies=available_copies+1 where book_name=bknam;
select return_date into dat from circulation_rec where book_id=bid and mem_id=id;
select months_between(sysdate,dat) into tim from dual;
if tim>0 then
dbms_output.put_line('You have to pay fine');
end if;
end;
/
Enter value for bknam: DCD
old 8: bknam:='&bknam';
new 8: bknam:='DCD';
Enter value for id: 1
old 9: id:=&id;
new 9: id:=1;
PL/SQL procedure successfully completed.
SQL> select * from book_rec;
BOOK_NAME AUTHOR TOTAL_COPIES AVAILABLE_COPIES
-------------------------------------------------------------------------------------------------------------
DCD Mano 3 2
OS Silber 2 1
SQL> select * from membership_rec;
MEM_ID MEM_NAME NO_OF_BOOKS_TAKEN
-----------------------------------------------------------------------------------
1 Tintumon 1
2 Kuttoos 1
SQL> select * from book_details;
BOOK_ID BOOK_NAME MEM_ID
--------------------------------------------------------------
1 DCD 2
2 DCD
3 DCD
4 OS 1
5 OS
SQL> select * from circulation_rec;
BOOK_ID MEM_ID ISSUE_DAT RETURN_DA
------------------------------------------------------------------------------
1 2 08-JAN-10 08-FEB-10
4 1 08-JAN-10 08-FEB-10
2 1 08-JAN-10 08-FEB-10