Nous utilisons des cookies pour améliorer votre expérience. En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies.


Politique de confidentialité

Les curseurs en Oracle PL/SQL

Les curseurs en Oracle PL/SQL

Introduction

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 |
+----+---------+-----+----------+------------+------+ 
                            

Création et utilisations

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.

Curseurs implicites

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 :

AttributDescription
%FOUNDSa valeur de retour est TRUE si les instructions DML telles que INSERT, DELETE et UPDATE affectent au moins une ou plusieurs lignes ou si une instruction SELECT INTO a renvoyé une ou plusieurs lignes. Sinon, il renvoie FALSE.
%NOTFOUNDSa valeur de retour est TRUE si les instructions DML telles que INSERT, DELETE et UPDATE n'affectent aucune ligne ou si une instruction SELECT INTO ne renvoie aucune ligne. Sinon, il renvoie FALSE.
%ROWCOUNTIl renvoie le nombre de lignes affectées par les instructions DML telles que INSERT, DELETE et UPDATE, pour SELECT INTO, il renvoie le nombre de lignes traités par le curseur.
%ISOPENIl renvoie toujours FALSE pour les curseurs implicites, car le curseur SQL est automatiquement fermé après l'exécution de ses instructions SQL associées.
%BULK_ROWCOUNTUn attribut composite conçu pour être utilisé avec l'instruction FORALL. Cet attribut agit comme une table indexée. Son ième élément stocke le nombre de lignes traitées par la ième exécution d'une instruction UPDATE ou DELETE. Si la ième exécution n'affecte aucune ligne,%BULK_ROWCOUNT(i) renvoie zéro.
%BULK_EXCEPTIONSTableau associatif qui stocke des informations sur les exceptions rencontrées par une instruction FORALL qui utilise la clause SAVE EXCEPTIONS. Vous devez parcourir ses éléments pour déterminer où les exceptions se sont produites et ce qu'elles étaient. Pour chaque valeur d'index i comprise entre 1 et %BULK_EXCEPTIONS.COUNT, %BULK_EXCEPTIONS(i).ERROR_INDEX spécifie l'itération de la boucle FORALL qui a provoqué une exception. %BULK_EXCEPTIONS(i).ERROR_CODE spécifie le code d'erreur Oracle qui correspond à l'exception.
Le nom du curseur implicite est 'sql'.
  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 :

7 employés sélectionnés
  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 :

2 employés supprimés

Curseurs explicites

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; 
                            

Partager ce cours avec tes amis :

Rédigé par ESSADDOUKI Mostafa

The education of the 21st century opens up opportunities to not merely teach, but to coach, mentor, nurture and inspire.

Commentaire(s)