Slip
30
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.
SQL> create table library(lno int primary
key,lname varchar(10),location varchar(10),librarian varchar(10),no_of_books
varchar(10));
SQL> create table book(bid int primary key,bname
varchar(10),author_name varchar(10),price varchar(10),publication
varchar(10),lno int);
SQL> insert into library values(1,'AMJ','pune','ram','100');
SQL> insert into book
values(1,'SWAMI','SM','5000','AM','1');
1)Write
a procedure to display names of book written by “Mr. Patil” and are from “DPU
Library”.
SQL> set
serveroutput on
SQL> create or replace procedure p1(n in varchar)as
cursor c1 is select lname,author_name from library,book where
library.lno=book.lno and author_name='RM' and lname='DPU';
2 c c1 %rowtype;
3 begin
4 open c1;
5
dbms_output.put_line('lname'||''||'author_name');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 if(c.lname=n) then
10
dbms_output.put_line(c.lname||''||c.author_name);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('DPU');
3 end;
4 /
lnameauthor_name
DPURM
2)Write
a trigger which will fire before insert or update on book having price less
than or equal to zero. (Raise user defined exception and give appropriate
message)
SQL> Set
serveroutput on
SQL>
Create or replace trigger t5 before insert or update on book
2 For
each row
3
Begin
4
If(:new.price<=0) then
5
Raise_application_error(-20001,'price>0');
6 End
if;
7 End;
8 /
Trigger
created.
/
