Consider the
following entities and their relationship.
Client
(c_no, c_name, c_addr, birth_date)
Policy_info
(p_no, p_name, maturity_amt, prem_amt, policy_term)
Relationship
between Client and Policy_info is many-to-many with descriptive attribute
date_of_purchase.
Constraints:primary
key, foreign key,c_name and p_name should not be null,policy_term should be
greater than zero.
Create a RDB
in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a
function which will return name of policy taken by minimum number of clients.
SQL> set
serveroutput on
SQL>
create or replace function fun1(pn in varchar) return varchar as minp
varchar(15);
2
begin
3
select (pname) into minp from client, policyinfo, clientpolicy where
client.cno=clientpolicy.cno and policyinfo.pno=clientpolicy.pno and
clientpolicy.cno=(select min(clientpolicy.cno) from clientpolicy);
4 if
sql %found then
5
return(minp);
6 else
7
return null;
8 end
if;
9 end
;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('minimum no. of clients policy is'||fun1('pname'));
3 end;
4 /
minimum no.
of clients policy is abc
PL/SQL
procedure successfully completed.
2)Write a
cursor which will display client wise policy details. (Use parameterized
cursor)
SQL> set
serveroutput on
SQL>
declare
2
cursor c1(cwise client.cname%type)is select cname, pname, maturityamt,
premamt, policyterm from
client, policyinfo, clientpolicy where
client.cno=clientpolicy.cno and policyinfo.pno=clientpolicy.
pno;
3 c
c1%rowtype;
4
begin
5 open
c1('&cwise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
dbms_output.put_line(c.cname||' '||c.pname||' '||c.maturityamt||'
'||c.premamt||' '||c.policyte
rm);
10 end
loop;
11
close c1;
12 end;
13 /
Enter value
for cwise: harsh
old 5: open c1('&cwise');
new 5: open c1('harsh');
harsh jkl
50000 30000 4
PL/SQL
procedure successfully completed.
