Les curseurs

Oracle utilise des espaces de travail pour exécuter les

ordres SQL et en manipuler les données:
Le curseur, permet d’attribuer un nom à cet espace
de travail et d’accéder aux données qu’il contient. Il
existe deux sortes de curseurs :
●
les curseurs implicites
●
les curseurs explicites.
Le PL/SQL crée de manière implicite un curseur
pour chaque ordre SQL, même pour ceux qui ne
retournent qu’une ligne.
 Les curseurs explicites
Pour les curseurs qui renvoient plus d’un
enregistrement, vous pouvez déclarer explicitement
un curseur, ce qui permet de traiter individuellement les
lignes retournées.
DECLARE
.....
CURSOR mon_curseur IS ordre select ;
....
BEGIN
DECLARE
CURSOR cur_dept10 IS
SELECT nom, salaire, fonction FROM emp
WHERE deptno = 10 ;
L’ensemble des lignes renvoyées par une interrogation
multiligne s’appelle un result set (jeu de résultats).
Le nombre de colonnes, ainsi que les lignes retournées
dépendent de l’ordre SQL traité.

emp1 10000 vendeur

emp2 15000 président
emp3 7000 vendeur
emp4 5600 sécretaire
 PL/SQL ouvre un curseur, traite les enregistrements
retournés par l’ordre SQL, puis ferme le curseur. De
même qu’un pointeur dans un fichier identifie
l’enregistrement courant, le curseur permet d’isoler
l’enregistrement courant d’un result set.

 Les étapes d’un curseur

●
Déclaration du curseur : CURSOR pour déclare un
curseur (la partie DECLARE)
●
Ouverture du curseur : OPEN exécute l’ordre SQL
associé au curseur, identifie le result set et positionne
le curseur avant le premier enregistrement
●
Traitement des lignes du curseur : FETCH retourne
l’enregistrement courant puis place le curseur sur
l’enregistrement suivant.
●
Fermeture du curseur: CLOSE
 La déclaration d’un curseur
Tout curseur utilisé dans un bloc PL/SQL doit
obligatoirement être déclaré dans la section
DECLARE du bloc, en précisant son nom et l’ordre
SQL associé.
CURSOR nom_curseur IS ordre_select ;
Par exemple :
DECLARE
-- déclaration de variables
...;
-- déclaration de curseurs
CURSOR c_emp IS SELECT nom, nodept, salaire
FROM emp WHERE fonction = ‘Vendeur’
ORDER BY nom ;
BEGIN
...;
...;
END;
 Ouverture d’un curseur
Dès que vous ouvrez le curseur, l’exécution de
l’ordre SQL est lancée. Cette phase d’ouverture
s’effectue dans la section BEGIN du bloc.

OPEN nom_curseur ;
Par exemple :
DECLARE
-- déclaration de variables
...;
-- déclaration de curseurs
CURSOR c_emp IS SELECT nom, nodept, salaire
FROM emp WHERE fonction = ‘Vendeur’
ORDER BY nom ;
BEGIN
open c_emp;
...;
END;
 Traitements

FETCH nom_curseur INTO liste_de_variables;

On récupérer les lignes de l’ordre SELECT et les traite

une par une, en stockant la valeur de chaque colonne
de l’ordre SQL dans une variable réceptrice.
DECLARE
-- déclaration de variables
v_nom emp.nom%TYPE;
v_departement emp.nodept%TYPE;
v_salaire emp.salaire%TYPE;
v_fonction emp.fonction%TYPE;
-- déclaration de curseurs
CURSOR c_emp IS SELECT nom, deptartement,
salaire FROM emp WHERE fonction = ‘Vendeur’
ORDER BY nom;
BEGIN
OPEN c_emp ;
LOOP
FETCH c_emp INTO v_nom,v_departement,
v_salaire ;
IF (v_salaire < 1000) THEN
INSERT INTO table_resultat
VALUES ( v_nom, v_departement, v_salaire );
END IF;
EXIT WHEN condition;
END LOOP;
...
END;
 Fermeture d’un curseur

CLOSE nom_curseur ;

L’instruction CLOSE ferme le curseur ouvert par

l’instruction OPEN et libère les ressources
consommées.
 EXERCICE

Ecrire un programme PL/SQL qui mis tous les

vendeur (nom, departement et salaire) dans
une table resultat.
 Les attributs d’un curseur

Les attributs permettent de connaître l’état de vos

curseurs :
• %FOUND et %NOTFOUND déterminent si toutes
les lignes retournées par le curseur ont été
traitées ;
• %ISOPEN précise si le curseur est ouvert ;
• %ROWCOUNT indique le nombre de lignes déjà
traitées.
 Les attributs du curseur : %FOUND

Dans le cas d’un curseur implicite, l’attribut SQL

%FOUND prend la valeur :
• INSERT, UPDATE, DELETE :
SQL%FOUND = TRUE, si l’ordre a traité au moins
une ligne ;
• SELECT... INTO : SQL%FOUND = TRUE, si l’ordre
SELECT a renvoyé une seule ligne.
 Les attributs du curseur : %FOUND

Dans le cas d’un curseur explicite :

nom_du_curseur%FOUND = TRUE, si le
dernier FETCH a retourné une ligne.
 Les attributs du curseur : %NOTFOUND

L'attribut SQL %NOTFOUND prend la valeur :

• INSERT, UPDATE, DELETE :
SQL%NOTFOUND = TRUE, si l’ordre n’a traité
aucune ligne ;
• SELECT... INTO :
SQL%NOTFOUND = TRUE, si l’ordre SELECT
n’a retourné aucune ligne.
 Les attributs du curseur : %NOTFOUND

Dans le cas d’un curseur explicite :

nom_du_curseur%NOTFOUND = TRUE, si le
dernier FETCH n’a pas renvoyé de ligne.

La condition idéale très largement utilisée pour

sortir d’une boucle FETCH.
 Les attributs d’un curseur : %ISOPEN

Cet attribut permet de vérifier si le curseur

considéré a été ouvert.
 Les attributs du curseur : %ROWCOUNT

L’attribut %ROWCOUNT renvoie dans une valeur

numérique le nombre de lignes traitées.
Dans le cas d’un curseur implicite :
INSERT, UPDATE, DELETE,
SQL%ROWCOUNT retourne le nombre de lignes
traitées par votre ordre SQL ;
Les attributs du curseur : %ROWCOUNT

SELECT... INTO : SQL%ROWCOUNT prend

les valeurs suivantes :
• SQL%ROWCOUNT = 0, si le SELECT... INTO
n’a renvoyé aucune ligne ;
• SQL%ROWCOUNT = 1, si le SELECT... INTO
n’a renvoyé qu’une seule ligne ;
• SQL%ROWCOUNT = 2, si le SELECT... INTO
a renvoyé plus d’une ligne.
Les attributs du curseur : %ROWCOUNT

Dans le cas d’un curseur explicite :

nom_curseur%ROWCOUNT
traduit l’énième ligne retournée par le FETCH.
 Problème

Nous recherchons le salaire de chaque

employé ainsi que la moyenne des salaires des
employés qui font le même métier que lui.
DECLARE
-- déclaration de variables
v_nom emp.nom%TYPE;
v_salaire emp.salaire%TYPE;
v_fonction emp.fonction%TYPE;
v_moy NUMBER(7,2);
-- déclaration de curseurs
CURSOR c_emp IS SELECT nom, salaire, fonction
FROM emp
ORDER BY nom ; – facultatif
BEGIN
IF NOT (c_emp%ISOPEN) THEN
OPEN c_emp ;
END IF;
LOOP
FETCH c_emp INTO v_nom,v_salaire , v_fonction ;
EXIT WHEN c_emp%NOTFOUND ; --sortie boucle
SELECT avg(sal) INTO v_moy FROM emp
WHERE job = v_fonction ;
-- stockage des résultats dans une table
INSERT INTO res VALUES ( v_nom, v_salaire, v_moyenne );
END LOOP;
CLOSE c_emp ;
Ecrire un programme PL/SQL qui stocke dans
une table résultat les 10 premiers candidats qui
passe un concours.
 Les curseurs paramétrés

Il est possible de réutiliser un même curseur avec des valeurs

différentes, dans un même bloc PL/SQL :
DECLARE
CURSOR mon_curseur (param1 TYPE, param2 TYPE, ... )
IS mon_ordre_select ;
-- l’ordre select utilise les paramètres param1, param2...
BEGIN
OPEN mon_curseur (val1, val2,...) ;
...
CLOSE mon_curseur;
END;
 Les curseurs paramétrés

Les types des paramètres sont les suivants :

CHAR, NUMBER, DATE, BOOLEAN,

Leur longueur n’est pas spécifiée. Le passage des

valeurs des paramètres s’effectue à l’ouverture du
curseur.
 Mettre à jour des données avec un curseur :
CURRENT-OF
la clause CURRENT-OF permet d’accéder directement, en
modification ou en suppression, à la ligne que vient de
renvoyer l’ordre FETCH.
Au préalable, dans la déclaration du curseur, il faut réserver
les lignes qui seront modifiées par la pose d’un verrou
d’intention (...FOR UPDATE OF colonne_à_ modifier...).
DECLARE
CURSOR mon_curseur IS SELECT nom, salaire, commission
FROM emp FOR UPDATE OF sal ;
BEGIN
FOR ma_structure IN mon_curseur
LOOP
IF ma_structure.comm IS NOT NULL THEN
-- si pas de commission, le salaire augmente de 5%
UPDATE emp SET salaire = salaire*1.05
WHERE CURRENT OF mon_curseur;
END IF;
END LOOP;
END;
 Gestion des erreurs
Le PL/SQL disposait d’un mécanisme pour traiter les erreurs
et les exceptions rencontrées dans les blocs PL/SQL.
Il existe deux grandes familles d’erreurs :
• celles retournées par Oracle (table inaccessible, conflit de
mise à jour...) ;
• celles dues à l’écriture du programme utilisateur.
 Gestion des erreurs
- Syntaxe d’utilisation des exceptions
DECLARE
...
nom_erreur EXCEPTION ;
...
BEGIN
...
IF (problème rencontré) THEN RAISE nom_erreur ;
...
EXCEPTION
WHEN nom_erreur THEN
(traitement de l’erreur);

END;
 Gestion des erreurs
Pour les erreurs Oracle prédéfinies, vous n’avez
pas besoin de placer une instruction d’appel à
l’exception dans le code PL/SQL :
DECLARE
...
BEGIN
...
EXCEPTION
WHEN ZERO_DIVIDE THEN (traitement de l’erreur);
WHEN NO_DATA_FOUND THEN (traitement de l’erreur);
...
 Les procédures
PROCEDURE ma_procedure (par1, par2, ...parn) IS
-- zone de déclaration : des variables locales, des –
– curseurs , des exceptions
BEGIN
-- traitements
EXCEPTION
-- traitement des exceptions...
END;
 Les fonctions
FUNCTION ma_fonction (par1,par2,...parn)
RETURN type_de_la_variable_retournée IS
-- zone de déclaration : des variables locales
– des curseurs des exceptions
BEGIN
-- traitements
-- clause RETURN
EXCEPTION
-- traitement des exceptions...
-- clause RETURN
END;
 Les packages

Un package PL/SQL Oracle permet de

regrouper un ensemble de procédures, de
fonctions et de variables au sein d’un ensemble
cohérent.
le package standard DBMS_OUTPUT

sql> set serveroutput on

dbms_output.put_line();