Consider
the following entities and their relationship.
Student
(s_reg_no, s_name, s_class)
Competition
(comp_no, comp_name, comp_type)
Relationship
between Student and Competition is many-to-many with descriptive attribute rank
and year.
Constraints:primary
key, foreign key,primary key for third table(s_reg_no, comp_no, year),s_name
and comp_name should not be null,
comp_type
can be sports or academic.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a function which will accept s_reg_no of student and returns total number of
competition in which student has participated in a given year.
SQL>
set serveroutput on
SQL>
create or replace function fun1(nocomp in varchar) return number as nofcomp
number;
2
begin
3
select count(competition.compno) into nofcomp from student1,
competition, student1competition where student1.sregno=student1competition.sregno
and competition.compno=student1competition.compno and student1.sregno=101;
4 if
sql %found then
5
return(nofcomp);
6
else
7
return null;
8 end
if;
9 end
fun1;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('no of competition-'||fun1(2015));
3 end;
4 /
no
of competition-1
PL/SQL
procedure successfully completed.
Another way
Function created.
SQL> begin
2)Write
a cursor which will display year wise details of competitions held.(Use
parameterized cursor)
Another way
SQL>
set serveroutput on
SQL> create or replace function fun1(nocomp
in number) return number as nofcomp number;
2 begin
3 select count(competition.compno)
into nofcomp from student1, competition, student1competition where
student1.sregno=student1competition.sregno and
competition.compno=student1competition.compno and year='2015';
4 if sql %found then
5 return 1;
6 else
7 return 0;
8 end if;
9 end fun1;
10 /
Function created.
SQL> begin
2 dbms_output.put_line('no of
competition-'||fun1(2015));
3 end;
4 /
SQL>set
serveroutput on
SQL>declare
2
cursor c1(yyyy student1competition.year%type)is select compname,comptype,year
from student1,competition,student1competition where
student1.sregno=student1competition.sregno and
competition.compno=student1competition.compno order by year;
3
c c1%rowtype;
4
begin
5
open c1('&yyyy');
6
loop
7
fetch c1 into c;
8
exit when c1%notfound;
9
dbms_output.put_line(c.compname||' '||c.comptype||' '||c.year);
10
end loop;
11
close c1;
12
end;
13
/
Enter
value for yyyy: 2015
old 5:
open c1('&yyyy');
new 5:
open c1('2015');
Running
Sports 2011
Foot
Ball Sports 2012
Paint
academic 2013
Chess
academic 2014
Quiz
academic 2015
PL/SQL
procedure successfully completed.
