Consider the following entities and their relationships.
Gym (GName, city,
charges, scheme)
Member (ID, MName,
phoneNo, address)
Relation between Gym and
member is one to many. Constraint: Primary Key, charges must be greater than 0.
Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display member details of
gym located at “Pimpri’”
SQL> set serveroutput
on
SQL> create or
replace procedure p1(n in varchar) as cursor c1 is select GName,MName,city from Gym,Member where
Gym.id=Member.id and Gym.city='pimpri';
2 c c1
%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('GName'||''||'MName'||''||'city');
6
loop
7
fetch c1 into c;
8 exit
when c1 %notfound;
9
if(c.pname=n)then
10
dbms_output.put_line(c.GName||''||c.MName||''||C.city);
11 end
if;
12 end
loop;
13
close c1;
14
end;
15 /
2)Write a cursor which will display gym wise
member details.(Use Parametrized Cursor)
SQL> set serveroutput
on
SQL> declare
2
cursor c1(yyyy Gym.GName %type)is
select GName,MName from Gym,Member where
where Gym.id=Member.id order by GName;
3 c
c1%rowtype;
4
begin
5
open c1('&GName ');
6
loop
7
fetch c1 into c;
8
exit when c1%notfound;
9
dbms_output.put_line(c.GName||''||c.MName);
10 end
loop;
11
close c1;
12
end;
13
14 /
