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

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

⚡ Résumé intelligent

Curseurs dans Oracle En PL/SQL, les curseurs pointent vers la zone de contexte qui contient les lignes renvoyées par une instruction SQL. Il en existe deux types : les curseurs implicites, créés automatiquement pour les opérations DML, et les curseurs explicites, déclarés et contrôlés par le programmeur.

  • 📍 Zone de contexte : Un curseur pointe vers la zone de contexte qui stocke une instruction SQL et son ensemble actif renvoyé.
  • ⚙️ Curseur implicite : Oracle ouvre automatiquement un curseur implicite pour chaque instruction DML et SELECT INTO sur une seule ligne.
  • ✋ Curseur explicite : Un programmeur déclare, ouvre, récupère et ferme un curseur explicite pour obtenir un contrôle total.
  • ???? Attributs du curseur : %FOUND, %NOTFOUND, %ISOPEN et %ROWCOUNT indiquent l'état de l'opération la plus récente.
  • (I.e. Boucle FOR avec curseur : Une boucle FOR ouvre, récupère et ferme un curseur implicitement, sans nécessiter d'intervention manuelle.
  • 🤖 Assistance IA : Les assistants IA tels que GitHub Copilot bloquent les boucles de curseur et signalent les curseurs non fermés.
En savoir plus

Oracle Courant PL/SQL implicite, explicite et boucle FOR

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

Un curseur est un pointeur vers la zone de contexte. Oracle crée une zone de contexte pour le traitement d'un SQL déclaration, et cette zone contient toutes les informations relatives à la déclaration.

PL / SQL Le curseur permet au programmeur de contrôler la zone de contexte. Il contient les lignes renvoyées par l'instruction SQL, et l'ensemble de ces lignes est appelé ensemble actif. Ces curseurs peuvent être nommés afin de pouvoir y faire référence depuis un autre endroit du code.

Le curseur est de deux types :

  • Curseur implicite
  • Curseur explicite

Curseur implicite

Chaque fois que Opération DML Lorsqu'une opération a lieu dans la base de données, un curseur implicite est créé, contenant les lignes affectées par cette opération. Ces curseurs ne peuvent pas être nommés et, par conséquent, ne peuvent être ni contrôlés ni référencés depuis un autre endroit du code. Seul le curseur le plus récent peut être consulté via ses attributs.

Curseur explicite

Les programmeurs peuvent créer une zone de contexte nommée pour exécuter leurs opérations DML et en obtenir un meilleur contrôle. Le curseur explicite doit être défini dans la section de déclaration du Bloc PL/SQLet elle est créée pour l'instruction SELECT qui doit être utilisée dans le code.

Voici les étapes à suivre pour travailler avec des curseurs explicites :

  • Déclaration du curseur : Déclarer le curseur revient 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 de contexte est identique à celui du curseur.
  • Ouverture du curseur : L'ouverture du curseur indique à PL/SQL d'allouer la mémoire nécessaire à ce curseur, le rendant ainsi prêt à extraire les enregistrements.
  • Récupération des données à partir du curseur : Dans ce processus, l'instruction SELECT est exécutée et les lignes extraites sont stockées dans la mémoire allouée. On les appelle alors des ensembles actifs. L'extraction de données à partir du curseur est une opération au niveau de l'enregistrement, ce qui signifie que nous pouvons accéder aux données enregistrement par enregistrement. Chaque instruction FITCH extrait un ensemble actif et contient les informations de cet enregistrement particulier. Cette instruction est identique à une instruction SELECT qui extrait l'enregistrement et l'affecte à la variable de la clause INTO, mais elle ne lèvera aucune exception. exceptions.
  • Fermeture du curseur : Une fois tous les enregistrements récupérés, nous devons fermer le curseur afin 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 déclaration contient la définition du curseur et de la variable de curseur qui recevra les données extraites. Le curseur est créé pour l'instruction SELECT spécifiée dans sa déclaration. L'exécution du curseur consiste à l'ouvrir, à y extraire les données, puis à le fermer.

ARTICLES LIÉS

Attributs du curseur

Le curseur implicite et le curseur explicite possèdent tous deux certains attributs accessibles. Ces attributs fournissent des informations supplémentaires 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É Renvoie la valeur booléenne TRUE si la dernière opération de récupération a permis de récupérer un enregistrement avec succès ; sinon, elle renvoie FALSE.
%PAS TROUVÉ Fonctionne à l'inverse de %FOUND. Renvoie TRUE si la dernière opération de récupération n'a permis de récupérer aucun enregistrement.
%EST OUVERT Renvoie la valeur booléenne TRUE si le curseur donné est déjà ouvert ; sinon, elle renvoie FALSE.
% ROWCOUNT Renvoie une valeur numérique indiquant le nombre réel d'enregistrements affectés ou récupérés par l'opération.

Exemple de curseur explicite : Dans cet exemple, nous verrons comment déclarer, ouvrir, récupérer et fermer un curseur explicite. Nous projetterons tous les noms des employés de la table « emp » à l'aide d'un curseur. Nous utiliserons également un attribut de curseur pour définir la boucle permettant de récupérer tous les enregistrements.

La capture d'écran ci-dessous montre cet exemple de curseur explicite et sa sortie. Oracle.

Exemple de curseur explicite récupérant les noms des employés de la table emp dans Oracle 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%ROWCOUNT);
CLOSE guru99_det;
END;
/

Sortie

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

Code Explication

  • Code ligne 2: Déclaration du curseur guru99_det pour l'instruction 'SELECT emp_name FROM emp'.
  • Code ligne 3: Déclarer la variable lv_emp_name avec le %type ancré à emp.emp_name.
  • Code ligne 5: Ouverture du curseur guru99_det.
  • Code ligne 6: Définition de l'instruction de boucle de base pour récupérer tous les enregistrements de la table emp.
  • Code ligne 7: Récupère les données guru99_det et attribue la valeur à lv_emp_name.
  • Code ligne 8: L'attribut %NOTFOUND du curseur permet de vérifier si tous les enregistrements ont été récupérés. Si c'est le cas, la fonction renvoie TRUE et l'exécution s'arrête ; sinon, elle continue à récupérer les données et les affiche.
  • Code ligne 10: Condition EXIT pour l'instruction de boucle.
  • Code ligne 12: Imprimez le nom de l'employé récupéré.
  • Code ligne 14: Utilisez l'attribut de curseur %ROWCOUNT pour trouver le nombre total d'enregistrements récupérés par le curseur.
  • Code ligne 15: Une fois la boucle terminée, le curseur est fermé et la mémoire allouée est libérée.

Instruction de curseur de boucle FOR

Un curseur Boucle POUR On peut utiliser la boucle FOR pour manipuler les curseurs. Au lieu de spécifier une plage de valeurs, on peut indiquer le nom du curseur afin que la boucle parcoure les enregistrements du premier au dernier. La variable curseur, son ouverture, sa lecture et sa fermeture sont gérées 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 déclaration contient la déclaration du curseur. Ce curseur est créé pour l'instruction SELECT spécifiée dans sa déclaration. Dans la partie exécution, le curseur déclaré est initialisé dans la boucle FOR, et la variable de boucle « I » joue alors le rôle de la variable de curseur.

Oracle Exemple de boucle avec curseur : Dans cet exemple, nous allons extraire tous les noms des 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

Code Explication

  • Code ligne 2: Déclaration du curseur guru99_det pour l'instruction 'SELECT emp_name FROM emp'.
  • Code ligne 4: Construction de la boucle FOR pour le curseur avec la variable de boucle lv_emp_name.
  • Code ligne 6: Impression du nom de l'employé à chaque itération de la boucle.
  • Code ligne 7: Sortir de la boucle (FIN DE BOUCLE).

À noter: Dans une boucle FOR avec curseur, les attributs du curseur ne peuvent pas être utilisés, car l'ouverture, la récupération et la fermeture du curseur sont effectuées implicitement par la boucle FOR.

FAQ

Un curseur de référence (REF CURSOR) est un pointeur vers un ensemble de résultats de requête. Contrairement à un curseur statique, il peut ouvrir différentes requêtes lors de l'exécution et transmettre des résultats entre des blocs PL/SQL ou à des programmes clients.

Un curseur normal récupère une ligne par FETCH, ce qui entraîne de nombreux changements de contexte. COLLECTE EN VRAC charge de nombreuses lignes dans une collection en une seule requête, réduisant considérablement la surcharge sur les grands ensembles de résultats.

Oui. Déclarez un curseur paramétré, par exemple CURSOR c(dept NUMBER) IS SELECT …, puis transmettez les valeurs à OPEN c(10). Les paramètres vous permettent de réutiliser une même définition de curseur avec différentes valeurs de filtre.

La clause FOR UPDATE verrouille les lignes sélectionnées par le curseur afin d'empêcher toute modification ultérieure. La clause WHERE CURRENT OF met ensuite à jour ou supprime la ligne exacte qui vient d'être extraite, sans répéter la condition WHERE.

Les curseurs ouverts conservent de la mémoire réservée et sont comptabilisés dans la limite OPEN_CURSORS. Laisser un grand nombre de curseurs ouverts finit par générer l'erreur ORA-01000 : nombre maximal de curseurs ouverts dépassé. Il est donc impératif de fermer explicitement un curseur après utilisation.

Chaque requête FETCH alterne entre les moteurs PL/SQL et SQL. Des milliers de ces changements de contexte s'accumulent, si bien qu'une seule instruction SQL ensembliste ou BULK COLLECT traite généralement les mêmes lignes beaucoup plus rapidement.

Oui. Copilote GitHub Ébauche des boucles OPEN, FETCH et CLOSE explicites ou des boucles FOR avec curseur à partir d'un commentaire, ajoute des vérifications de sortie %NOTFOUND et suggère des noms d'attributs, bien que vous deviez d'abord examiner la logique.

Les assistants IA signalent les boucles de curseur ligne par ligne susceptibles de se transformer en requêtes SQL ensemblistes ou en opérations BULK COLLECT, repèrent les curseurs non fermés et expliquent le comportement de l'attribut %attribute. Cette analyse par apprentissage automatique améliore les performances avant la mise en production du code.

Résumez cet article avec :