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 procedure which will display all policy details of given client for a given
year.
SQL>
set serveroutput on
SQL>
create or replace procedure p2(n in varchar) as cursor c1 is select cname,
pname, maturityamt, premamt, policyterm, year from client, policyinfo,
clientpolicy where client.cno=clientpolicy.cno and
policyinfo.pno=clientpolicy.pno and cname='harsh';
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('cname'||' '||'pname'||' '||'maturityamt
'||''||'premamt '||''||'policyterm');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.year=n) then
10
dbms_output.put_line(c.cname||' '||c.pname||' '||c.maturityamt||'
'||c.premamt||' '||c.policyterm);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
3
end;
4 /
cname
pname maturityamt premamt policyterm
harsh
jkl 50000 30000 4
PL/SQL
procedure successfully completed.
2)Write
a trigger which restricts insertion of 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 policyinfo
2 for
each row
3
begin
4
if(:new.maturityamt<:new.premamt)then
5
raise_application_error(-20001,'maturity amount should be greater than
premium amount ');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into policyinfo values(6,'stu',20000,25000,5);
insert
into policyinfo values(6,'stu',20000,25000,5)
*
ERROR
at line 1:
ORA-20001:
maturity amount should be greater than premium amount
ORA-06512:
at "SCOTT.T1", line 3
ORA-04088:
error during execution of trigger 'SCOTT.T1'
