Oracle Curseur PL/SQL : implicite, explicite, boucle For avec exemple

Par : Fiona Brun Fiona Brun
Hours Le kit de préparation mis à jour

Qu’est-ce que CURSEUR en PL/SQL ?

Un curseur est un pointeur vers cette zone contextuelle. Oracle crée une zone de contexte pour le traitement d'une instruction SQL qui contient toutes les informations sur l'instruction.

PL/SQL permet au programmeur de contrôler la zone de contexte via le curseur. Un curseur contient les lignes renvoyées par l'instruction SQL. L’ensemble de lignes que contient le curseur est appelé ensemble actif. Ces curseurs peuvent également être nommés de manière à pouvoir être référencés depuis un autre endroit du code.

Le curseur est de deux types.

  • Curseur implicite
  • Curseur explicite

Curseur implicite

Chaque fois qu'une opération DML se produit dans la base de données, un curseur implicite est créé qui contient les lignes affectées dans cette opération particulière. Ces curseurs ne peuvent pas être nommés et, par conséquent, ils ne peuvent pas être contrôlés ou référencés depuis un autre endroit du code. Nous pouvons nous référer uniquement au curseur le plus récent via les attributs du curseur.

Curseur explicite

Les programmeurs sont autorisés à créer une zone de contexte nommée pour exécuter leurs opérations DML afin d'en obtenir plus de contrôle. Le curseur explicite doit être défini dans la section de déclaration du Bloc PL/SQL, et il est créé pour l'instruction 'SELECT' qui doit être utilisée dans le code.

Vous trouverez ci-dessous les étapes à suivre pour travailler avec des curseurs explicites.

  • Déclarer le curseur Déclarer le curseur signifie simplement créer une zone de contexte nommée pour l'instruction 'SELECT' définie dans la partie déclaration. Le nom de cette zone contextuelle est le même que le nom du curseur.
  • Curseur d'ouvertureL'ouverture du curseur indiquera au PL / SQL pour allouer la mémoire à ce curseur. Cela rendra le curseur prêt à récupérer les enregistrements.
  • Récupérer des données à partir du curseurDans ce processus, l'instruction « SELECT » est exécutée et les lignes récupérées sont stockées dans la mémoire allouée. Ceux-ci sont désormais appelés ensembles actifs. La récupération de données à partir du curseur est une activité au niveau de l'enregistrement, ce qui signifie que nous pouvons accéder aux données enregistrement par enregistrement. Chaque instruction fetch récupérera un ensemble actif et conservera les informations de cet enregistrement particulier. Cette instruction est identique à l'instruction « SELECT » qui récupère l'enregistrement et l'affecte à la variable dans la clause « INTO », mais elle ne lèvera aucune exception.
  • Fermeture du curseurUne fois que tous les enregistrements sont récupérés, nous devons fermer le curseur pour que la mémoire allouée à cette zone de contexte soit libérée.

Syntaxe

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;
  • Dans la syntaxe ci-dessus, la partie déclaration contient la déclaration du curseur et la variable du curseur dans laquelle les données récupérées seront affectées.
  • Le curseur est créé pour l'instruction 'SELECT' donnée dans la déclaration du curseur.
  • Dans la partie exécution, le curseur déclaré est ouvert, récupéré et fermé.

ARTICLES LIÉS

Attributs du curseur

Le curseur implicite et le curseur explicite possèdent tous deux certains attributs accessibles. Ces attributs donnent plus d'informations sur les opérations du curseur. Vous trouverez ci-dessous les différents attributs du curseur et leur utilisation.

Attribut du curseur Description
%A TROUVÉ Il renvoie le résultat booléen « VRAI » si l'opération de récupération la plus récente a récupéré un enregistrement avec succès, sinon il renverra FALSE.
%PAS TROUVÉ Cela fonctionne à l'opposé de %FOUND, il renverra 'TRUE' si l'opération de récupération la plus récente n'a pu récupérer aucun enregistrement.
%EST OUVERT Il renvoie le résultat booléen 'TRUE' si le curseur donné est déjà ouvert, sinon il renvoie 'FALSE'
% ROWCOUNT Il renvoie la valeur numérique. Il donne le nombre réel d'enregistrements affectés par l'activité DML.

Exemple de curseur explicite:

Dans cet exemple, nous allons voir comment déclarer, ouvrir, récupérer et fermer le curseur explicite.

Nous projetterons tous les noms de l'employé de la table emp à l'aide d'un curseur. Nous utiliserons également l'attribut du curseur pour définir la boucle afin qu'elle récupère tous les enregistrements du curseur.

Oracle Curseur PL/SQL

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;

LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/

Sortie

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

Explication du code

  • Ligne de code 2: Déclaration du curseur guru99_det pour l'instruction 'SELECT emp_name FROM emp'.
  • Ligne de code 3: Déclaration de la variable lv_emp_name.
  • Ligne de code 5: Ouverture du curseur guru99_det.
  • Ligne de code 6 : Définition de l'instruction de boucle de base pour récupérer tous les enregistrements de la table 'emp'.
  • Ligne de code 7 : Récupère les données guru99_det et attribue la valeur à lv_emp_name.
  • Ligne de code 9 : Utilisation de l'attribut de curseur '%NOTFOUND' pour savoir si tous les enregistrements du curseur sont récupérés. S'il est récupéré, il renverra « VRAI » et le contrôle quittera la boucle, sinon le contrôle continuera à récupérer les données du curseur et à imprimer les données.
  • Ligne de code 11 : Condition EXIT pour l'instruction de boucle.
  • Ligne de code 12 : Imprimez le nom de l'employé récupéré.
  • Ligne de code 14 : Utilisation de l'attribut de curseur '%ROWCOUNT' pour trouver le nombre total d'enregistrements affectés/récupérés dans le curseur.
  • Ligne de code 15 : Après la sortie de la boucle, le curseur est fermé et la mémoire allouée est libérée.

Instruction de curseur de boucle FOR

L'instruction «FOR LOOP» peut être utilisée pour travailler avec des curseurs. Nous pouvons donner le nom du curseur au lieu de la limite de plage dans l'instruction de boucle FOR afin que la boucle fonctionne du premier enregistrement du curseur au dernier enregistrement du curseur. La variable curseur, l'ouverture du curseur, la récupération et la fermeture du curseur seront effectués implicitement par la boucle FOR.

Syntaxe

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR I IN <cursor_name>
  LOOP
  .
  .
  END LOOP;
END;
  • Dans la syntaxe ci-dessus, la partie déclaration contient la déclaration du curseur.
  • Le curseur est créé pour l'instruction 'SELECT' donnée dans la déclaration du curseur.
  • En partie exécution, le curseur déclaré est configuré dans la boucle FOR et la variable de boucle 'I' se comportera comme variable de curseur dans ce cas.

Oracle Exemple de curseur pour une boucle:
Dans cet exemple, nous projetterons tous les noms d'employés de la table emp à l'aide d'une boucle curseur-FOR.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/

Sortie

Employee Fetched:BBB 
Employee Fetched:XXX
Employee Fetched:YYY

Explication du code

  • Ligne de code 2: Déclaration du curseur guru99_det pour l'instruction 'SELECT emp_name FROM emp'.
  • Ligne de code 4: Construction de la boucle 'FOR' pour le curseur avec la variable de boucle lv_emp_name.
  • Ligne de code 5 : Impression du nom de l'employé à chaque itération de la boucle.
  • Ligne de code 8 : Sortez de la boucle

Remarque: Dans la boucle Cursor-FOR, les attributs du curseur ne peuvent pas être utilisés puisque l'ouverture, la récupération et la fermeture du curseur se font implicitement par Boucle POUR.