Correction TP6- PL/SQL
CREATE SEQUENCE new_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE
/
create or replace trigger auto_increment
before insert on Joueur
for each row
begin
if (:new.NuJoueur is null) then
select new_seq.nextval into :new.NuJoueur from dual;
end if;
end;
/
EXERCICE 1 : LES TRIGGERS
-- 1. Que fait le trigger défini dans le script BaseTENNIS-Trigger.sql?
1. ce trigger vérifie si le champ NuJoueur que l'on souhaite insérer dans
de la table Joueur est null (NuJoueur est l'identifiant de la table, c'est un champs obligatoire)
2. Si NuJoueur est NULL, il attribue la prochaine valeur de la séquence 'new_seq'à l'attribut NuJoueur.
3. Ce trigger se déclenche avant l'insertion d'un tuple dans le table Joueur.
-- 2.
-- Ce trigger transforme le champ Nom dans la table Joueur en majuscule. Il se déclenche avant
l'insertion d'un tuple dans la table Joueur.
Drop trigger trig1;
CREATE OR REPLACE TRIGGER trig1
BEFORE insert ON Joueur
FOR EACH ROW
BEGIN
Badri Sabrine LFIG
:new.Nom := UPPER(:new.Nom);
END;
/
-- 3. testez le trigger
-- Ajustement de la largeur de la colonne 'temps_realise'
Advertisement
col 'Nom' for a14;
col 'Prenom' for a14;
col 'Nationalite' for a10;
insert into joueur (Nom, Prenom, Annais, Nationalite)
Values ('gasket', 'Richard', 1986, 'France');
-- 4. Modifier le trigger précédent pour pouvoir gérer les mises à jour !
create or replace trigger trig1
before insert or update on joueur
for each row
begin
:new.Nom := upper(:new.Nom);
end;
/
-- 5. testez le trigger
update joueur set nom='Gasquet' where Nom='GASKET';
-- 6.
-- Ce trigger se déclenche après l'insertion d'un tuple dans la table Gain, et transforme la valeur de la
prime en euro si la date du tournoi est antérieure à 2001
Drop trigger trig2;
create or replace trigger trig2
before insert on Gain
for each row
begin
if (:new.annee < 2001) then
:new.prime := :new.prime * 0.152;
end if;
end;
/
Badri Sabrine LFIG
-- testez le trigger
insert into gain values (1, 'Roland Garros', 2001, 100, 'Nike');
insert into gain values (2, 'Roland Garros', 2000, 100, 'Nike');
-- Supprimez les lignes précedemment ajoutées
delete from gain where prime=100;
delete from gain where prime=100*0.152;
Exercice 2 : PL/SQL : Requêtes à résultat unique
Advertisement
-- 1. Complétez le script suivant - moyennePrime.sql
ACCEPT V_lieutournoi PROMPT "Quel lieu de tournoi: "
ACCEPT V_annee NUMBER PROMPT "Quelle annee: "
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V_moyenne GAIN.prime%TYPE;
FIN exception;
BEGIN
-- Requête de calcul de la moyenne des primes
select avg(GAIN.prime) into V_moyenne from gain
where (GAIN.lieutournoi = '&V_lieutournoi' and GAIN.annee = &V_annee);
--fin requête
if V_moyenne is null then raise FIN;
end if;
dbms_output.put_line('&V_lieutournoi'||' '||to_char(&V_annee)||': '||to_char(V_moyenne));
EXCEPTION
when FIN then dbms_output.put_line('Tournoi non repertorie');
END;
/
-- 2. tester le script moyennePrime.sql
start moyennePrime.sql
-- 3. Ecrire un script moyennePrime2.sql
ACCEPT V_lieutournoi PROMPT "Quel lieu de tournoi: "
ACCEPT V_annee NUMBER PROMPT "Quelle annee: "
Badri Sabrine LFIG
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V_moyenne GAIN.prime%TYPE;
BEGIN
select avg(GAIN.prime) into V_moyenne from gain
where (GAIN.lieutournoi = '&V_lieutournoi' and GAIN.annee = &V_annee);
if V_moyenne is null then
dbms_output.put_line('Tournoi non repertorie');
else
Advertisement
dbms_output.put_line('&V_lieutournoi'||' '||to_char(&V_annee)||':
'||to_char(V_moyenne));
end if;
END;
/
-- 4. tester le script moyennePrime2.sql
start moyennePrime2.sql
EXERCICE 3 : PL/SQL : REQUETES A RESULTAT MULTIPLE, UTILISATION DES
CURSEURS
-- 1.
-- script primeJoueur.sql
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT V_Annee1 NUMBER PROMPT 'Annee de depart: '
ACCEPT V_Annee2 NUMBER PROMPT 'Derniere annee: '
DECLARE
V_N varchar(20);
V_M Gain.Prime%type;
cursor C_M is
select nom, max(prime)
from GAIN, JOUEUR
where annee between &V_Annee1 and &V_Annee2
and gain.NuJoueur=joueur.NuJoueur
Badri Sabrine LFIG
group by nom;
BEGIN
open C_M;
loop
fetch C_M into V_N, V_M;
exit when C_M%NOTFOUND;
if C_M%ROWCOUNT=1
then dbms_output.put_line('Le resultat est :');
end if;
dbms_output.put_line(V_N ||' '||to_char(V_M) );
end loop;
if C_M%ROWCOUNT=0
Advertisement
then dbms_output.put_line('Aucun tournoi n''est répertorie entre ces dates');
end if;
close C_M;
END;
/
-- 2. tester le script primeJoueur.sql
start primeJoueur.sql
-- 3. écrire le script joueurSponsor.sql
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT V_sponsor PROMPT "Donnez le nom du sponsor: "
DECLARE
V_nom Joueur.Nom%type;
cursor C_sponsor is
select joueur.nom from joueur, gain
where gain.nujoueur = joueur.nujoueur and gain.nomsponsor = '&V_sponsor';
BEGIN
open C_sponsor;
loop
fetch C_sponsor into V_nom;
Badri Sabrine LFIG
exit when C_sponsor%NOTFOUND;
if C_sponsor%ROWCOUNT=1 then
dbms_output.put_line('Les joueurs sponsories par &V_sponsor :');
end if;
dbms_output.put_line(V_nom);
end loop;
if C_sponsor%ROWCOUNT=0
then dbms_output.put_line('&V_sponsor est Sponsor inconnu');
end if;
close C_sponsor;
END;
/
Badri Sabrine LFIG