Slip 4 - C) 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. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure which will display all policy details having premium amount less than 5000. 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)

Solution:

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  /