Cours SQL ORACLE – 07-04 : CURSOR (Curseur)

Les CURSORS (ou curseurs en français) sont des boucles qu’on utilise pour faire des itérations sur le résultat d’une requête SELECT ligne par ligne.

Exemple, on peut créer un curseur qui va boucler sur la table EMP avec le PRENOM commençant par B. Pour chaque ligne de ce résultat, on va afficher les informations de cet employé.

Deux Type de curseurs existent :

1 . Les curseurs explicites:

Les curseurs explicites sont déclarés dans la partie de déclaration du Bloc PL/SQL :

DECLARE
CURSOR <nom_du_curseur> IS
SELECT ... FROM ... ;

BEGIN
...
END;

Exemple : Un curseur qui comporte tous les employés de la table EMP qui occupent un poste de MANAGER

DECLARE
CURSOR curseur_EMPLOYE IS
SELECT ENAME, MGR, SAL FROM EMP WHERE JOB = 'MANAGER';

BEGIN
...
END;

Une fois le curseur déclaré, on pourra l’utiliser dans le Bloc PL/SQL après le BEGIN. On commence d’abord par l’ouvrir avant de la parcourir.

Pour ouvrir un curseur :

DECLARE
CURSOR curseur_EMPLOYE IS
SELECT ENAME, MGR, SAL FROM EMP WHERE JOB = 'MANAGER';

BEGIN
OPEN curseur_EMPLOYE ;
...
END;

Après l’ouverture du curseur, nous pouvons le parcourir et faire autant d’itération possible que de lignes retournées par la requête du curseur. On utilise la boucle LOOP … END LOOP pour les itérations :

DECLARE
CURSOR curseur_EMPLOYE IS
SELECT ENAME, MGR, SAL FROM EMP WHERE JOB = 'MANAGER';

BEGIN
OPEN curseur_EMPLOYE ;

LOOP
...
END LOOP;
END;

Une fois nous somme dans la boucle, on doit affecter la ligne en cours à une ou des variables selon les colonnes sélectionnées dans notre SELECT. Pour cela on utilise FETCH … INTO :

DECLARE
V_ENAME EMP.ENAME%TYPE;
V_MGR EMP.MGR%TYPE;
V_SAL EMP.SAL%TYPE;
CURSOR curseur_EMPLOYE IS
SELECT ENAME, MGR, SAL FROM EMP WHERE JOB = 'MANAGER';

BEGIN
OPEN curseur_EMPLOYE ;

LOOP
FETCH curseur_EMPLOYE INTO V_ENAME, V_MGR, V_SAL;
...
END LOOP;
END;

Enfin, il faudra ajouter la condition de sortie de la boucle. Généralement la condition de sortie est que nous avons parcouru toutes les lignes retournées :

DECLARE
V_ENAME EMP.ENAME%TYPE;
V_MGR EMP.MGR%TYPE;
V_SAL EMP.SAL%TYPE;
CURSOR curseur_EMPLOYE IS
SELECT ENAME, MGR, SAL FROM EMP WHERE JOB = 'MANAGER';

BEGIN
OPEN curseur_EMPLOYE ;

LOOP
FETCH curseur_EMPLOYE INTO V_ENAME, V_MGR, V_SAL;
...
EXIT WHEN NOT curseur_EMPLOYE%FOUND;
/* On peut utiliser aussi NOTFOUND comme suit
EXIT WHEN curseur_EMPLOYE%NOTFOUND;
*/

END LOOP;
END;

Le curseur est mis en place, nous pouvons exploiter et manipuler ses données selon notre besoin puis fermer ce curseur :

DECLARE
V_ENAME EMP.ENAME%TYPE;
V_MGR EMP.MGR%TYPE;
V_SAL EMP.SAL%TYPE;
CURSOR curseur_EMPLOYE IS
SELECT ENAME, MGR, SAL FROM EMP WHERE JOB = 'MANAGER';

BEGIN
OPEN curseur_EMPLOYE ;

LOOP
FETCH curseur_EMPLOYE INTO V_ENAME, V_MGR, V_SAL;

DBMS_OUTPUT.PUT_LINE('L''employé ' || V_ENAME || ' a un salaire de ' || V_SAL ||
'. Le numéro de son Manager est ' || V_MGR);
EXIT WHEN NOT curseur_EMPLOYE%FOUND;
END LOOP;

CLOSE curseur_EMPLOYE ;
END;

Le résultat de ce Block PL/SQL est le suivant :

L'employé JONES a un salaire de 2975. Le numéro de son Manager est 7839
L'employé BLAKE a un salaire de 2850. Le numéro de son Manager est 7839
L'employé CLARK a un salaire de 2450. Le numéro de son Manager est 7839

Nous pouvons utiliser * dans le SELECT du curseur, dans ce cas, la déclaration de la variable qui comportera les lignes de curseur sera du même type que la table sélectionnée ROWTYPE. Par-contre l’appel de chaque colonne sera <nom_curseur>.<nom_colonne> :

DECLARE
V_EMP EMP%ROWTYPE;

CURSOR curseur_EMPLOYE IS
SELECT * FROM EMP WHERE JOB = 'MANAGER';

BEGIN
OPEN curseur_EMPLOYE ;

LOOP
FETCH curseur_EMPLOYE INTO V_EMP ;

DBMS_OUTPUT.PUT_LINE('L''employé ' || V_EMP.ENAME || ' a un salaire de ' ||
V_EMP.SAL || '. Le numéro de son Manager est ' || V_EMP.MGR);
EXIT WHEN NOT curseur_EMPLOYE%FOUND;
END LOOP;
CLOSE curseur_EMPLOYE ;
END;

Le deuxième type de curseur est implicite

2 . Les curseurs implicites:

Les curseurs implicites sont plus facile à utiliser que ceux explicites. On ne déclare pas de curseur, ni on l’ouvre ni on le ferme. On utilise toujours la boucle FOR :

FOR <nom_curseur> IN (<requte_SELECT>)
LOOP
...
END LOOP;

On reprend le dernier exemple cette fois ci avec un curseur explicite :

DECLARE
BEGIN
FOR curseur_EMPLOYE IN (SELECT * FROM EMP WHERE JOB = 'MANAGER')
LOOP

DBMS_OUTPUT.PUT_LINE('L''employé ' || curseur_EMPLOYE.ENAME ||
' a un salaire de ' || curseur_EMPLOYE.SAL ||
'. Le numéro de son Manager est ' || curseur_EMPLOYE.MGR);

END LOOP;
END;

Le résultat est toujours le même dans ce cas:

L'employé JONES a un salaire de 2975. Le numéro de son Manager est 7839
L'employé BLAKE a un salaire de 2850. Le numéro de son Manager est 7839
L'employé CLARK a un salaire de 2450. Le numéro de son Manager est 7839