Slip 4
Consider the following entities and their
relationships.
Client(client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt,
prem_amt, date)
Relation between Client and Policy_info is Many to
Many
Constraint: Primary key, prem_amt and maturity_amt
should be > 0.
SQL> create table client(cno int primary
key,cname varchar(10),addr varchar(15),bdate varchar(15));
SQL> insert into client
values(1,'ajay','pune','01-04-200');
SQL> create table policy(pno int primary key,disc
varchar(10),mamt int,pamt int,pdate varchar(15));
SQL> insert into policy
values(1,'life','10000','2000','10-5-2003');
SQL> create table cp(cno int,pno int);
SQL> insert into cp values(1,1);
1)Write
a procedure which will display all policy details having premium amount less
than 5000.
SQL> set serveroutput on
SQL> create or replace procedure p4(n in
varchar)as cursor c4 is select pamt,pdate from client,polic
y,cp where client.cno=cp.cno and policy.pno=cp.pno
and pamt< 5000;
2 c c4 %rowtype;
3 begin
4 open c4;
5 dbms_output.put_line('pamt'||''||'pdate');
6 loop
7 fetch c4 into c;
8 exit
when c4 %notfound;
9 if(c.pamt=n) then
10 dbms_output.put_line(c.pamt||''||c.pdate);
11 end if;
12 end loop;
13 close c4;
14 end;
15 /
Procedure created.
SQL> begin
2 p4('2000');
3 end;
4 /
pamtpdate
200010-5-2003
2)Write
a trigger which will fire before insert or update on policy_info having
maturity amount less than premium amount. (Raise user defined exception and
give appropriate message)
SQL> set serveroutput
on
SQL> create or
replace trigger t1 before insert or update on cp
2 for
each row
3 begin
4 if(:new.mamt<:new.pamt)then
5 raise_application_error(-20001,'mamt>pamt');
6 end
if;
7 end;
8 /
