Consider
the following entities and their relationship.
Company
(c_no, c_name, c_city, c_share_value)
Person
(p_no, p_name, p_city, p_ph_no)
Relationship
between Company and Person is many-to-many with descriptive attribute
no_of_shares.
Constraints:primary
key, foreign key,c_name and p_name should not be null,no_of_shares 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 person having maximum number of shares of
given company.
SQL>
set serveroutput on
SQL>
create or replace function fun1(pn in varchar) return varchar as maxshr
varchar(15);
2 begin
3
select (pname) into maxshr from company, person, companyperson where
company.cno=companyperson.cno and person.pno=companyperson.pno and
noofshares=(select max(noofshares) from person, companyperson where
person.pno=companyperson.pno) group by pname;
4 if
sql %found then
5
return(maxshr);
6 else
7
return null;
8 end
if;
9 end
;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('max shares-'||fun1('syspro'));
3 end;
4 /
max
shares-harsh
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display person wise share details. (Use parameterized
cursor)
SQL>
set serveroutput on
SQL>
declare
2
cursor c1(pwise person.pname%type)is select pname,sharevalue,noofshares
from company, person, companyperson where company.cno=companyperson.cno and
person.pno=companyperson.pno order by pname;
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.sharevalue||' '||c.noofshares);
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
652656 27
PL/SQL
procedure successfully completed.
