Part. 2. Chap.

4 : PL/SQL : Les curseurs

L'une des plus importantes caractéristiques du PL/SQL est la possibilité de manipuler les données ligne
par ligne.
Lorsque l'on exécute un ordre SQL à partir de PL/SQL, Oracle alloue une zone mémoire de travail privée
pour cet ordre. Cette zone de travail contient des informations relatives à l'ordre SQL ainsi que les statuts
d’exécution de l’ordre.

I. DEFINITION
Les curseurs PL/SQL sont un mécanisme permettant de nommer cette zone de travail et de manipuler
les données qu'elle contient.
Le curseur permet de nommer cette zone de contexte, d'accéder aux informations et éventuellement de
contrôler le traitement. Cette zone de contexte est une mémoire de taille fixe, utilisée par le noyau pour
analyser et interpréter tout ordre SQL.
Un curseur PL/SQL permet de récupérer et de traiter les données de la base dans un programme
PL/SQL, ligne par ligne.
Il existe deux types de curseurs :
- Le curseur explicite : Il est créé et géré par l'utilisateur pour traiter un ordre SELECT qui
ramène plusieurs lignes. Le traitement du select se fera ligne par ligne.
- Le curseur implicite : Il est généré et géré par le noyau pour les autres commandes SQL.

II. UTILISATION D'UN CURSEUR EXPLICITE

Pour traiter une requête qui retourne plusieurs lignes, l'utilisateur doit définir un curseur qui lui permet
d’extraire la totalité des lignes sélectionnées.
L’utilisation d'un curseur pour traiter un ordre SELECT ramenant plusieurs lignes, nécessite 4 étapes :
1. Déclaration du curseur
2. Ouverture du curseur
3. Traitement des lignes
4. Fermeture du curseur.

Figure 1 : Etapes d’utilisation d’un curseur

21
 Part. 2. Chap. 4 : PL/SQL : Les curseurs

II.1 La déclaration d'un curseur

La déclaration du curseur permet de stocker l'ordre SELECT dans le curseur.

Le curseur se définit dans la partie DECALRE d'un bloc PL/SQL.
Syntaxe :
CURSOR nomcurseur [(nompararn type [,nomparam type, ...)]
IS Commande_SELECT ;
Exemple :
DECLARE
CURSOR DEPT_10 IS
SELECT ename, sal FROM emp WHERE depno = 10;

II.2 L'ouverture et la fermeture d’un curseur

L'étape d'ouverture permet d'effectuer :

1. l'allocation mémoire du curseur,
2. l'analyse sémantique et syntaxique de l'ordre
3. le positionnement de verrous éventuels (si select for update...)
L'étape de fermeture permet de libérer la place mémoire réservée.
Syntaxe :
OPEN nomcurseur [(nomparam1[,nomparam2 , ...)] ;
/* traitement des lignes */
CLOSE nomcurseur ;
Exemple :
BEGIN
…
OPEN DEPT_10 ;
/* traitement des lignes*/
CLOSE DEPT_10 ;

II.3 Le traitement des lignes

Il faut traiter les lignes une par une et renseigner les variables réceptrices définies dans la partie
DECLARE du bloc.
Syntaxe : Dans la partie traitement du bloc PL/SQL, il faut commencer par ouvrir le curseur puis :
FETCH nomcurseur INTO {nomvariable [,nomvariable] | nomrecord}
L’ordre FETCH ne ramène qu’une seule ligne à la fois. De ce fait, il faut recommencer l’ordre pour traiter
la ligne suivante.
Exemple :
DECLARE
CURSOR DEPT_10 IS
SELECT ename, sal FROM emp WHERE depno = 10;
Vnom emp.name%TYPE ;
Vsalaire emp.sal%TYPE ;

22
 Part. 2. Chap. 4 : PL/SQL : Les curseurs

BEGIN
OPEN DEPT_10;
LOOP
FETCH DEPT10 INTO vnom,vsalaire ;
--Traitement ligne
END LOOP ;
CLOSE DEPT_10;
END;

III. LES ATTRIBUTS D’UN CURSEUR

Les attributs d’un curseur fournissent des informations sur l’exécution d’un ordre. Ils sont conservés par
PL/SQL après l’exécution du curseur (implicite ou explicite).
Ces attributs permettent de tester directement le résultat de l’exécution. Ils sont résumés dans le tableau
suivant :
Curseurs implicites Curseurs explicites
SQL%FOUND NOMCURSEUR%FOUND

SQL%NOTFOUND NOMCURSEUR%NOTFOUND

SQL%ISOPEN NOMCURSEUR%ISOPEN

SQL%ROWCOUNT NOMCURSEUR%ROWCOUNT

SQL%ROWTYPE NOMCURSEUR%ROWTYPE

Pour chaque exécution d'un ordre de manipulation du curseur, le moteur SQL renvoie une information
appelée statut, qui indique si l'ordre a été exécuté avec succès ou non.
Cette information est disponible dans le programme par l'intermédiaire de quatre attributs rattachés à
chaque curseur.
Les statuts du curseur explicite sont :
- %FOUND : C’est un attribut de type booléen ; il est « VRAI » si exécution correcte de l'ordre
SQL.
- %NOTFOUND : C’est un attribut de type booléen ; il est « VRAI » si exécution incorrecte de
l'ordre SQL.
- %ISOPEN : C’est un attribut de type booléen ; il est « VRAI » si curseur ouvert.
- %ROWCOUNT : Nombre de lignes traitées par l'ordre SQL ; il évolue à chaque ligne distribuée.
La syntaxe de consultation d’un attribut est :
nom_cursor%attribut;

23
 Part. 2. Chap. 4 : PL/SQL : Les curseurs

IV. LES BOUCLES ET LES CURSEURS

L'objectif est de fournir au programmeur une structure simple et efficace pour utiliser les structures de
boucle et les curseurs.

Syntaxe :
DECLARE
CURSOR nomcurseur IS ordre_select ;
BEGIN
FOR nomrecord IN nomcurseur LOOP
/* traitement
END LOOP;
END;
Le bloc PL/SQL ci-dessus permet d'obtenir une génération implicite de la structure suivante :
Syntaxe :
DECLARE
CURSOR nomcurseur IS ordre_select;
nomrecord nomcurseur%ROWTYPE;
BEGIN
OPEN nomcurseur ;
LOOP
FETCH nomcurseur INTO nomrecord ;
EXIT WHEN nomcurseur%NOTFOUND ;
/* traitement
END LOOP;
CLOSE nomcurseur;
END;

V. LES CURSEURS PARAMETRES

Un curseur paramétré permet d'utiliser des variables dans le curseur, principalement dans la clause
WHERE.
Il faut pour cela spécifier les noms et les types des paramètres dans la déclaration du curseur.
Syntaxe de déclaration :
CURSOR nomcurseur (param1 type, param2 type,...)
IS SELECT ordre_select ;
L'ordre_select utilise les paramètres.
Les types possibles sont : char, number, date, boolean sans spécifier la longueur.
Syntaxe d’utilisation :
BEGIN
OPEN nomcurseur (valeur1 , valeur2, ....) ;

24
 Part. 2. Chap. 4 : PL/SQL : Les curseurs

Exemple :
DECLARE
CURSOR c1 (depart number)
IS SELECT sal, nvl(comm,0) commi FROM emp WHERE deptno=depart ;
total number(11,2) := 0 ;
sal_sup number(4):=0 ;
comm_sup number(4):=0;
BEGIN
FOR c1_rec IN c1(20) LOOP
total:= total+ c1_rec.sal + c1_rec.commi ;
IF c1_rec.sal > 4000
THEN sal_sup := sal_sup + 1 ;
END IF ;
IF c1_rec.commi > 3000 THEN commi_sup:=commi_sup+1 ; END IF;
END LOOP;
INSERT INTO temp
VALUES (sal_Sup, comm_sup, 'total salaire’ || TO_CHAR(total));
COMMIT ;
END;

VI. LA CLAUSE "WHERE CURRENT OF…"

Cette clause permet d'accéder directement à la ligne ramenée par l'ordre FETCH afin de la traiter
(UPDATE, DELETE).
Il faut se réserver la ligne lors de la déclaration du curseur par le positionnement d'un verrou d'intention :
(FOR UPDATE OF nom_colonne).
Il faut spécifier que l'on veut traiter la ligne courante au FETCH par la clause : (WHERE CURRENT OF
nom_curseur).
Exemple :
DECLARE
CURSOR cl IS SELECT ename, sal FROM emp
FOR UPDATE OF sal ;
BEGIN
FOR c1_record IN c1 LOOP
IF c1_ record.sal > 1500 THEN
INSERT INTO resultat
VALUES (c1_record.sal, c1_record.sal*1.3, c1_record.ename);
UPDATE emp SET sal = sal * 1.3 WHERE CURRENT OF c1 ;
END IF;
END LOOP ;
COMMIT;
END ;

25