PL/SQL
Les curseurs
Objectif général :
Déclarer les curseurs, gérer les curseurs utiliser les boucles dédiées curseurs et
effectuer des mises à jour avec les curseurs.
D
Objectifs Elément de contenu Moyens Méthodologie
spécifiques
Ce cours vise à :
• Rôle
• Attributs d’un curseur
Définir Les curseurs Formative
implicites implicite
• Déclarer le curseur
• Contrôler les curseurs
Formative
Gérer les curseurs explicites
explicites • Ouvrir le curseur
• Extraire des données
à partir du curseur
• Fermer le curseur
• Curseurs et
enregistrements
• Boucles FOR dediée
curseur
Appliquer les • Exercice récapitulatif
différentes notions
-
1
PL/SQL
Les curseurs
Pour traiter une commande SQL, PL/SQL ouvre une zone de contexte pour
l’exécuter et stocker et les informations.
Chaque fois qu’une commande SELECT, INSERT, UPDATE ou DELETE est
soumise pour l’exécution, une zone de travail lui est allouée : c’est le curseur.
Définition :
• Un curseur est une zone de mémoire de taille fixe utilisé par le moteur
de base Oracle pour analyser et interpréter tout ordre SQL
• Il existe 2 types de curseurs :
– CURSEUR IMPLICITE :
Curseur SQL généré et géré par le noyau pour chaque ordre SQL
d'un bloc.
– CURSEUR EXPLICITE :
Curseur SQL généré et géré par l'utilisateur pour traiter un ordre
SELECT qui ramène plus d'une ligne.
1. CURSEUR IMPLICITE
C’est toute commande SQL (SELECT, INSERT, UPDATE, DELETE) située dans
la partie exécutable du bloc
Le curseur implicite est non déclaré dans la partie déclarative et doit ramener
exactement une seule ligne
• Syntaxe
Select col1, col2,…
Into v_col1, v_col2…
From relations
Where condition;
2
PL/SQL
Exemple :
Declare
V_nb number;
Begin
Select count(*) into v_nb from chercheur where labno = 10;
End;
/
1.1. Attributs d’un Curseur Implicite
Attribut Type Valeur
SQL%ROWCOUNT Number Retourne le nombre de lignes affectées par
le dernier ordre SQL.
SQL%FOUND Boolean True: si le dernier ordre SQL affecte au
moins une ligne
SQL%NOTFOUND Boolean True: si le dernier ordre SQL n’affecte
aucune ligne
SQL%ISOPEN Boolean Toujours FALSE
1.2. EXCEPTIONS d’un Curseur Implicite
• Si un curseur implicite ne ramène pas exactement une seule ligne,
PL/SQL considère cette situation comme une erreur et réagit en levant
une exception gérée dans la section EXCEPTION.
Condition N° exception Nom exception
SELECT …INTO n’identifie aucune ORA-01403 NO_DATA_FOUND
ligne
SELECT …INTO identifie plusieurs ORA-01422 TOO_MANY_ROWS
lignes
3
PL/SQL
2. CURSEUR EXPLICITE
C’est un ordre SELECT permettant de ramener zéro ou plusieurs lignes.
Il permet de:
– Ramener plusieurs lignes
– Travailler individuellement sur les lignes ramenées
– Faire référence à la ligne courante
– Évaluer l’état d’avancement
2.1. Cycle de vie d’un curseur explicite
Une fois le curseur explicite est déclaré dans la section déclarative, il
faut l’ouvrir c’est à dire réserver effectivement une place mémoire et
l’initialiser. L’ouverture du curseur provoque l’exécution de la commande
select le définissant et l’affectation de la zone mémoire du curseur. Par la
suite on récupére la ligne de la table stockée dans le curseur par la
commande FETCH et une fois le traitement est terminé, il faut libérer la
place mémoire en fermant le curseur :
2.1.1. Déclaration d’un curseur
La déclaration se fait dans la section DECLARE du bloc où on indique le
nom du curseur et l'ordre SQL associé
• Syntaxe :
CURSOR nom_curseur IS ordre_select ;
On peut attribuer des paramètres formels
• Syntaxe
CURSOR nom_curseur [(p_nom_paramètre type[{:=valeur|DEFAULT
valeur}]]IS ordre_select ;
• Exemple :
DECLARE
CURSOR pl_nice IS
SELECT pl, plnom
FROM pilote
WHERE adr='Nice';
4
PL/SQL
BEGIN
...
END ;
/
2.1.2. ouverture d’un curseur
L'ouverture du curseur alloue l’espace mémoire au curseur et exécute
l'ordre SELECT associé au curseur et place les éventuels verrous si le curseur est
défini avec l’option FOR UPDATE et elle se passe dans la section BEGIN du
bloc.
• Syntaxe :
OPEN nom_curseur [(paramètre effectif)];
• Exemple :
DECLARE
CURSOR pl_nice IS
SELECT pl, plnom
FROM pilote
WHERE adr='Nice';
BEGIN
...
OPEN pl_nice;
...
END ;
2.1.3. Traitement des lignes
Après l'exécution du SELECT les lignes ramenées sont traitées une par une,
la valeur de chaque colonne du SELECT doit être stockée dans une variable
réceptrice.
• Syntaxe :
FETCH nom_curseur INTO liste_variables ;
5
PL/SQL
• A chaque FETCH , le contenu de la ligne courante est affectée dans les
variables de la clause INTO et le pointeur d’enregistrement est déplacé
vers la ligne suivante
• Exemple :
DECLARE
CURSOR pl_nice IS
SELECT pl, plnom, sal
FROM pilote
WHERE adr='Nice';
num pilote.pl%TYPE;
nom pilote.plnom%TYPE;
salaire pilote.sal%TYPE;
BEGIN
OPEN pl_nice;
LOOP
FETCH pl_nice INTO num,
nom,salaire;
...
EXIT WHEN sal > 10 000;
END LOOP;
END ;
/
2.1.4. Fermeture d’un curseur
Pour libérer la mémoire prise par le curseur, il faut le fermer dès qu'on n'en a
plus besoin.
Syntaxe :
CLOSE nom_curseur ;
La réouverture d’un curseur est possible surtout pour les curseurs paramétrés
Exemple :
DECLARE
6
PL/SQL
CURSOR pl_nice IS
SELECT pl#, plnom, sal
FROM pilote
WHERE adr='Nice';
num pilote.pl#%TYPE;
nom pilote.plnom%TYPE;
salaire pilote.sal%TYPE;
BEGIN
OPEN pl_nice;
LOOP
FETCH pl_nice INTO num, nom,salaire;
...
EXIT WHEN sal > 10 000;
END LOOP;
CLOSE pl_nice;
END ;
/
2.2. Attributs d’un curseur explicite
attribut Type valeur Explication
nom_curseur%ROWCOUNT Number entier Compteur s’incrémente après
chaque ligne lue
0 Après OPEN et avant le premier
FETCH
nom_curseur%FOUND Boolean TRUE Si le dernier FETCH ramène une
ligne
FALSE Si le dernier FETCH ne trouve pas
de lignes
NULL Avant le premier FETCH
7
PL/SQL
nom_curseur%NOTFOUND Boolean TRUE Si le dernier FETCH ne trouve plus
de lignes
FALSE Si le dernier FETCH ramène une
ligne
NULL Avant le premier FETCH
nom_curseur%ISOPEN Boolean TRUE Si le curseur est ouvert
FALSE Si le curseur est fermé
2.2.1. Curseur%found
• Exemple :
DECLARE
CURSOR pl_nice IS
SELECT pl#, plnom, sal FROM pilote
WHERE adr='Nice';
num pilote.pl%TYPE;
nom pilote.plnom%TYPE;
salaire pilote.sal%TYPE;
BEGIN
OPEN pl_nice;
FETCH pl_nice INTO num, nom,salaire;
WHILE pl_nice%FOUND
LOOP
...
FETCH pl_nice INTO num,
nom,salaire;
END LOOP;
CLOSE pl_nice;
END ;
2.2.2. Curseur%isopen
• Exemple :
40
PL/SQL
DECLARE
CURSOR pl_nice IS
SELECT pl, plnom, sal
FROM pilote
WHERE adr='Nice';
num pilote.pl%TYPE;
nom pilote.plnom%TYPE;
salaire pilote.sal%TYPE;
BEGIN
IF NOT(pl_nice%ISOPEN)
THEN
OPEN pl_nice;
END IF;
...
END ;
/
2.2.3. Curseur%rowcount
Exemple :
Declare
cursor C1 is
select ename, empno, sal from emp order by sal desc ;
nom char(10);
numero number(4);
salaire number(7,2);
Begin
Open Cl;
Loop .
Fetch cl into nom, numéro, salaire ;
exit when (c1%rowcount > 25) or (cl%notfound);
insert into temp values (salaire, numéro, nom);
41
PL/SQL
End Loop;
Close C1 ;
commit ;
End;
2.2.4. Curseur%rowtype
Exemple :
Declare
cursor c1 is select sal + nvl(comm,0), saltot, ename from emp;
c1_record c1%Rowtype;
Begin
open c1;
Loop
Fetch c1 into c1_record;
Exit when c1 %notfound ;
if c1_record.saltot > 2000 then
insert into temp values (c1_record.saltot, c1_record.ename);
end if,
End Loop;
close c1;
End;
/
3. CURSEUR ET ENREGISTREMENT
Au lieu de déclarer autant de variables que d'attributs ramenés par le
SELECT du curseur, on peut utiliser une structure.
• Syntaxe :
DECLARE
CURSOR nom_curseur IS ordre_select;
nom_structure nom_curseur%ROWTYPE;
42
PL/SQL
• Pour renseigner la structure :
FETCH nom_curseur INTO nom_structure;
• Pour accéder aux éléments de la structure :
nom_structure.nom_colonne
3.1. Boucle dédiée curseur
Prend en charge :
– L’ouverture du curseur (OPEN)
– Le test de sortie (EXIT)
– La lecture (FETCH)
– La fermeture du curseur (CLOSE)
• Syntaxe
FOR nom_record IN nom_curseur
LOOP
--traitement
END LOOP;
Au lieu d'écrire :
DECLARE
CURSOR nom_curseur IS SELECT ... ;
rec nom_curseur%ROWTYPE;
BEGIN
OPEN nom_curseur;
LOOP
FETCH nom_curseur INTO rec;
EXIT WHEN nom_curseur%NOTFOUND;
...
END LOOP;
CLOSE nom_curseur;
END;
43
PL/SQL
/
il suffit d'écrire :
DECLARE
CURSOR nom_curseur IS SELECT ... ;
BEGIN
FOR nom_record IN nom_curseur LOOP
...
END LOOP;
END;
/
ou encore :
FOR nom_struct IN (SELECT ...)
LOOP
...
END LOOP;
3.2. la clause Where current of
• 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 c1 is select ename, sal from emp
for update of sal ;
Begin
For c1_record in c1 Loop
44
PL/SQL
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 ;
/
45
