Consider
the following entities and their relationship.
Person
(p_no, p_name, p_addr)
Investment
(inv_no, inv_name, inv_date, inv_amt)
Relationship
between Person and Investment is one-to-many.
Constraints:
primary key, foreign key,p_name and inv_name should not be null,inv_amt should
be greater than 10000.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
create table person1(pno int primary key, pname
varchar(15) not null, paddr varchar(15));
insert
into person1 values(101,'lokesh','daund');
create table investment(ino int primary key, iname
varchar(15) not null, idate date, iamt int, pno int);
insert
into investment values(1,'abc','14 apr 2015',14000,101);
1)Write
a procedure which will display details of person, made investment on given
date.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select pname,
paddr, idate from person1, investment where person1.pno=investment.pno;
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('pname'||' '||'paddr');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.idate=n) then
10
dbms_output.put_line(c.pname||' '||c.paddr);
11 end
if;
12 end
loop;
13 end;
14 /
Procedure
created.
SQL>
begin
2
p1('14/4/2015');
3 end;
4 /
pname
paddr
lokesh
daund
harsh
delhi
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion or updation of investment having inv_date
greater than current date. (Raise user defined exception and give appropriate
message)
SQL>
set serveroutput on
SQL>
create or replace trigger t1 before insert or update on investment
2 for
each row
3
declare
4 d1
varchar(15);
5 d2
varchar(15);
6
begin
7
d1:=to_char(:new.idate,'dd-mm-yyyy');
8
d2:=to_char(sysdate,'dd-mm-yyyy');
9
if(d1>d2) then
10
raise_application_error(-20001,'investment date should be less than
current date.');
11 end
if;
12 end;
13 /
Trigger
created.
SQL>
insert into investment values(6,'xyz','26 nov 2016',27000,102);
insert
into investment values(6,'xyz','26 nov 2016',27000,102)
*
ERROR
at line 1:
ORA-20001:
investment date should be less than current date.
ORA-06512:
at "SCOTT.T1", line 8
ORA-04088:
error during execution of trigger 'SCOTT.T1'
