Slip 5 - C) 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)

Solution:

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  /