Curseur PL / SQL

Résumé: dans ce tutoriel, vous découvrirez le curseur PL / SQL et son utilisation.

Un curseur est un pointeur qui pointe vers le résultat d’une requête. PL / SQL a deux types de curseurs: les curseurs implicites et les curseurs explicites.

Curseurs implicites

Chaque fois qu’Oracle exécute une instruction SQL telle que SELECT INTO, INSERT, UPDATE et DELETE, il crée automatiquement un curseur implicite.

Oracle gère en interne tout le cycle d’exécution des curseurs implicites et ne révèle que les informations et les statuts du curseur tels que SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND et SQL%NOTFOUND.

Le curseur implicite n’est pas élégant lorsque la requête renvoie zéro ou plusieurs lignes qui provoquent respectivement une exception NO_DATA_FOUND ou TOO_MANY_ROWS.

Curseurs explicites

Un curseur explicite est une instruction SELECT déclarée explicitement dans la section déclaration du bloc courant ou dans une spécification de package.

Pour un curseur explicite, vous avez cont rol sur son cycle d’exécution de OPEN, FETCH et CLOSE.

Oracle définit un cycle d’exécution qui exécute une instruction SQL et lui associe un curseur.

L’illustration suivante montre le cycle d’exécution d’un curseur explicite:

Examinons chaque étape en détail.

Déclarons un curseur

Avant d’utiliser un curseur explicite, vous devez le déclarer dans la section déclaration d’un bloc ou package comme suit:

Dans cette syntaxe :

• Tout d’abord, spécifiez le nom du curseur après le mot-clé CURSOR.
• Deuxièmement, définissez une requête pour récupérer les données après le mot-clé IS.

Ouvrir un curseur

Avant de commencer à récupérer les lignes du curseur, vous devez l’ouvrir. Pour ouvrir un curseur, vous utilisez la syntaxe suivante:

Dans cette syntaxe, cursor_name est le nom du curseur déclaré dans la section de déclaration.

Lorsque vous ouvrez un curseur, Oracle analyse la requête, lie les variables et exécute l’instruction SQL associée.

Oracle détermine également un plan d’exécution , associe les variables hôtes et les paramètres du curseur aux espaces réservés dans l’instruction SQL, détermine le jeu de résultats et définit le curseur sur la première ligne du jeu de résultats.

En savoir plus sur le curseur paramétré dans le didacticiel suivant.

Récupérer depuis un curseur

L’instruction FETCH place le contenu de la ligne courante dans des variables. La syntaxe de l’instruction FETCH est la suivante:

Pour récupérer toutes les lignes d’un jeu de résultats, vous devez pour récupérer chaque ligne jusqu’à la dernière.

Fermer un curseur

Après avoir récupéré toutes les lignes, vous devez fermer le curseur avec CLOSE instruction:

La fermeture d’un curseur indique à Oracle de libérer la mémoire allouée à un moment approprié.

Si vous déclarez un curseur dans un bloc, procédure ou fonction anonyme, le curseur se fermera automatiquement à la fin de l’exécution de ces objets.

Cependant, vous devez fermer explicitement les curseurs basés sur des packages. Notez que si vous fermez un curseur qui ne s’est pas encore ouvert, Oracle lèvera une exception INVALID_CURSOR.

Attributs de curseur explicites

Un curseur comporte quatre attributs auxquels vous pouvez vous référer au format suivant:

où cursor_name est le nom du curseur explicite.

1)% ISOPEN

Cet attribut est TRUE si le curseur est ouvert ou FALSE si ce n’est pas le cas.

2)% FOUND

Cet attribut a quatre valeurs:

• NULL avant la première extraction
• TRUE si un enregistrement a été récupéré avec succès
• FALSE si aucune ligne n’est renvoyée
• INVALID_CURSOR si le curseur n’est pas ouvert

3)% NOTFOUND

Cet attribut a quatre valeurs:

• NULL avant la première extraction
• FALSE si un enregistrement a été récupéré avec succès
• TRUE si aucune ligne n’est renvoyée
• INVALID_CURSOR si le curseur n’est pas ouvert

3)% ROWCOUNT

L’attribut %ROWCOUNT renvoie le nombre de lignes extraites du curseur. Si le curseur n’est pas ouvert, cet attribut renvoie INVALID_CURSOR.

Exemple de curseur PL / SQL

Nous utiliserons les tables orders et order_items de l’exemple de base de données pour la démonstration.

L’instruction suivante crée une vue qui renvoie les revenus des ventes par les clients:

Les valeurs de la colonne de crédit représentent 5% des revenus totaux des ventes.

Supposons que vous deviez développer une bloquer cela:

1. Réinitialisez les limites de crédit de tous les clients à zéro.
2. Récupérez les clients triés par ventes par ordre décroissant et leur attribuez de nouvelles limites de crédit à partir d’un budget de 1 million.

Le bloc anonyme suivant illustre la logique:

Dans la section déclaration, nous déclarons trois variables.

La première est l_budget dont la valeur initiale est 1,000,000.

La deuxième variable est une variable de curseur explicite nommée c_sales dont récupère les données de la vue sales:

La troisième variable est une enregistrement basé sur le curseur nommé c_sales.

Dans la section exécution, nous procédons comme suit:

• Tout d’abord, réinitialisez les limites de crédit de tous les clients à zéro à l’aide d’une instruction UPDATE.
• Deuxièmement, ouvrez le curseur c_sales.
• Troisièmement, récupérez chaque ligne du curseur. À chaque itération de boucle, nous mettons à jour la limite de crédit et réduisons le budget. La boucle se termine lorsqu’il n’y a pas de ligne à récupérer ou que le budget est épuisé.
• Enfin, fermez le curseur.

La requête suivante récupère les données de customers table de vérification de la mise à jour:

Résultat:

Comme vous pouvez le voir clairement sur le résultat, seuls les premiers clients ont les limites de crédit. Si vous additionnez toutes les limites de crédit, le total doit être de 1 million comme indiqué ci-dessous:

Maintenant , vous devez comprendre les curseurs PL / SQL, y compris les curseurs implicites et explicites, et comment les utiliser efficacement pour traiter des données, ligne par ligne, à partir d’une table.