Manipulation des curseurs

Ce document traite de la manipulation des curseurs en PL/SQL, un concept essentiel pour gérer les résultats des requêtes SQL retournant plusieurs enregistrements. Il s’adresse aux étudiants et développeurs souhaitant maîtriser l’utilisation des curseurs explicites, leurs attributs, ainsi que les techniques simplifiées et paramétrées pour optimiser le traitement des données.

Manipulation des curseurs

Un curseur est une variable qui pointe vers le résultat d’une requête SQL. Il existe deux types de curseurs :

• Curseurs implicites : déclarés et gérés automatiquement par SQL, utilisés pour les requêtes retournant un seul enregistrement.
• Curseurs explicites : déclarés et manipulés par l’utilisateur, adaptés aux requêtes retournant plusieurs enregistrements.

Étapes de manipulation d’un curseur explicite

• Déclaration : définir le curseur avec une requête SQL.
• Ouverture : ouvrir le curseur pour exécuter la requête et allouer la mémoire.
• Exécution (FETCH) : lire les lignes une par une et affecter les valeurs aux variables hôtes.
• Fermeture : libérer la mémoire en fermant le curseur.

Déclaration

Syntaxe :

CURSOR nom_curseur IS instruction_select;

Exemple :

DECLARE
  CURSOR cur_emp IS
    SELECT employee_id, first_name, last_name FROM employees
    WHERE department_id = 100;
  CURSOR cur_dept IS
    SELECT * FROM departments
    ORDER BY department_id;

Ouverture

Syntaxe :

OPEN nom_curseur;

Ouvre le curseur pour exécuter la requête et identifier les lignes. Aucune exception n’est levée si la requête ne retourne aucune ligne.

Exemple :

DECLARE
  CURSOR cur_emp IS
    SELECT employee_id, first_name, last_name FROM employees
    WHERE department_id = 100;
BEGIN
  OPEN cur_emp;
END;

Exécution (FETCH)

Syntaxe :

FETCH nom_curseur INTO variable1, variable2, ...;

Cette instruction lit la ligne courante pointée par le curseur et affecte ses valeurs aux variables hôtes. Elle avance ensuite le curseur à l’enregistrement suivant.

Il faut prévoir le même nombre de variables que de colonnes retournées et tester si le curseur contient des lignes.

Exemple complet :

DECLARE
  CURSOR cur_emp IS
    SELECT employee_id, first_name, last_name FROM employees
    WHERE department_id = 100;
  v_no employees.employee_id%TYPE;
  v_fname employees.first_name%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO v_no, v_fname, v_lname;
    EXIT WHEN cur_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employé no: ' || v_no || ' Nom: ' || v_fname || ' Prénom: ' || v_lname);
  END LOOP;
  CLOSE cur_emp;
END;

Fermeture

Syntaxe :

CLOSE nom_curseur;

Cette instruction ferme le curseur et libère la mémoire allouée.

Exemple :

DECLARE
  CURSOR cur_emp IS
    SELECT employee_id FROM employees WHERE department_id = 100;
  v_no employees.employee_id%TYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO v_no;
    EXIT WHEN cur_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employé no: ' || v_no);
  END LOOP;
  CLOSE cur_emp;
END;

Attributs des curseurs

Chaque curseur possède quatre attributs principaux :

• %ISOPEN : renvoie TRUE si le curseur est ouvert.
• %FOUND : renvoie TRUE si le dernier FETCH a réussi (données lues).
• %NOTFOUND : inverse de %FOUND, TRUE si aucun enregistrement n’a été trouvé.
• %ROWCOUNT : nombre de lignes déjà lues par le curseur.

Ces attributs s’utilisent en les préfixant par le nom du curseur, par exemple : cur_emp%FOUND.

Exemple d’utilisation :

DECLARE
  CURSOR cur_emp IS SELECT employee_id, last_name FROM employees;
  v_no employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  IF NOT cur_emp%ISOPEN THEN
    OPEN cur_emp;
  END IF;
  FETCH cur_emp INTO v_no, v_lname;
  WHILE cur_emp%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('Ligne numéro : ' || cur_emp%ROWCOUNT);
    FETCH cur_emp INTO v_no, v_lname;
  END LOOP;
  CLOSE cur_emp;
END;

Utilisation simplifiée des curseurs

Utilisation du type RECORD

Il est possible de déclarer un enregistrement (record) dont les champs correspondent aux colonnes retournées par le curseur. Cela évite de déclarer une variable par colonne.

Déclaration :

DECLARE
  CURSOR cur_emp IS SELECT employee_id, last_name FROM employees;
  rec_emp cur_emp%ROWTYPE;

Pour accéder aux colonnes :

• rec_emp.employee_id
• rec_emp.last_name

Exemple :

DECLARE
  CURSOR cur_emp IS SELECT employee_id, last_name FROM employees;
  rec_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO rec_emp;
    EXIT WHEN cur_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employé no: ' || rec_emp.employee_id || ' Nom: ' || rec_emp.last_name);
  END LOOP;
  CLOSE cur_emp;
END;

Utilisation de la structure FOR .. IN

La structure FOR .. IN simplifie encore plus la manipulation des curseurs en automatisant l’ouverture, le FETCH, la condition de sortie et la fermeture du curseur. Elle évite également de déclarer explicitement un enregistrement hôte.

Syntaxe :

FOR nom_record IN nom_curseur LOOP
  -- traitement avec nom_record.colonne
END LOOP;

Exemple :

DECLARE
  CURSOR cur_emp IS SELECT employee_id, last_name FROM employees;
BEGIN
  FOR rec_emp IN cur_emp LOOP
    DBMS_OUTPUT.PUT_LINE('Employé no: ' || rec_emp.employee_id || ' Nom: ' || rec_emp.last_name);
  END LOOP;
END;

Utilisation de sous-requête dans FOR .. IN

On peut éviter de déclarer un curseur en utilisant directement une requête dans la boucle FOR .. IN. Le curseur et l’enregistrement sont alors déclarés implicitement.

Syntaxe :

FOR nom_record IN (requête_select) LOOP
  -- traitement
END LOOP;

Exemple :

BEGIN
  FOR rec_emp IN (SELECT employee_id, last_name FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE('Employé no: ' || rec_emp.employee_id || ' Nom: ' || rec_emp.last_name);
  END LOOP;
END;

Curseurs paramétrés

Les curseurs paramétrés permettent de passer des paramètres à la requête associée au curseur, évitant ainsi de multiplier les curseurs similaires.

Syntaxe de définition :

CURSOR nom_curseur(param1 type, param2 type, ...) IS requête_select;

Les valeurs des paramètres sont transmises lors de l’ouverture :

OPEN nom_curseur(valeurParam1, valeurParam2, ...);

Il faut fermer le curseur avant de l’ouvrir avec d’autres valeurs, sauf si on utilise la boucle FOR qui gère automatiquement la fermeture.

Exemple :

DECLARE
  CURSOR cur_emp(v_dept NUMBER, v_sal employees.salary%TYPE) IS
    SELECT employee_id, last_name, salary FROM employees
    WHERE department_id = v_dept AND salary > v_sal;
  rec_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp(80, 6000);
  LOOP
    FETCH cur_emp INTO rec_emp;
    EXIT WHEN cur_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employé no: ' || rec_emp.employee_id || ' Nom: ' || rec_emp.last_name || ' Salaire: ' || rec_emp.salary);
  END LOOP;
  CLOSE cur_emp;
END;

Glossaire des termes clés

• Curseur : variable pointant vers le résultat d’une requête SQL.
• Curseur implicite : curseur géré automatiquement par SQL pour une seule ligne.
• Curseur explicite : curseur déclaré et manipulé par l’utilisateur pour plusieurs lignes.
• FETCH : instruction qui lit la ligne courante du curseur et avance au suivant.
• OPEN : instruction qui ouvre un curseur et exécute la requête.
• CLOSE : instruction qui ferme un curseur et libère la mémoire.
• %ISOPEN : attribut indiquant si le curseur est ouvert.
• %FOUND : attribut indiquant si le dernier FETCH a réussi.
• %NOTFOUND : attribut indiquant si le dernier FETCH n’a pas trouvé de ligne.
• %ROWCOUNT : nombre de lignes déjà lues par le curseur.
• Record (enregistrement) : structure regroupant plusieurs colonnes retournées par un curseur.
• FOR .. IN : structure de boucle simplifiée pour parcourir un curseur ou une requête.
• Curseur paramétré : curseur acceptant des paramètres pour personnaliser la requête.

Points clés à retenir

• Les curseurs explicites sont nécessaires pour traiter plusieurs lignes retournées par une requête.
• La manipulation d’un curseur suit quatre étapes : déclaration, ouverture, exécution (FETCH), fermeture.
• Les attributs %ISOPEN, %FOUND, %NOTFOUND et %ROWCOUNT permettent de contrôler l’état et la progression du curseur.
• Le type RECORD et la structure FOR .. IN simplifient grandement la gestion des curseurs.
• Les curseurs paramétrés évitent la duplication de curseurs similaires en permettant de passer des arguments à la requête.