Kindly Note that Ignore Issues at the time of
program execution. All programs are reference only.
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 procedure which will accept year and type of competition as an input and
display details of competition accordingly.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select compname,
comptype, year from student1, competition, student1competition where
student1.sregno=student1competition.sregno and
competition.compno=student1competition.compno
and comptype='academic';
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('compname'||' '||'comptype');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.year=n) then
10
dbms_output.put_line(c.compname||' '||c.comptype);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p1(2013);
3 end;
4 /
compname
comptype
Paint
academic
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion of rank value greater than 3. (Raise user
defined exception and give appropriate message)
SQL>
set serveroutput on
SQL>
create or replace trigger t2 before insert or update on student1competition
2 for
each row
3
begin
4
if(:new.rank>3) then
5
raise_application_error(-20001,'rank should be 1, 2 or 3.');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into student1competition values(106,6,4,2015);
insert
into student1competition values(106,6,4,2015)
*
ERROR
at line 1:
ORA-20001:
rank should be 1, 2 or 3.
ORA-06512:
at "SCOTT.T2", line 3
ORA-04088:
error during execution of trigger 'SCOTT.T2'
