ESC Tunis / ISAMM 2e LBDAD

Chapitre 3 : Les curseurs

I. Référencement de types existants en PL/SQL

I.1) Notion de %TYPE

Référence un type existant qui est soit un type défini précédemment, soit une colonne d’une table.

I.1.A)Notation
nom_variable nom_variable_ref%TYPE ;
nom_variable nom_table.nom_colonne%TYPE ;

I.1.B)Exemples
Crédit REAL ;

Montant Crédit%TYPE ;

→ Montant et crédit ont le même type de données.

NumEtud Etudiant.NumE%TYPE ;

→ NumEtud a le même type de données que la colonne NumE de la table Etudiant.

I.2) Notion de %ROWTYPE

Référence une ligne d’une table

I.2.A)Notation
nom_variable nom_table%ROWTYPE ;

I.2.B)Exemple
UnClient Client%ROWTYPE ;

→ La variable UnClient a la même composition qu’une ligne de la table Client.

Ingénierie des bases de données 34

ESC Tunis / ISAMM 2e LBDAD

I.3) Le type RECORD

Le type enregistrement (Record) désigne l’ensemble des données logiquement liées et stockées dans des
champs.

I.3.A)Déclaration d’un type enregistrement

Exemple :
TYPE ETUDIANT IS RECORD

Numéro INTEGER,

Nom VARCHAR2 (20),

Age INTEGER

);

I.3.B)Déclaration d’une variable enregistrement

E1 ETUDIANT ;

II. Les curseurs

C’est une structure de données permettant de stocker le résultat d’une requête qui retourne plusieurs
tuples.
PL/SQL utilise des curseurs pour tous les accès à des informations de la base de données. Le langage
supporte à la fois l’emploi de curseurs implicites et explicites.
Les curseurs implicites sont ceux qui sont établis lorsqu’un curseur explicite n’a pas été déclaré. Il faut
utiliser des curseurs explicites ou des curseurs de boucles FOR dans toutes les requêtes qui renvoient
plusieurs lignes.

II.1) Déclaration d’un curseur

Les curseurs sont définis dans la zone des variables de sous-programmes PL/SQL en utilisant l’instruction
CURSOR NomCurseur IS suivie d’une requête SQL valide.

Ingénierie des bases de données 35

ESC Tunis / ISAMM 2e LBDAD

Exemple :
CURSOR calcul IS

SELECT numprod, prixuni

FROM produit ;

L’instruction SQL peut être n’importe quelle requête valide. Après son initialisation, les actions d’un
curseur peuvent être contrôlées avec les instructions OPEN, FETCH et CLOSE ou en utilisant une boucle
FOR.
NB :
Un tuple du curseur précédent sera de type calcul%ROWTYPE.

II.2) Opérations sur un curseur

II.2.A) Ouverture d’un curseur
OPEN NomCurseur ;

II.2.B) Gestion automatique d’un curseur

Exemple :
FOR tuple IN calcul LOOP

var1:=tuple.numprod ;

var2:=tuple.prixuni ;

END LOOP ;

II.2.C) Gestion manuelle d’un curseur

Exemple :
LOOP

FETCH calcul INTO tuple ;

...

Ingénierie des bases de données 36

ESC Tunis / ISAMM 2e LBDAD

EXIT WHEN calcul%NOTFOUND ;

END LOOP ;

II.2.D) Fermeture d’un curseur

CLOSE NomCurseur ;

II.3) Contrôle d’un curseur

Afin d’utiliser un curseur pour manipuler des données, il faut utiliser l’instruction OPEN pour exécuter la
requête et identifier toutes les lignes qui satisfont le critère de sélection. Les extractions ultérieures de
lignes sont réalisées avec l’instruction FETCH.
Notons que FETCH permet de se positionner sur la ligne suivante et de charger les données de
l’enregistrement courant dans une ou plusieurs variables.
Lorsque toutes les données sont traitées, l’instruction CLOSE clôt toute activité associée avec le curseur
ouvert.
Exemple :
Soit la table EMP(idemp, Nom, Age, Poste, Salaire, Anciennnete)
Le code suivant permet d’ouvrir le curseur CursEmp et de traiter les lignes extraites. Après l’extraction et
le traitement de toutes les informations, le curseur est fermé.
Version 1 :
DECLARE

CURSOR CursEmp IS

SELECT *

FROM Emp

Where poste = 'Directeur' ;

BEGIN

FOR cur IN CursEmp LOOP

DBMS_OUTPUT.PUT_LINE (cur.Nom || ' - ' || cur.Age);

Ingénierie des bases de données 37

ESC Tunis / ISAMM 2e LBDAD

END LOOP ;

CLOSE CursEmp ;

END ;

Version 2 :
DECLARE

CURSOR CursEmp IS

SELECT Nom, Age, Salaire

FROM Emp

Where poste = 'Directeur' ;

var1 Emp.Nom%TYPE;

var2 Emp.Age%TYPE;

BEGIN

OPEN CursEmp;

LOOP

FETCH CursEmp INTO var1, var2;

DBMS_OUTPUT.PUT_LINE (var1 || ' - ' || var2);

EXIT WHEN info%NOTFOUND;

END LOOP;

CLOSE CursEmp;

END;

Ingénierie des bases de données 38

ESC Tunis / ISAMM 2e LBDAD

II.4) Attributs des curseurs explicites

Il y a quatre attributs associés aux curseurs PL/SQL :
 %NOTFOUND
 %FOUND
 %ROWCOUNT
 %ISOPEN
Tous les attributs de curseur s’évaluent à TRUE, FALSE ou NULL, en fonction de la situation.

II.4.A) L’attribut %NOTFOUND

L’attribut %NOTFOUND s’évalue à FALSE quand une ligne est extraite, TRUE si le dernier FETCH n’a
pas renvoyé une valeur et NULL si le curseur SELECT n’a pas renvoyé de données.

II.4.B) L’attribut %FOUND

L’attribut %FOUND est l’opposé logique de %NOTFOUND par rapport à TRUE et FALSE, mais s’évalue
néanmoins à NULL si le curseur ne renvoie pas de données.

II.4.C) L’attribut %ROWCOUNT

%ROWCOUNT est utilisé pour connaître le nombre de lignes contenues dans un curseur.

II.4.D) L’attribut %ISOPEN

%ISOPEN, est évalué soit TRUE ou bien FALSE, suivant que le curseur associé est ouvert ou non. Avant
que le curseur ne soit ouvert et après qu’il soit fermé, %ISOPEN vaut FALSE. Dans les autres cas, cet
attribut s’évalue à TRUE.

II.5) Paramètres des curseurs

On peut spécifier des paramètres pour les curseurs de la même manière que pour des sous-programmes.

Ingénierie des bases de données 39

ESC Tunis / ISAMM 2e LBDAD

L’exemple suivant illustre la syntaxe de déclaration de curseurs avec des paramètres :

DECLARE

CURSOR C_Emp (rôle IN emp.Poste%TYPE) IS

SELECT *

FROM Emp

WHERE poste = 'Secrétaire' ;

BEGIN

FOR compteur IN C_Emp ('secrétaire')

LOOP

DBMS_OUTPUT.PUT_LINE (TO_CHAR (compteur.NumEmp) || '-' ||

compteur.NomEmp) ;

END LOOP ;

END ;

III. Applications

III.1) Activité 1
III.1.A) Énoncé
Déclarer un type nommé Personne basé sur deux types nommés respectivement IdPers et Adresse.

III.1.B) Correction
TYPE Personne IS RECORD

Ingénierie des bases de données 40

ESC Tunis / ISAMM 2e LBDAD

IdPers VARCHAR2 (40),

Adresse VARCHAR2 (60)

) ;

P1 Personne ; -- Variable de type Personne

III.2) Activité 2
III.2.A) Énoncé
Soit la base de données Aéroport composée de tables suivants :
 Avion (NumAv, NomAV, Capacité, Localisation)
 Pilote (NumPilote, NomPilote, Ville, Salaire)
 Vol (NumVol, #NumAv, #NumPilote, VilleDep, VilleArr, HeurDep, HeurArr)
On désire réduire de 12 % le temps de vol des avions 1 et 4. Pour les avions 2 et 8, la réduction du temps
de vol sera de 15 %.
On doit donc mettre à jour les nouveaux temps d’arrivée pour les avions concernés.
Écrire un bloc PL/SQL en utilisant un curseur permettant de lire les données suivantes : le numéro du vol,
le numéro de l’avion, l’heure de départ ainsi que celui d’arrivée pour les avions concernés par cette mise à
jour.
Pour chaque vol lu par le curseur, on va calculer le temps de vol et le réduire selon le pourcentage requis
afin de mettre à jour le champ HeurArr.

III.2.B) Correction
III.2.B.a) Version 1
DECLARE

CURSOR AvModif IS

SELECT NumVol, NumAv, HeurDep, HeurArr

FROM Vol

WHERE NumAv IN (1, 2, 4, 8) ;

Ingénierie des bases de données 41

ESC Tunis / ISAMM 2e LBDAD

VolModif AvModif%ROWTYPE ;

tvol REAL ;

BEGIN

FOR VolModif IN AvModif LOOP

tvol := VolModif.HeurArr – VolModif.HeurDep ;

IF (VolModif.NumAv IN (1,4)) THEN

tvol := tvol * 0.88 ;

ELSE

tvol := tvol * 0.85 ;

END IF ;

UPDATE Vol

SET HeurArr = VolModif.HeurDep + tvol ;

WHERE NumVol = VolModif.NumVol ;

END LOOP ;

END ;

III.2.B.b) Version 2
DECLARE

CURSOR AvModif IS

SELECT NumVol, NumAv, HeurDep, HeurArr

FROM Vol

Ingénierie des bases de données 42

ESC Tunis / ISAMM 2e LBDAD

WHERE NumAv IN (1, 2, 4, 8) ;

VolModif AvModif%ROWTYPE ;

tvol REAL ;

Ha AvModif.HeurArr%type ;

Hd AvModif.HeurDep%type ;

nv AvModif.NumVol%type ;

Na AvModif.NumAv%type ;

BEGIN

OPEN AvModif;

LOOP

FETCH AvModif INTO nv, na, hd, ha;

tvol = ha – hd ;

IF (na IN (1,4)) THEN

tvol := tvol * 0.88 ;

ELSE

tvol := tvol * 0.85 ;

END IF ;

UPDATE Vol

SET HeurArr = hd + tvol ;

WHERE NumVol = nv ;

EXIT WHEN AvModif%NOTFOUND;

Ingénierie des bases de données 43

ESC Tunis / ISAMM 2e LBDAD

END LOOP ;

close AvModif ;

END ;

III.3) Activité 3
III.3.A) Énoncé
On souhaite supprimer les valeurs en double (doublons) d’une colonne nommée « db1 » présente dans la
table Test.
1) Écrire un bloc PL/SQL permettant d’afficher les valeurs présentes au moins en double puis les
supprimer
2) Peut-on ajouter une contrainte de clé primaire sur cette colonne à partir du bloc PL/SQL ?

III.3.B) Correction
1)
DECLARE

CURSOR Doublons IS

SELECT db1

FROM Test

GROUP BY db1

HAVING CONUT (*) > 1 ;

n Doublons%ROWTYPE ;

BEGIN

DBMS_OUTPUT.PUT_LINE (''Affichage des doublons'') ;

Ingénierie des bases de données 44

ESC Tunis / ISAMM 2e LBDAD

FOR n IN Doublons LOOP

DBMS_OUTPUT.PUT_LINE (n.db1) ;

DELETE FROM Test

WHERE n.db1 = Test.db1 ;

INSERT INTO Test

VALUES (n.db1) ;

END LOOP ;

2)
ALTER TABLE Test

ADD CONSTRAINT PK PRIMARY KEY (db1) ;

END ;

III.4) Activité 4
III.4.A) Énoncé
Soit la base de données suivante :
 DEP (NumDep, NomDep, Emplacement)
 Emp (NumEmp, NomEmp, Poste, Salaire, Commission, DateRecrut, #NumDep)
1) Écrire un bloc PL/SQL pour attribuer à l’employé numéro 140 une commission comme suit :
 Si l’employé occupe le poste de vendeur, sa commission est de 100 dinars ;
 Si l’employé occupe un autre poste alors il n’aura pas de commissions ;

Ingénierie des bases de données 45

ESC Tunis / ISAMM 2e LBDAD

 Si l’employé n’existe pas alors on affichera un message d’erreur.

2) Écrire un bloc PL/SQL pour afficher les n salaires les plus élevées de la table Emp.
3) Écrire un bloc PL/SQL pour retourner au plus 15 employés du département 8 ayant un salaire
supérieur à 1000 dinars.
4) Utiliser deux curseurs dont au moins un est paramétré pour trouver les n salaires les plus élevées
en tenant compte des doublons
5) Reprendre la question 2 de cet exercice pour traiter l'erreur Oracle NO_DATA_FOUND
(Renommer l’erreur et afficher un message approprié. Rappelons que le code de cette erreur est
+100)
III.4.B) Correction
1)
DECLARE

Num Emp.NumEmp%TYPE ;

BEGIN

SELECT NumEmp INTO Num

FROM Emp

WHERE NumEmp = 140 ;

IF Num ISNULL THEN

DBMS_OUTPUT.PUT_LINE (''L'employé n'existe pas!'') ;

ELSEIF Num = 140 THEN

UPDATE Emp

SET Commission = 100

WHERE NumEmp = Num AND Poste = ‘Vendeur’ ;

ELSE

UPDATE Emp

Ingénierie des bases de données 46

ESC Tunis / ISAMM 2e LBDAD

SET Commission = 0

WHERE NumEmp = Num AND Poste <> ‘Vendeur’ ;

END IF ;

COMMIT ;

END ;

2)
ACCEPT n PROMPT ''Donner n'' ;

DECLARE

CURSOR C1 IS

SELECT NomEmp, Salaire

FROM Emp

ORDER BY Salaire DESC ;

Nom Emp.NomEmp%TYPE ;

Sal Emp.Salaire%TYPE ;

i Number ;

BEGIN

OPEN C1

FOR i IN 1.. &n LOOP // & connaître n

FETCH C1 INTO Nom, Sal ;

Ingénierie des bases de données 47

ESC Tunis / ISAMM 2e LBDAD

INSERT INTO Res

VALUES (Nom, Sal) ;

END LOOP ;

CLOSE C1 ;

END ;

SELECT *

FROM Res ;

3)
DECLARE

CURSOR CS IS

SELECT NomEmp, Salaire

FROM Emp

WHERE (NumDep = 8) AND (Salaire > 1000) ;

Nom Emp.NomEmp%TYPE ;

Sal Emp.Salaire%TYPE ;

BEGIN

OPEN CS

LOOP

Ingénierie des bases de données 48

ESC Tunis / ISAMM 2e LBDAD

FETCH CS INTO Nom, Sal ;

EXIT WHEN CS%NOTFOUND OR CS%ROWCONUT > 15 ;

INSERT INTO Tmp

VALUES (Nom, Sal) ;

END LOOP ;

END ;

SELECT *

FROM Tmp ;

4)
ACCEPT n PROMPT ''Donner n'' ;

DECLARE

CURSOR C IS

SELECT DISTINCT Salaire

FROM Emp

ORDER BY Salaire DESC ;

CURSOR D (V NUMBER) IS -- ou bien Emp.Salaire%TYPE

SELECT NumEmp, NomEmp

FROM Emp

Ingénierie des bases de données 49

ESC Tunis / ISAMM 2e LBDAD

WHERE Salaire = V ;

Sal Emp.Salaire%TYPE ;

i NUMBER ;

BEGIN

OPEN C ;

LOOP

FETCH C INTO Sal ;

EXIT WHEN C%NOTFOUND OR C%ROWCOUNT > &n ;

FOR i IN D (V) LOOP

INSERT INTO Tmp

VALUES (i.NumEmp, i.NomEmp) ;

END LOOP ;

END LOOP ;

CLOSE C ;

END ;

SELECT *

FROM Tmp ;

Ingénierie des bases de données 50

ESC Tunis / ISAMM 2e LBDAD

5)
DECLARE

CURSOR C1 IS

......

CurVide EXCEPTION;

PRAGMA_EXCEPTION_INIT (CurVide, +100);

BEGIN

....

EXCEPTION

WHEN CurVide THEN

RAISE_APPLICATION_ERROR (-20010, ''message d'erreur'');

END;

Ingénierie des bases de données 51