Slip 18 - C) 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. 2) Write a cursor which will display year wise details of competitions. (Use parameterized cursor)

Solution:

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.

2) Write a cursor which will display year wise details of competitions held.(Use parameterized cursor)

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.