Module d’enseignement Ingénierie des Bases de Données Code E.
E ECUEF341
Niveau Licence Génie Logiciel et Système d’Information Classe LGLSI -2
Correction de TP 2 : PL/SQL
Objectif Programmation PL/SQL
Boite à outils logiciels Oracle Express – SQL developer
Elaboré par Nour BRINIS et Sabrine SLAMA
Soit la BD suivante :
JOUEUR(NUJOUEUR, NOM, PRENOM, ANNAISS, NATIONALITE)
RENCONTRE(NUPERDANT, LIEUTOURNOI, ANNEE, NUGAGNANT, SCORE)
GAIN(NUJOUEUR, LIEUTOURNOI, ANNEE, PRIME, NOMSPONSOR)
SPONSOR(NOM, ADRESSE, CHIFFREAFFAIRE)
1. Utiliser une boucle pour accorder la nationalité tunisienne à tous les joueurs du numéro 1 au
numéro 10.
BEGIN
For i in 1..10 Loop
Update JOUEUR set Nationalite= 'Tunisienne' where Nujoueur= i ;
End loop ;
END ;
/
2. Ecrire un script PL/SQL prenant en entrée un choix n et permettant de:
ajouter 10% aux primes de tous les gains effectués à l’année 2020 et sponsorisés par toto,
si n=1.
retirer 10% des primes de tous les gains effectués à l’année 2020 et sponsorisés par toto,
si n=2.
supprimer tous les gains effectués en 2020 et sponsorisés par toto, Si n=4
Afficher un message d’erreur sinon.
set serveroutput on
Accept n NUMBER PROMPT 'Entrez un nombre : ';
BEGIN
Case
When (&n = 1) THEN update gain set prime=prime + prime*0.1 where (Annee=2020) and
(Nomsponsor='TOTO');
When (&n = 2) THEN update gain set prime=prime - prime*0.1 where (Annee=2020) and
(Nomsponsor='TOTO');
When (&n = 4) THEN Delete FROM GAIN where (Annee=2020) and (Nomsponsor='TOTO');
else DBMS_output.put_line('Erreur de choix');
End case;
END;
/
OU
set serveroutput on
Accept n NUMBER PROMPT 'Entrez un nombre :';
BEGIN
if (&n = 1) THEN update gain set prime=prime + prime*0.1 where (Annee=2020) and
(Nomsponsor='TOTO');
elsif (&n = 2) THEN update gain set prime=prime - prime*0.1 where (Annee=2020) and
(Nomsponsor='TOTO');
elsif (&n = 4) THEN Delete FROM GAIN where (Annee=2020) and (Nomsponsor='TOTO');
else DBMS_output.put_line('Erreur de choix');
end if ;
END;
/
3. Ecrire une fonction PL/SQL permettant de supprimer toutes les rencontres qui ont un score 0 et
retourner le nombre des rencontres supprimées.
create or replace function Supp(c varchar) return number IS
RS number;
BEGIN
Delete FROM RENCONTRE where (Score = c);
RS:= SQL%ROWCOUNT ;
/*Le curseur implicite Oracle SQL%ROWCOUNT utilisé en PL/SQL pour connaître le nombre de
lignes impactées par la dernière requête de type DML (Select, Insert, Update, Delete,
Merge).*/
return RS;
dbms_output.put_line ('le nombre des rencontres supprimées est :'|| RS);
END ;
/
La valeur retournée par la fonction doit être assignée à une variable SQL :
Exemple 1 :
SQL> VARIABLE R number;
SQL>EXECUTE: R:= Supp ('0');
SQL>Print R;
Exemple 2 :
4. Ecrire une procédure PL/SQL permettant d’afficher le nom et le prénom des joueurs de
nationalité tunisienne qui on gagné lors d’une rencontre effectuée dans un lieu donné en
paramètre. Tester avec lieu de tournoi = « Roland Garros ».
La table RENCONTRE :
La table JOUEUR :
Create or replace procedure affichage_joueur (L in VARCHAR) as
cursor C_JOUEUR is
select Nom, Prenom
from JOUEUR where nujoueur IN
(Select DISTINCT nugagnant
From rencontre
Where lieutournoi= L) and nationalite= 'Tunisienne';
v_nom joueur.nom%type ;
v_prenom joueur.prenom%type ;
begin
Open C_JOUEUR ;
Loop
FETCH C_JOUEUR INTO v_nom, v_prenom ;
Exit when C_JOUEUR%NOTFOUND ;
dbms_output.put_line ('Nom:' || v_nom || '-' || 'Prénom:' || v_prenom) ;
End loop;
close C_JOUEUR ;
end;
/
execute affichage_joueur('Roland Garros') ;
5. Définir un tableau dynamique Max_Score qui contiendra la valeur maximale des primes obtenues
par chaque joueur en 2020. L’indice du tableau est le NUJOUEUR.
Exemple : Max_prime(5)=1000 signifie que le joueur ayant NUJOUEUR=5 a obtenu une prime
maximale =1000.
Afficher le nombre d’éléments du tableau.
Afficher le premier et le dernier indice du tableau.
A partir du dernier indice du tableau, supprimer tous les éléments du tableau tant que
l’indice est > 4.
Afficher le nombre d’éléments restants du tableau.
La table GAIN :
Utilisation du curseur explicite C pour le traitement d’un ordre SELECT ramenant
plusieurs lignes:
set serveroutput on
Declare
-- collection de type index by
TYPE Max_Score IS TABLE OF number index by binary_integer;
T Max_Score;
i number;
cursor C is
SELECT Nujoueur , MAX(prime)
FROM GAIN
WHERE annee= 2020
GROUP BY nujoueur ;
v_nujoueur gain.nujoueur%type;
v_prime_max gain.prime%type;
Begin
Open C;
Loop
fetch C into v_nujoueur, v_prime_max ;
exit when C%notfound ;
T(v_nujoueur) := v_prime_max ;
dbms_output.put_line( 'T(' || v_nujoueur ||')=' || v_prime_max);
End loop ;
Close C ;
dbms_output.put_line('Le nombre déléments du tableau est:' || T.COUNT);
dbms_output.put_line('Le premier indice du tableau est:' || T.FIRST);
dbms_output.put_line('Le dernier indice du tableau est:' || T.LAST);
i := T.LAST;
While i>4 Loop
T.delete(i);
i := T.PRIOR(i);
End loop ;
dbms_output.put_line('Le nombre d éléments restants du tableau est : ' || T.count);
End;
/
6. Ecrire un script PL/SQL permettant de :
Prendre en entrée un lieu de tournoi et une année (saisies utilisateur)
Fournir en sortie la moyenne des primes obtenues lors du tournoi pour cette année
Afficher un message d’erreur si la moyenne des primes est null.
set serveroutput on
Accept lieu PROMPT 'Entrez le lieu du tournoi';
Accept anee PROMPT 'Entrez annee Du Tournoi';
DECLARE
moyenPrime NUMBER;
BEGIN
SELECT AVG(prime) INTO moyenPrime FROM gain
WHERE( (lieutournoi= '&lieu') AND (annee = &anee) ) ;
IF ( moyenPrime>0) THEN
DBMS_output.put_line('la moyenne des primes obtenues lors du tournoi effectué à &lieu en' ||
&anee || ' est' || moyenPrime );
ELSE
DBMS_OUTPUT.PUT_LINE('&lieu ou'|| &anee || 'est introuvable');
END IF;
END;
7. Ecrire un script PL/SQL permettant de :
prendre en entrée deux année (saisies utilisateur)
fournir en sortie pour chaque joueur la plus forte prime qu'il a touchée entre les deux
années saisies par l'utilisateur
set serveroutput ON
Accept annee1 NUMBER PROMPT 'Entrez lannee de debut';
Accept annee2 NUMBER PROMPT 'Entrez lannee de fin';
Declare
CURSOR MaxPrimeJoueur IS
SELECT nujoueur , MAX(prime)
FROM GAIN
WHERE (annee BETWEEN &annee1 AND &annee2)
GROUP BY nujoueur ;
Nomjoueur VARCHAR2(30);
numj VARCHAR2(30);
primemax number;
Begin
open MaxPrimeJoueur ;
loop
fetch MaxPrimeJoueur into numj, primemax ;
exit when MaxPrimeJoueur %NOTFOUND ;
select Nom into Nomjoueur
from JOUEUR
where Nujoueur=numj;
DBMS_output.put_line('Le prime Maximal pour le joueur '|| Nomjoueur || 'est' || primemax);
end loop ;
close MaxPrimeJoueur;
End;
8. Ecrire le script PL/SQL permettant de :
prendre en entrée le nom du sponsor (saisie utilisateur)
fournir en sortie le nom des joueurs sponsorisés par le sponsor saisi par l'utilisateur
Accept sponsor PROMPT 'Saisir le nom du sponsor: ';
DECLARE
CURSOR Sponsor_Joueur IS
SELECT DISTINCT nujoueur
FROM GAIN
WHERE (nomsponsor='&sponsor');
Nomjoueur VARCHAR2(30);
numj VARCHAR2(30);
Begin
open Sponsor_Joueur ;
DBMS_output.put_line(' Les noms des joueurs sponsorisés par &sponsor : ');
loop
fetch Sponsor_Joueur into numj ;
exit when Sponsor_Joueur%NOTFOUND ;
select Nom into Nomjoueur from joueur where Nujoueur=numj;
DBMS_output.put_line(Nomjoueur );
end loop ;
close Sponsor_Joueur;
End;
