Slip 25 - 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 function which will return total maturity amount of policies of a particular client. 2) Write a cursor which will display policy date wise client details.

Solution:

1)Write a procedure to display member details of gym located at “Pimpri’”

SQL> set serveroutput on

SQL> create or replace procedure p1(n in varchar) as cursor c1 is  select GName,MName,city from Gym,Member where Gym.id=Member.id and Gym.city='pimpri';

  2  c c1 %rowtype;

  3  begin

  4  open c1;

  5  dbms_output.put_line('GName'||''||'MName'||''||'city');

  6   loop

  7   fetch c1 into c;

  8  exit when c1 %notfound;

  9   if(c.pname=n)then

 10  dbms_output.put_line(c.GName||''||c.MName||''||C.city);

 11   end if;

 12   end loop;

 13   close c1;

 14    end;

 15  /

2)Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

SQL> set serveroutput on

SQL> declare

  2    cursor c1(yyyy  Gym.GName %type)is select GName,MName from Gym,Member  where where Gym.id=Member.id order by GName;

  3    c c1%rowtype;

  4    begin

  5    open c1('&GName ');

  6    loop

  7    fetch c1 into c;

  8    exit when c1%notfound;

  9    dbms_output.put_line(c.GName||''||c.MName);

 10   end loop;

 11    close c1;

 12    end;

 13

 14  /