Les curseurs en Oracle PL/SQL

L'écriture de SQL en PL/SQL est l'une des parties critiques de la programmation de bases de données. Toutes les instructions SQL intégrées dans un bloc PL/SQL sont exécutées comme un curseur. Un curseur est une zone de mémoire privée, temporairement allouée dans la zone globale de la session utilisateur (UGA), qui est utilisée pour traiter les instructions SQL. La mémoire privée stocke l'ensemble des résultats récupérés lors de l'exécution du SQL et les attributs du curseur. Les curseurs peuvent être classés en curseurs implicites et explicites.

•  Oracle crée un curseur implicite pour toutes les instructions SQL incluses dans la section exécutable d'un bloc PL/SQL. Dans ce cas, le cycle de vie du curseur est géré par la base de données Oracle.
•  Pour les curseurs explicites, le cycle d'exécution peut être contrôlé par l'utilisateur. Les développeurs de bases de données peuvent déclarer explicitement un curseur dans la section DECLARE.

Pendant ce cours, nous allons travailler avec ces deux tables :

Table - Employes                                      Table - Departement    
+----+---------+-----+----------+------------+------+  +--------+--------------+
| Id | Nom     | Age | Salaire  | Profession | Dep  |  | Id_dep | Nom_dep      |
+----+---------+-----+----------+------------+------+  +--------+--------------+
|  1 | Ismail  |  25 | 6000.00  | Assistant  |    2 |  |      1 | Informatique |
|  2 | Mohamed |  30 | 8000.40  | Directeur  |    1 |  |      2 | RH           |
|  3 | Fatima  |  29 | 6000.00  | Directeur  |    3 |  |      3 | Vente        |
|  4 | Dounia  |  30 | 7000.00  | Assistant  |    4 |  |      4 | Strategies   |
|  5 | Omar    |  30 | 10000.00 | Ingenieur  |    1 |  +--------+--------------+
|  7 | Mostafa |  33 | 7000.00  | Ingenieur  |    3 |
|  8 | Moneim  |  31 | 12000.00 | Ingenieur  |    4 |
+----+---------+-----+----------+------------+------+ 
                            

Un curseur est un pointeur vers une zone SQL privée qui stocke des informations sur le traitement d'une instruction SELECT ou LMD comme INSERT, UPDATE, DELETE ou MERGE.

Le curseur est un mécanisme qui vous permet d'attribuer un nom à une instruction SELECT et de manipuler les informations contenues dans cette instruction SQL.

La fonction principale d'un curseur est de récupérer des données, une ligne à la fois, à partir d'un jeu de résultats, contrairement aux commandes SQL qui agissent sur toutes les lignes du jeu de résultats à la fois. Les curseurs sont utilisés lorsque l'utilisateur a besoin de mettre à jour les enregistrements de manière singleton ou ligne par ligne, dans une table de base de données. Les données stockées dans le curseur sont appelées l'ensemble de données actif.

Chaque fois qu'une instruction LMD (INSERT, UPDATE et DELETE) est émise, un curseur implicite est associé à cette instruction. Pour les opérations INSERT, le curseur contient les données à insérer. Pour les opérations UPDATE et DELETE, le curseur identifie les lignes qui seraient affectées.

Dans PL/SQL, vous pouvez faire référence au curseur implicite le plus récent en tant que curseur SQL, qui a toujours des attributs tels que %FOUND,%ISOPEN,%NOTFOUND et %ROWCOUNT. Le curseur SQL a des attributs supplémentaires, %BULK_ROWCOUNT et %BULK_EXCEPTIONS, conçus pour être utilisés avec l'instruction FORALL. Le tableau suivant fournit la description des attributs d'un curseur :

  Exemple 1 :

Le programme suivant mettra à jour la table 'employés' et augmentera le salaire de chaque employé de 300 et utilisera l'attribut SQL\%ROWCOUNT pour déterminer le nombre de lignes affectées :

 DECLARE  
   nombre_ligne number(2); 
BEGIN 
   UPDATE Employes 
   SET Salaire = Salaire + 300; 
   IF sql%notfound THEN 
      dbms_output.put_line('aucun employé sélectionné'); 
   ELSIF sql%found THEN 
      nombre_ligne := sql%rowcount;
      dbms_output.put_line( nombre_ligne || ' employés sélectionnés '); 
   END IF;  
END; 
/ 
                            

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant :

  Exemple 2 :

Le programme suivant supprimera les employés dont le salaire est supérieur à 9000 et utilisera l'attribut SQL%ROWCOUNT pour déterminer le nombre de lignes supprimées

DECLARE  
   nombre_ligne number(2); 
BEGIN 
   DELETE FROM Employes WHERE Salaire>9000 ;
   IF sql%notfound THEN 
      dbms_output.put_line('aucun employé sélectionné'); 
   ELSIF sql%found THEN 
      nombre_ligne := sql%rowcount;
      dbms_output.put_line( nombre_ligne || ' employés supprimés '); 
   END IF;  
END; 
/
                            

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant :

L'utilisation d'un curseur explicite comprend les étapes suivantes :

•  Déclaration du curseur pour l'initialisation de la mémoire.
•  Ouverture du curseur pour allouer la mémoire.
•  Lorsque le curseur est ouvert, les lignes peuvent être extraites du curseur une par une ou dans un bloc pour effectuer la manipulation des données(FETCH).
•  Fermer le curseur pour libérer la mémoire allouée.

Déclaration du curseur

La syntaxe pour créer un curseur explicite est la suivante :

Syntaxe

CURSOR nom_curseur IS requete_sql; 
                            

Avec :

•  nom_curseur - Un nom approprié pour le curseur.
•  requete_sql - Une requête de sélection qui renvoie plusieurs lignes, ou autres requêtes LMD(UPDATE, DELETE)

  Exemple:

DECLARE  
   CURSOR liste_emp IS SELECT * FROM Employes;
BEGIN 
   -- instructions

END;   
                            

Ouverture du curseur

L'ouverture du curseur alloue la mémoire pour le curseur et le rend prêt pour l'extraction des lignes retournées par l'instruction SQL dans celui-ci.

OPEN nom_curseur; 

DECLARE  
   CURSOR liste_emp IS SELECT * FROM Employes;
BEGIN 
   OPEN liste_emp;
   -- instructions

END;  
                            

Extraction des lignes

L'extraction des données implique un accès à une ligne à la fois.

Syntaxe

                                FETCH nom_curseur INTO liste_variables; 
                            

Avec :

•  liste_variables - représentent une liste de variables pour stocker la valeur de chaque colonne. généralement nous utilisons record

  Exemple:

Par exemple, nous allons récupérer les lignes du curseur ouvert ci-dessus comme suit :

DECLARE  
   CURSOR list_emp IS SELECT * FROM Employes;
   record_emp Employes%rowtype;
BEGIN 
   open list_emp;
   fetch list_emp into record_emp;
   -- manipuler record_emp
END; 
                            

L'exemple ci-dessus récupere uniquement la première ligne, pour récupérer toutes les lignes une par une, nous utilisons une boucle.

DECLARE  
   CURSOR liste_emp IS SELECT * FROM Employes;
   record_emp Employes%rowtype;
BEGIN 
   OPEN liste_emp;
   LOOP
        FETCH liste_emp INTO record_emp;
            EXIT WHEN liste_emp%notfound; -- sortir si le curseur ne pointe sur aucune ligne
            -- Traitements sur la ligne courante
            dbms_output.put_line('Nom :'|| record_emp.nom || ' - Professeion : '|| record_emp.profession);
    END LOOP;  
    
    CLOSE liste_emp;    
END; 
                            

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant :

                                Nom :Ismail - Professeion : Assistant
                                Nom :Mohamed - Professeion : Directeur
                                Nom :Fatima - Professeion : Directeur
                                Nom :Dounia - Professeion : Assistant
                                Nom :Omar - Professeion : Ingenieur
                                Nom :Mostafa - Professeion : Ingenieur
                                Nom :Moneim - Professeion : Ingenieur
                                                                    

Fermer le curseur

Fermer le curseur signifie libérer la mémoire allouée.

Syntaxe

                                CLOSE nom_curseur; 
                            

Comme nous l'avons vu jusqu'à présent, un curseur explicite doit être ouvert avant la manipulation, puis fermé lorsque nous avons terminé. il existe un moyen de manipuler le curseur et de laisser l'oracle faire le reste, en utilisant la boucle for :

Syntaxe

FOR nom_record IN nom_curseur LOOP
	-- traitements
END LOOP; 
                            

  Exemple :

DECLARE  
   CURSOR liste_emp IS SELECT * FROM Employes;
   record_emp Employes%rowtype;
BEGIN 
   FOR record_emp IN liste_emp LOOP
        -- Traitements sur la ligne courante
        dbms_output.put_line('Nom :'|| record_emp.nom || ' - Professeion : '|| record_emp.profession);
   END LOOP;      
END;