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:
1)Write
a function which will return name of person having maximum total amount of
investment.
SQL>
set serveroutput on
SQL>
create or replace function fun1(pn in varchar) return varchar as maxinv
varchar(15);
2
begin
3
select (pname) into maxinv from person1, investment where
person1.pno=investment.pno and investment.pno=(select max(investment.pno) from
investment);
4 if
sql %found then
5
return(maxinv);
6 else
7
return null;
8 end
if;
9 end
;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('maximum no. of invested person is
'||fun1('pname'));
3 end;
4 /
maximum
no. of invested person is shashikant
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display person wise details of investment. (Use
parameterized cursor)
SQL>
set serveroutput on
SQL>
declare
2
cursor c1(pwise person1.pname%type)is select pname, iname, idate, iamt
from person1, investment where person1.pno=investment.pno;
3 c
c1%rowtype;
4
begin
5 open
c1('&pwise');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9 dbms_output.put_line(c.pname||'
'||c.iname||' '||c.idate||' '||c.iamt);
10 end
loop;
11
close c1;
12 end;
13 /
Enter
value for pwise: harsh
old 5: open c1('&pwise');
new 5: open c1('harsh');
harsh
def 14-APR-15 27000
PL/SQL
procedure successfully completed.
