Slip 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:
SQL> create table library(l_no int primary
key,l_name varchar(20),location varchar(20),librarian varchar(20),no_of_book
int);
SQL> create table books(b_id int primary
key,b_name varchar(20),author_name varchar(20),price int,publication
varchar(20),l_no int);
SQL> insert into library
values(1,'francis','pune','riya','10');
SQL> insert into books
values(1,'wonderland','alice','500','nirali',1);
1)Write a function which will accept publication name from user
and display total price of books of that publication.
SQL> set serveroutput on;
SQL> create or replace function fun1(pn in varchar)
return number as pm number;
2 begin
3 select sum(books.price) into pm from
library,books where library.l_no=books.l_no and publication=’vision’;
4 if sql %found then
5 return(pm);
6 else
7 return null;
8 end if;
9 end;
10 /
Function created.
SQL> begin
2
dbms_output.put_line('price-'||fun1('vision'));
3 end;
4 /
2)Write a cursor which will display library wise book details.(Use
Parameterized Cursor)
SQL> set serveroutput
on
SQL> declare
2 cursor
c1(yyyy Library.l_name %type)is select l_name,b_name from
Library,Book where Library.l_no= Book.l_no order by l_name;
3 c
c1%rowtype;
4 begin
5 open
c1('&l_name ');
6 loop
7 fetch
c1 into c;
8 exit
when c1%notfound;
9 dbms_output.put_line(c.l_name||''||c.b_name);
10 end
loop;
11 close
c1;
12 end;
13
14 /
