create table Player (player_ID number(10),name varchar(30),nationality varchar(15) , kit_number
number(10) ,Club varchar(20) ,
contract_start_year number(4) , contract_end_year number(4) );
insert into player values(1,'Wayne Rooney','England','10','MU',2016,2020)
exception
declare
kitno exception;
kit number;
playern varchar2(40);
begin
kit:=:kit;
if kit>99 then
raise kitno;
end if;
select name into playern from player where kit_number=kit;
dbms_output.put_line(playern);
exception
when kitno then
dbms_output.put_line('PLease enter the kit between 1-99');
when too_many_rows then
dbms_output.put_line('too many players with the given kit number');
when no_data_found then
dbms_output.put_line('no player found with the given player id');
end;
begin
insert into player values(4,'Gareth Bale','Wales','11','RM',2013,2021);
if sql%found then
dbms_output.put_line('Player Successfully Inserted ');
end if;
end;
declare
a number;
begin
a:=:a;
delete from player where player_id=a;
if sql%found then
dbms_output.put_line('Player Successfully Deleted ');
end if;
end;
declare
a number;
b number;
begin
a:=:no_of_year;
update player set contract_end_year =a+contract_end_year where club ='RM';
b:=sql%rowcount;
dbms_output.put_line(b || ' Players have extended their contract ');
end;
declare
cursor c1 is select name from player where club='RM';
a number;
b varchar2(30);
begin
open c1;
loop
fetch c1 into b;
exit when c1%notfound;
dbms_output.put_line(b);
end loop;
a:=c1%rowcount;
close c1;
dbms_output.put_line('Total ' || a || 'players in RM');
end;
declare
cursor c1 is select * from player where club='RM';
a number;
begin
for i in c1 loop
dbms_output.put_line(i.name || ' Plays For ' || i.nationality || ' AND ' || i.club);
a:=c1%rowcount;
end loop;
dbms_output.put_line('Total ' || a || 'players in RM');
end;
declare
cursor c1(d number) is select name from player where kit_number=d;
a number;
b player.name%type;
begin
open c1(:d);
loop
fetch c1 into b;
exit when c1%notfound;
dbms_output.put_line(b);
end loop;
a:=c1%rowcount;
close c1;
dbms_output.put_line('Total ' || a || 'players');
end;
create or replace procedure RemovePlayer(playerid number) as
begin
delete from player where player_id =playerid;
end;
create or replace procedure AddContract (a number,b number) is
begin
update player set contract_end_year=contract_end_year+a where player_id=b;
end;
create or replace procedure AddP (a player.player_id%type,b player.name%type,c
player.nationality%type,d player.kit_number%type,e player.club%type,f
player.contract_start_year%type,g player.contract_end_year%type) is
begin
insert into player values(a,b,c,d,e,f,g);
end;
begin
AddP(5,'Marcelo','Brazil',14,'RM',2007,2021);
end;
create or replace function ContractYears(a number) return number is
b number;
begin
select contract_end_year-contract_start_year into b from player where player_id=a;
return b;
dbms_output.put_line(b || 'years ');
end;
declare
a number;
begin
a:=ContractYears(1);
dbms_output.put_line(a);
end;
create or replace function CountPlayers(a player.club%type) return integer is b number;
begin
select count(*) into b from player where club=a;
dbms_output.put_line(b);
return b;
end;
declare
a number;
begin
a:=CountPlayers('RM');
end;
create or replace trigger kit_update
after update of kit_number on player
for each row
begin
dbms_output.put_line(:old.kit_number || 'changed to ' || :new.kit_number);
end;
update player set kit_number=11 where player_id=1;
CREATE OR REPLACE TRIGGER PRIMARY_KEY
BEFORE INSERT ON player
FOR EACH ROW
DECLARE
i player.player_id%TYPE;
BEGIN
IF (:NEW.player_id IS NULL) THEN
RAISE_APPLICATION_ERROR(-20002, 'PRIMARY KEY VIOLATION
BECAUSE IT CANNOT BE NULL');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
CREATE OR REPLACE TRIGGER Con_CHECK
BEFORE INSERT OR UPDATE ON player FOR EACH ROW
BEGIN
IF :NEW.contract_start_year>:NEW.contract_end_year THEN
RAISE_APPLICATION_ERROR(-20000,'RECORD IS ILLEGAL');
END IF;
END;
insert into player values (15,'Hazard','Belgium',10,'CHE',2017,2013);
CREATE OR REPLACE TRIGGER kit_CHECK
BEFORE INSERT OR UPDATE ON player FOR EACH ROW
BEGIN
IF :NEW.kit_number>99 or :NEW.kit_number <1 THEN
RAISE_APPLICATION_ERROR(-20001,'Please Enter A valid kit number');
END IF;
END;
insert into player values (15,'Hazard','Belgium',101,'CHE',2017,2021);
