Slip
16
Consider the following entities and their
relationships.
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many.
Constraint: Primary key, no_of_players should be > 0.
SQL> create table game2(gid int primary key,gname
varchar(20),no_of_players varchar(20),coach_name varchar(20));
SQL> insert into game2 values(001,'football','11','aaa');
SQL> create table player(pid int primary
key,pname varchar(20),addr varchar(20),club_name varchar(20));
SQL> insert into player
values(100,'mmm','pune','pfc');
SQL> create table gp(gid int,pid int);
SQL> insert into gp values(001,100);
1)Write
a procedure which will display games details having number of players more than
5.
SQL> set serveroutput on
SQL> create or replace procedure p3(n in
varchar)as cursor c3 is select gname,coach_name from game2,player,gp where
game2.gid=gp.gid and player.pid=gp.pid and no_of_players > 5;
2 c c3
%rowtype;
3 begin
4 open c3;
5
dbms_output.put_line('gname'||''||'coach_name');
6 Loop
7 Fetch c3
into c;
8 exit when
c3 %notfound;
9
if(c.gname=n)then
10
dbms_output.put_line(c.gname||''||c.coach_name);
11 end if;
12 end loop;
13 close c3;
14 end;
15 /
Procedure created.
begin
p3('football');
end;
4 /
gnamecoach_name
footballaaa
Another
Way
SQL> set serveroutput on
SQL> create or replace procedure p5(n in number)
as sum_no_of_players game2.no_of_players %type;
2 begin
3 select sum(no_of_players) into
sum_no_of_players from game2,player,gp where game2.gid=gp.gid and
player.pid=gp.pid and no_of_players=n;
4 if (sum_no_of_players > 5)then
5 dbms_output.put_line('sum
no_of_players='||sum_no_of_players);
6 else
7 dbms_output.put_line('no_of_players does not
exists');
8 end if;
9 end p5;
10 /
Procedure created.
SQL> execute p5(11);
sum no_of_players=33
Another
method
SQL> set serveroutput on
SQL>
create or replace procedure p3(n in varchar)as
2 cursor c3 is select gname,coach_name from
game2,player,gp where game2.gid=gp.gid and player.pid=gp.pid and
3 no_of_players<=(select sum(no_of_players
) from game2 where gname=n);
5 c c3 %rowtype;
6 begin
7 open c3;
8
dbms_output.put_line('gname'||''||'coach_name');
9 loop
10 fetch c3 into c;
11 exit when c3 %notfound;
12 if(c.gname=n)then
13 dbms_output.put_line(c.gname||''||c.coach_name);
14 end if;
15 end loop;
16 close c3;
17 end;
18 /
Procedure created.
SQL> begin
2 p3('football');
3 end;
4 /
gnamecoach_name
footballaaa
Another
method
SQL> set serveroutput on
SQL> create or replace procedure p4(p_no in out
number) as v_no number;
2 begin
3 select count(no_of_players)into v_no from
game2,player,gp where game2.gid=gp.gid and player.pid=gp.pid and
no_of_players=p_no;
4 p_no:=v_no;
5 exception when no_data_found then p_no:=0;
6 end p4;
7 /
Procedure created.
SQL> declare c_no number;
2 begin
3 c_no:=&c_no;
4 p4(c_no);
5 if(c_no=0)then
6 dbms_output.put_line('dept does not
exist!......');
7 else
8 dbms_output.put_line('no_of_players=' ||
c_no);
9 end if;
10 end;
11 /
Enter value for c_no: 11
old 3:
c_no:=&c_no;
new 3:
c_no:=11;
no_of_players=3
2)Write
a trigger which will fire before insert or update on Game having no_of_players
less than or equal to zero. (Raise user defined exception and give appropriate
message)
SQL> set serveroutput on
SQL> create or replace trigger t5 before insert or update on game2
2 for each row
3 begin
4 if (:new.no_of_players<=0)then
5 raise_application_error (-20001,'no_of_players>0');
6 end if;
7 end;
8 /
Trigger created.
SQL> insert into game2 values(4,'hollyball',-1000,'aaa');
insert into game2 values(4,'hollyball',-1000,'aaa')
*
ERROR at line 1:
ORA-20001: no_of_players>0
ORA-06512: at "SCOTT.T5", line 3
ORA-04088: error during execution of trigger 'SCOTT.T5'
SQL> create or replace trigger t5 before insert or update on game2
2 for each row
3 begin
4 if (:new.no_of_players<=0)then
5 raise_application_error (-20001,'no_of_players>0');
6 end if;
7 end;
8 /
Trigger created.
SQL> insert into game2 values(4,'hollyball',-1000,'aaa');
insert into game2 values(4,'hollyball',-1000,'aaa')
*
ERROR at line 1:
ORA-20001: no_of_players>0
ORA-06512: at "SCOTT.T5", line 3
ORA-04088: error during execution of trigger 'SCOTT.T5'
