DECLARE CURSOR (Transact-SQL) (Français)

• 14/03/2017
• 13 minutes de lecture
• • c
  • r
  • i
  • M
  • m
  • +6

Sapplique à: SQL Server (toutes les versions prises en charge ) Azure SQL Database

Définit les attributs dun curseur de serveur Transact-SQL, tels que son comportement de défilement et la requête utilisée pour créer le jeu de résultats sur lequel le curseur fonctionne. DECLARE CURSOR accepte à la fois une syntaxe basée sur la norme ISO et une syntaxe utilisant un ensemble dextensions Transact-SQL.

Conventions de syntaxe Transact-SQL

Syntaxe

Arguments

nom_curseur
Est le nom du curseur du serveur Transact-SQL défini. nom_curseur doit être conforme aux règles des identificateurs.

INSENSITIVE
Définit un curseur qui fait une copie temporaire des données à utiliser par le curseur. Toutes les requêtes adressées au curseur sont traitées depuis cette table temporaire dans tempdb; par conséquent, les modifications apportées aux tables de base ne sont pas reflétées dans les données renvoyées par les extractions effectuées sur ce curseur, et ce curseur ne permet pas de modifications. Lorsque la syntaxe ISO est utilisée, si INSENSITIVE est omis, les suppressions validées et les mises à jour apportées aux tables sous-jacentes (par nimporte quel utilisateur) sont reflétées dans les extractions suivantes.

Spécifie que toutes les options de récupération (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) sont disponibles. Si SCROLL nest pas spécifié dans une image ISO DECLARE CURSOR, NEXT est la seule option de récupération prise en charge . SCROLL ne peut pas être spécifié si FAST_FORWARD est également spécifié. Si SCROLL nest pas spécifié, seule loption de récupération NEXT est disponible et le curseur devient FORWARD_ONLY.

select_statement
Est une instruction SELECT standard qui définit lensemble de résultats du curseur. Les mots clés FOR BROWSE et INTO ne sont pas autorisés dans linstruction select_statement dune déclaration de curseur.

SQL Server convertit implicitement le curseur vers un autre type si les clauses de select_statement sont en conflit avec la fonctionnalité du type de curseur demandé.

LIRE UNIQUEMENT
Empêche les mises à jour effectuées via ce curseur. Le curseur ne peut pas être référencé dans une clause WHERE CURRENT OF dans une instruction UPDATE ou DELETE. Cette option remplace la capacité par défaut dun curseur à être mis à jour.

nom_curseur
Est le nom du curseur du serveur Transact-SQL défini. nom_curseur doit être conforme aux règles des identificateurs.

LOCAL
Spécifie que la portée du curseur est locale au lot, à la procédure stockée ou au déclencheur dans lequel le curseur a été créé. Le nom du curseur nest valide que dans cette portée. Le curseur peut être référencé par des variables de curseur locales dans le paramètre batch, procédure stockée ou déclencheur, ou une procédure stockée OUTPUT. Un paramètre OUTPUT est utilisé pour renvoyer le curseur local au lot appelant, à la procédure stockée ou au déclencheur, qui peut affecter le paramètre à une variable de curseur pour référencer le curseur après la procédure stockée se termine. Le curseur est désalloué implicitement lorsque le lot, la procédure stockée ou le déclencheur se termine, sauf si le curseur a été renvoyé dans un paramètre OUTPUT. Sil est renvoyé dans un paramètre OUTPUT, le curseur est désalloué lorsque la dernière variable le référençant est désallouée ou sort du champ dapplication.

GLOBAL
Spécifie que la portée du curseur est globale à la connexion. Le nom du curseur peut être référencé dans nimporte quelle procédure stockée ou lot exécuté par la connexion. Le curseur nest désalloué implicitement quà la déconnexion.

FORWARD_ONLY
Spécifie que le curseur ne peut avancer et faire défiler que du premier au dernier rang. FETCH NEXT est la seule option de récupération prise en charge. Toutes les instructions dinsertion, de mise à jour et de suppression effectuées par lutilisateur actuel (ou validées par dautres utilisateurs) qui affectent les lignes du jeu de résultats sont visibles lorsque les lignes sont extraites.Étant donné que le curseur ne peut pas faire défiler vers larrière, les modifications apportées aux lignes de la base de données après lextraction de la ligne ne sont pas visibles via le curseur. Les curseurs avant uniquement sont dynamiques par défaut, ce qui signifie que toutes les modifications sont détectées lors du traitement de la ligne actuelle. Cela permet une ouverture plus rapide du curseur et permet au jeu de résultats dafficher les mises à jour apportées aux tables sous-jacentes. Alors que les curseurs avant uniquement ne prennent pas en charge le défilement vers larrière, les applications peuvent revenir au début du jeu de résultats en fermant et en rouvrant le curseur.Si FORWARD_ONLY est spécifié sans STATIC, KEYSET, ou DYNAMIC, le curseur fonctionne comme un curseur dynamique. Lorsque ni FORWARD_ONLY ni SCROLL nest spécifié, FORWARD_ONLY est la valeur par défaut, sauf si les mots clés STATIC, KEYSET ou DYNAMIC sont spécifiés. Les curseurs STATIC, KEYSET et DYNAMIC sont définis par défaut sur SCROLL. Contrairement aux API de base de données telles que ODBC et ADO, FORWARD_ONLY est pris en charge avec STATIC, KEYSET, et DYNAMIC Curseurs Transact-SQL.

STATIC
Spécifie que le curseur affiche toujours le jeu de résultats tel quil était lors de sa première ouverture, et rend une copie temporaire des données à utiliser par le curseur. Toutes les requêtes adressées au curseur sont traitées à partir de cette table temporaire dans tempdb. Par conséquent, les insertions, les mises à jour et les suppressions apportées aux tables de base ne sont pas reflétées dans les données renvoyées par les extractions effectuées sur ce curseur, et ce curseur ne détecte pas les modifications apportées à lappartenance, à lordre ou aux valeurs du jeu de résultats après louverture du curseur . Les curseurs statiques peuvent détecter leurs propres mises à jour, suppressions et insertions, bien quils ne soient pas obligés de le faire. Par exemple, supposons quun curseur statique récupère une ligne et quune autre application met alors à jour cette ligne. Si lapplication récupère la ligne à partir du curseur statique, les valeurs quelle voit restent inchangées, malgré les modifications apportées par lautre application. Tous les types de défilement sont pris en charge.

KEYSET
Spécifie que lappartenance et lordre des lignes dans le curseur sont fixes lorsque le curseur est ouvert. Lensemble de clés qui identifient de manière unique les lignes est intégré dans une table dans tempdb connue sous le nom de keyset. Ce curseur fournit des fonctionnalités entre un curseur statique et un curseur dynamique dans sa capacité à détecter les changements. Tout comme un curseur statique, il ne détecte pas toujours les changements dappartenance et dordre du jeu de résultats. Comme un curseur dynamique, il détecte les modifications des valeurs des lignes dans le jeu de résultats. Les curseurs pilotés par le jeu de clés sont contrôlés par un ensemble didentificateurs uniques (clés) appelés jeu de clés. Les clés sont construites à partir dun ensemble de colonnes qui identifient de manière unique les lignes du jeu de résultats. Lensemble de clés est lensemble des valeurs de clé de toutes les lignes renvoyées par linstruction de requête. Avec les curseurs pilotés par jeu de clés, une clé est créée et enregistrée pour chaque ligne du curseur et stockée sur le poste de travail client ou sur le serveur. Lorsque vous accédez à chaque ligne, la clé stockée est utilisée pour récupérer les valeurs de données actuelles à partir de la source de données. Dans un curseur piloté par jeu de clés, lappartenance au jeu de résultats est gelée lorsque le jeu de clés est entièrement rempli. Par la suite, les ajouts ou mises à jour qui affectent lappartenance ne font pas partie de lensemble de résultats jusquà ce quil soit rouvert. >

• Si une ligne est supprimée, une tentative de récupération de la ligne renvoie un @@FETCH_STATUS de -2 car la ligne supprimée apparaît comme un espace dans le jeu de résultats. La clé de la ligne existe dans le jeu de clés, mais la ligne nexiste plus dans le jeu de résultats.
• Les insertions effectuées à lextérieur du curseur (par dautres processus) ne sont visibles que si le curseur est fermé et rouvert. Les insertions effectuées depuis lintérieur du curseur sont visibles à la fin de lensemble de résultats.
• Les mises à jour des valeurs clés depuis lextérieur du curseur ressemblent à une suppression de lancienne ligne suivie dune insertion de la nouvelle ligne. La ligne avec les nouvelles valeurs nest pas visible et les tentatives de récupération de la ligne avec les anciennes valeurs renvoient un @@FETCH_STATUS de -2. Les nouvelles valeurs sont visibles si la mise à jour se fait via le curseur en spécifiant la clause WHERE CURRENT OF.

DYNAMIQUE
Définit un curseur qui reflète toutes les modifications de données apportées à les lignes de son jeu de résultats lorsque vous faites défiler le curseur et récupérez un nouvel enregistrement, que les modifications se produisent depuis lintérieur du curseur ou par dautres utilisateurs en dehors du curseur. Par conséquent, toutes les instructions dinsertion, de mise à jour et de suppression effectuées par tous les utilisateurs sont visibles via le curseur.Les valeurs des données, lordre et lappartenance des lignes peuvent changer à chaque extraction. Loption de récupération ABSOLUTE nest pas prise en charge avec les curseurs dynamiques. Les mises à jour effectuées en dehors du curseur ne sont pas visibles tant quelles ne sont pas validées (sauf si le niveau disolement de transaction du curseur est défini sur UNCOMMITTED). Par exemple, supposons quun curseur dynamique récupère deux lignes et une autre application, puis met à jour lune de ces lignes et supprime lautre. Si le curseur dynamique récupère ensuite ces lignes, il ne trouvera pas la ligne supprimée, mais il affichera les nouvelles valeurs pour la ligne mise à jour.

FAST_FORWARD
Spécifie un FORWARD_ONLY, READ_ONLY curseur avec optimisations des performances activées. FAST_FORWARD ne peut pas être spécifié si SCROLL ou FOR_UPDATE est également spécifié. Ce type de curseur ne permet pas de modifier les données depuis lintérieur du curseur.

READ_ONLY
Empêche les mises à jour effectuées via ce curseur. Le curseur ne peut pas être référencé dans une clause WHERE CURRENT OF dans une instruction UPDATE ou DELETE. Cette option remplace la capacité par défaut dun curseur à être mis à jour.

SCROLL_LOCKS
Spécifie que les mises à jour ou les suppressions positionnées effectuées via le curseur sont assurées de réussir. SQL Server verrouille les lignes lors de leur lecture dans le curseur pour garantir leur disponibilité pour des modifications ultérieures. SCROLL_LOCKS ne peut pas être spécifié si FAST_FORWARD ou STATIC est également spécifié.

OPTIMISTIC
Spécifie que les mises à jour ou les suppressions positionnées effectuées via le curseur échouent si la ligne a été mise à jour depuis quelle a été lue dans le curseur. SQL Server ne verrouille pas les lignes lorsquelles sont lues dans le curseur. Il utilise à la place des comparaisons de valeurs de colonne dhorodatage, ou une valeur de somme de contrôle si la table na pas de colonne dhorodatage, pour déterminer si la ligne a été modifiée après avoir été lue dans le curseur. Si la ligne a été modifiée, la tentative de mise à jour ou de suppression positionnée échoue. OPTIMISTIC ne peut pas être spécifié si FAST_FORWARD est également spécifié.

TYPE_WARNING
Spécifie quun message davertissement est envoyé au client lorsque le curseur est implicitement converti du type demandé en un autre.

select_statement
Est une instruction SELECT standard qui définit lensemble de résultats du curseur. Les mots clés COMPUTE, COMPUTE BY, FOR BROWSE et INTO ne sont pas autorisés dans select_statement dune déclaration de curseur.

SQL Server convertit implicitement le curseur en un autre type si les clauses de select_statement sont en conflit avec la fonctionnalité du type de curseur demandé. Pour plus dinformations, consultez Conversions implicites du curseur.

Remarques

DECLARE CURSOR définit les attributs dun curseur de serveur Transact-SQL, comme son comportement de défilement et la requête utilisée pour créer le jeu de résultats sur lequel opère le curseur. Linstruction OPEN remplit lensemble de résultats et FETCH renvoie une ligne de lensemble de résultats. Linstruction CLOSE libère le jeu de résultats actuel associé au curseur. Linstruction DEALLOCATE libère les ressources utilisées par le curseur.

La première forme de linstruction DECLARE CURSOR utilise lISO syntaxe pour déclarer les comportements du curseur. La deuxième forme de DECLARE CURSOR utilise les extensions Transact-SQL qui vous permettent de définir des curseurs en utilisant les mêmes types de curseur que ceux utilisés dans les fonctions de curseur de lAPI de base de données ODBC ou ADO.

Vous ne pouvez pas mélanger les deux formes. Si vous spécifiez les mots clés SCROLL ou INSENSITIVE avant le mot clé CURSOR, vous ne pouvez en utiliser aucun mots clés entre les mots clés CURSOR et FOR <select_statement>. Si vous spécifiez des mots clés entre les mots clés CURSOR et FOR <select_statement>, vous ne pouvez pas spécifier SCROLL ou INSENSITIVE avant le mot clé CURSOR.

Si un DECLARE CURSOR utilisant la syntaxe Transact-SQL ne spécifie pas READ_ONLY, OPTIMISTIC, ou SCROLL_LOCKS, la valeur par défaut est la suivante:

• Si le SELECT ne prend pas en charge les mises à jour (autorisations insuffisantes, accès aux tables distantes qui ne prennent pas en charge les mises à jour, etc.), le curseur est READ_ONLY.

• STATIC et FAST_FORWARD curseurs par défaut sur READ_ONLY.

• DYNAMIC et KEYSET les curseurs par défaut sont OPTIMISTIC .

Les noms de curseur ne peuvent être référencés que par dautres instructions Transact-SQL. Ils ne peuvent pas être référencés par les fonctions de lAPI de base de données. Par exemple, après avoir déclaré un curseur, le nom du curseur ne peut pas être référencé à partir de fonctions ou méthodes OLE DB, ODBC ou ADO. Les lignes de curseur ne peuvent pas être récupérées à laide des fonctions ou des méthodes dextraction des API; les lignes ne peuvent être extraites que par des instructions Transact-SQL FETCH.

Une fois quun curseur a été déclaré, ces procédures stockées système peuvent être utilisées pour déterminer les caractéristiques du curseur.

Procédures stockées système	Description
sp_cursor_list	Renvoie une liste des curseurs actuellement visibles sur la connexion et leurs attributs.
sp_describe_cursor	Décrit les attributs dun curseur, par exemple sil sagit dun curseur avant uniquement ou défilant.
sp_describe_cursor_columns	Décrit les attributs des colonnes dans le jeu de résultats du curseur.
sp_describe_cursor_tables	Décrit les tables de base consultées par le curseur.

Les variables peuvent être utilisées dans le cadre de linstruction select_statement qui déclare un curseur. Les valeurs des variables du curseur ne changent pas après la déclaration dun curseur.

Autorisations

Autorisations de DECLARE CURSOR par défaut pour tout utilisateur qui a SELECT autorisations sur les vues, tables et colonnes utilisées dans le curseur.

Limitations et restrictions

Vous ne pouvez pas utiliser de curseurs ou de déclencheurs sur une table avec un index columnstore en cluster. Cette restriction ne sapplique pas aux index columnstore non clusterisés; vous pouvez utiliser des curseurs et des déclencheurs sur une table avec un index columnstore non clusterisé.

Exemples

A. Utilisation dun curseur et dune syntaxe simples

Le jeu de résultats généré à louverture de ce curseur comprend toutes les lignes et toutes les colonnes du tableau. Ce curseur peut être mis à jour et toutes les mises à jour et suppressions sont représentées dans les extractions effectuées sur ce curseur. FETCH NEXT est la seule extraction disponible car loption SCROLL na pas été spécifiée.

B. Utilisation de curseurs imbriqués pour produire une sortie de rapport

Lexemple suivant montre comment les curseurs peuvent être imbriqués pour produire des rapports complexes. Le curseur interne est déclaré pour chaque fournisseur.

Voir aussi