Article
03/31/2012

Curseurs Transact-SQL

Les curseurs Transact-SQL sont principalement utilisés dans les procédures stockées, les déclencheurs et les scripts Transact-SQL, où ils permettent à d'autres instructions Transact-SQL d'accéder au contenu d'un ensemble de résultats.

En général, pour utiliser un curseur Transact-SQL dans une procédure stockée ou un déclencheur, procédez comme suit :

Déclarez les variables Transact-SQL devant contenir les données retournées par le curseur. Déclarez une variable pour chaque colonne de l'ensemble de résultats. Déclarez des variables suffisamment importantes pour contenir les valeurs retournées par la colonne et dont le type de données peut être converti implicitement à partir du type de données de la colonne.
Associez un curseur Transact-SQL à une instruction SELECT à l'aide de l'instruction DECLARE CURSOR. L'instruction DECLARE CURSOR définit également les caractéristiques du curseur, comme son nom, et indique s'il s'agit d'un curseur en lecture seule ou d'un curseur avant uniquement.
Utilisez l'instruction OPEN pour exécuter l'instruction SELECT et remplir le curseur.
Utilisez l'instruction FETCH INTO pour extraire des lignes individuelles et placer les données de chaque colonne dans une variable spécifiée. Les autres instructions Transact-SQL peuvent ensuite référencer ces variables pour accéder aux valeurs des données extraites. Les curseurs Transact-SQL ne permettent pas de rechercher des blocs de lignes.
Lorsque vous avez terminé d'utiliser le curseur, utilisez l'instruction CLOSE. La fermeture d'un curseur libère des ressources, comme l'ensemble de résultats du curseur et ses verrous sur la ligne en cours. Cependant, la structure du curseur vous permet encore de procéder à un nouveau traitement si vous réutilisez une instruction OPEN. Étant donné que le curseur est encore présent, vous ne pouvez pas réutiliser son nom à ce stade. L'instruction DEALLOCATE libère entièrement toutes les ressources allouées au curseur, y compris son nom. Après avoir désalloué un curseur, utilisez l'instruction DECLARE pour reconstruire le curseur.

Analyse de l'activité des curseurs Transact-SQL

Vous pouvez utiliser la procédure stockée système sp_cursor_list pour obtenir la liste des curseurs visibles pour la connexion actuelle, ainsi que la procédure stockée sp_describe_cursor, sp_describe_cursor_columns et sp_describe_cursor_tables pour déterminer les caractéristiques d'un curseur.

Lorsqu'un curseur est ouvert, la fonction @@CURSOR_ROWS ou la colonne cursor_rows retournée par sp_cursor_list ou sp_describe_cursor indique le nombre de lignes contenues dans ce curseur.

Après chaque instruction FETCH, @@FETCH_STATUS est mis à jour pour refléter l'état de la dernière extraction. Vous pouvez également obtenir ces informations d'état à partir de la colonne fetch_status retournée par sp_describe_cursor. @@FETCH_STATUS indique des conditions, par exemple l'extraction au-delà de la première ou de la dernière ligne du curseur. @@FETCH_STATUS est générale pour votre connexion, et elle est réinitialisée par chaque extraction effectuée sur un curseur ouvert pour la connexion. S'il vous faut par la suite connaître l'état, sauvegardez @@FETCH_STATUS dans une variable utilisateur avant d'exécuter une autre instruction sur la connexion. Même si l'instruction suivante n'est pas FETCH, il peut s'agir d'une instruction INSERT, UPDATE ou DELETE qui active un déclencheur contenant des instructions FETCH qui réinitialisent @@FETCH_STATUS. La colonne fetch_status retournée par sp_describe_cursor est propre au curseur spécifié et n'est pas affectée par les instructions FETCH qui font référence à d'autres curseurs. Cependant, les instructions FETCH qui font référence au même curseur affectent sp_describe_cursor ; vous devez donc l'utiliser avec précaution.

Lorsque l'exécution d'une instruction FETCH est terminée, le curseur est positionné sur la ligne extraite. La ligne extraite est appelée ligne courante. Si le curseur n'a pas été déclaré en tant que curseur en lecture seule, vous pouvez exécuter une instruction UPDATE ou DELETE avec une clause WHERE CURRENT OF cursor_name pour modifier la ligne courante.

Le nom attribué à un curseur Transact-SQL par l'instruction DECLARE CURSOR peut être global ou local. Les noms des curseurs globaux sont référencés par un traitement d'instructions, une procédure stockée ou un déclencheur en cours d'exécution sur la même connexion. Les noms de curseurs locaux ne peuvent pas être référencés en dehors du traitement d'instructions, de la procédure stockée ou du déclencheur dans lequel le curseur est déclaré. Les curseurs locaux contenus dans les déclencheurs et les procédures stockées sont par conséquent protégés contre toute référence non souhaitée en dehors de la procédure stockée ou du déclencheur.

Utilisation de la variable cursor

Microsoft SQL Server prend également en charge les variables dont le type de données est cursor. Vous pouvez associer un curseur à une variable cursor à l'aide de l'une des deux méthodes suivantes :

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR;

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;

SET @MyVariable = MyCursor;
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR;

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;
DEALLOCATE MyCursor;

Après avoir associé un curseur à une variable cursor, vous pouvez utiliser celle-ci à la place du nom du curseur dans les instructions de curseur Transact-SQL. Les paramètres de sortie des procédures stockées peuvent également se voir affecter un type de données cursor et être associés à un curseur. Les procédures stockées peuvent ainsi contrôler les curseurs locaux qu'elles contiennent.

Référencement de curseurs Transact-SQL

Les variables et les noms de curseur Transact-SQL sont référencés exclusivement par des instructions Transact-SQL ; ils ne sont pas référencés par les fonctions API de OLE DB, ODBC et ADO. Par exemple, si vous utilisez DECLARE CURSOR et OPEN pour ouvrir un curseur Transact-SQL, vous ne pouvez pas utiliser les fonctions ODBC SQLFetch ou SQLFetchScroll pour extraire une ligne du curseur Transact-SQL. Les applications nécessitant l'utilisation d'un curseur et faisant appel à ces API doivent utiliser le support de curseur intégré dans les API de bases de données à la place des curseurs Transact-SQL.

Vous pouvez recourir aux curseurs Transact-SQL dans des applications en utilisant l'instruction FETCH et en liant chaque colonne retournée par l'instruction FETCH à une variable de programme. Néanmoins, l'instruction FETCH de Transact-SQL ne gère pas les traitements d'instructions. Il s'agit donc du moyen le moins efficace pour retourner des données à une application. L'extraction de chaque ligne nécessite un aller-retour vers le serveur. Il est préférable d'utiliser la fonctionnalité de curseur intégrée dans les API de bases de données qui prennent en charge l'extraction de traitements de lignes.

Les curseurs Transact-SQL sont particulièrement efficaces lorsqu'ils sont contenus dans des procédures stockées et des déclencheurs. En effet, chaque instruction est compilée dans un plan d'exécution sur le serveur et il n'y a pas de trafic réseau associé à l'extraction de lignes.

Curseurs Transact-SQL et options SET

Dans SQL Server, une erreur est générée si une instruction FETCH est émise lorsque des valeurs sont modifiées au niveau de l'heure d'ouverture du curseur. Cette erreur se produit pour toutes les options suivantes affectant le plan, ou pour les options nécessaires pour les vues indexées et les colonnes calculées. Pour éviter cette erreur, ne modifiez pas les options SET lorsqu'un curseur est ouvert.

Options qui affectent le plan

ARITHABORT

NUMERIC_ROUNDABORT

FORCEPLAN

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

ANSI_WARNINGS

ANSI_PADDING

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

DATEFIRST

DATEFORMAT

LANGUAGE

TEXTSIZE

Vues indexées et colonnes calculées