RDBMS Slip No.5

Consider the following entities and their relationships.  
Library(Lno, Lname, Location, Librarian, no_of_books) 
 Book(Bid, Bname, Author_Name, Price, publication) 
Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1) Write a function which will accept publication name from user and display total price of books of that publication. 
2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)

Consider the following entities and their relationships.  
Library(Lno, Lname, Location, Librarian, no_of_books) 
 Book(Bid, Bname, Author_Name, Price, publication) 
Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Answer : 

create table library11(Lno integer primary key not null, Lname varchar2(40), Location varchar2(40), Librarian varchar2(40), no_of_books integer);
insert into library11 values(1,'bbk','pune','amit',100)
insert into library11 values(2,'book know','satara','amol',120)
insert into library11 values(3,'about you','sangali','ramesh',200)

create table book(Bid integer primary key not null, Bname varchar2(40), Author_Name varchar2(40), Price integer not null, publication varchar2(40), Lno1 integer references library11(Lno));
insert into book values(101,'wsk','Dr.j.k.s',200,'sk publication',1)
insert into book values(102,'wk','Dr.s.s.s',500,'pk publication',2)
insert into book values(103,'ok','Dr.k.k',450,'nw publication',3)

1) Write a function which will accept publication name from user and display total price of books of that publication. 

Answer:

create or replace function f2(publication1 in varchar)return number
is
str1 number;
begin
select Price into str1 from book where publication='nw publication';
return str1;
end f2;

select f2('nw publication') from dual;

declare
n2 varchar2(20);
n3 number;
begin
n2:=:n2;
n3:=f2(n2);
dbms_output.put_line('total price of books of that publication'||n3);
end;

2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)

Answer :

declare
cursor book_data(Lno2 dept.dno%type) is select Bid,Bname,Author_Name,Price,Publication from library11,book where library11.Lno=book.Lno1 and Lno=Lno;
Lno3 book.Bid%type;
Bid1 book.Bid%type;
Bname1 book.Bname%type;
Author_Name1 book.Author_Name%type;
Price1 book.Price%type;
Publication1 book.Publication%type;

begin
Lno3:=:Lno3;
open book_data(Lno3);
loop
fetch book_data into Bid1,Bname1,Author_Name1,Price1,Publication1;
dbms_output.put_line('the d no is'||Bid1);
dbms_output.put_line('the d name is'||Bname1);
dbms_output.put_line('the d loc is'||Author_Name1);
dbms_output.put_line('the d loc is'||Price1);
dbms_output.put_line('the d loc is'||Publication1);
exit when book_data %notfound;
end loop;
close book_data;
end;