Slip 1
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.
1)Write a function which will return total
maturity amount of policies of a particular client.
SQL> create table
client1(Cno int primary key, Cname varchar(15), Address varchar(20),birthdate
varchar(10));
SQL> create table
poly(pno int primary key,decb varchar(10),mamt int check(mamt>0),pamt int
check(pamt>0),pdate varchar(15));
SQL> create table cp(Cno
int,pno int);
SQL> insert into
client1
values(1,'shree','pune','23jan');
SQL> insert into poly
values(1,'L-term',1000,500,'23jan');
SQL> insert into cp
values(1,1);
SQL> set serveroutput on
SQL> create or replace
function f1(abc in varchar) return number as xyz number;
2
begin
3 select sum(poly.mamt)into xyz from client1
,poly,cp where client1.Cno=cp.Cno and poly.pno=cp.pno and cname='shree';
4 if
sql% found then
5
return(xyz);
6 else
7
return null;
8 end
if;
9 end
f1;
10 /
Function created.
SQL> begin
2
dbms_output.put_line('amt-'||f1('shree'));
3 end;
4 /
amt-1000
2)Write a cursor which will display policy
date wise client details.
SQL> set serveroutput on
SQL> declare
2
cursor c1 is select cname,
address,pdate from client,poly,cp where client.cno=cp.cno and poly.pno=cp.pno order by pdate;
3 c
c1%rowtype;
4
begin
5
open c1;
6
loop
7
fetch c1 into c;
8
exit when c1%notfound;
9
dbms_output.put_line(c.cname||''||c.address||''||c.pdate);
10
end loop;
11
close c1;
12
end;
13 /
shreepune23jan
ommumbai22feb
sairaichur21march
PL/SQL procedure
successfully completed.
