DECLARE CURSOR (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Définit les attributs d’un curseur de serveur Transact-SQL, notamment son comportement quant au défilement et la requête utilisée pour créer le jeu de résultats sur lequel le curseur opère. DECLARE CURSOR accepte à la fois une syntaxe basée sur la norme ISO et une syntaxe utilisant un ensemble d’extensions Transact-SQL.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe ISO :

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Syntaxe étendue Transact-SQL :

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ , ...n ] ] ]
[ ; ]

Arguments

cursor_name

Nom du curseur de serveur Transact-SQL défini. cursor_name doit suivre les règles applicables aux identificateurs.

INSENSITIVE

Définit un curseur qui effectue une copie temporaire des données qu'il doit utiliser. Toutes les demandes adressées au curseur sont répondues à partir de 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 retournées par des extractions effectuées sur ce curseur, et ce curseur n’autorise pas les modifications. Si, lors de l’utilisation de la syntaxe ISO, l’option INSENSITIVE est omise, les suppressions et les mises à jour validées effectuées (par n’importe quel utilisateur) dans les tables sous-jacentes sont reflétées dans les extractions ultérieures.

SCROLL

Spécifie que toutes les fonctions d’extraction (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) sont disponibles. Si SCROLL elle n’est pas spécifiée dans un iso DECLARE CURSOR, NEXT est la seule option fetch prise en charge. SCROLL ne peut pas être spécifié s’il FAST_FORWARD est également spécifié. Si SCROLL elle n’est pas spécifiée, seule l’option NEXT d’extraction est disponible et le curseur devient FORWARD_ONLY.

select_statement

Instruction standard SELECT qui définit le jeu de résultats du curseur. Les mot clé et INTO ne sont pas autorisées FOR BROWSEdans select_statement d’une déclaration de curseur.

SQL Server convertit implicitement le curseur en un autre type si les clauses de l’instruction select_statement sont incompatibles avec la fonctionnalité du type de curseur demandé.

READ ONLY

Interdit les mises à jour par l'intermédiaire de ce curseur. Le curseur ne peut pas être référencé dans une WHERE CURRENT OF clause d’une instruction ou DELETE d’une UPDATE instruction. Cette option remplace la possibilité par défaut de mise à jour d'un curseur.

UPDATE [ OF column_name [ ,...n ] ]

Définit les colonnes qui peuvent être mises à jour par le curseur. Si OF <column_name> [, <... n> ] elle est spécifiée, seules les colonnes répertoriées autorisent les modifications. Si vous spécifiez UPDATE sans liste de colonnes, toutes les colonnes peuvent être mises à jour.

cursor_name

Nom du curseur de serveur Transact-SQL défini. cursor_name doit suivre les règles applicables aux identificateurs.

LOCAL

Spécifie que l'étendue du curseur est locale pour le traitement d'instructions, la procédure stockée ou le déclencheur dans lequel il a été créé. Le nom du curseur n’est valide que dans cette étendue. Le curseur peut être référencé par des variables de curseur locales dans le traitement, la procédure stockée ou le déclencheur, ou bien par un paramètre OUTPUT d’une procédure stockée. Un paramètre OUTPUT est utilisé pour passer en retour le curseur local au traitement par lot, à la procédure stockée ou au déclencheur appelant, qui peut affecter le paramètre à une variable de curseur pour référencer le curseur à la fin de la procédure stockée. Le curseur est désalloué implicitement à la fin du traitement par lot, de la procédure stockée ou du déclencheur, à moins d’avoir été passé en retour dans un paramètre OUTPUT. S’il est repassé dans un OUTPUT paramètre, le curseur est désalloué lorsque la dernière variable référençant celle-ci est libérée ou sort de l’étendue.

GLOBAL

Spécifie que l'étendue du curseur est globale pour la connexion. Toute procédure stockée ou tout lot exécuté par la connexion peut faire référence au nom du curseur. Le curseur n'est désalloué implicitement qu'au moment de la déconnexion.

FORWARD_ONLY

Spécifie que le curseur peut seulement aller vers l’avant et défiler de la première à la dernière ligne. FETCH NEXT est la seule fonction d’extraction prise en charge. Toutes les instructions d’insertion, de mise à jour et de suppression effectuées par l’utilisateur actuel (ou validées par d’autres utilisateurs) qui affectent les lignes du jeu de résultats sont visibles à mesure que les lignes sont extraites. Étant donné que le curseur ne peut pas être fait défiler vers l’arrière, toutefois, les modifications apportées aux lignes de la base de données après l’extraction de la ligne ne sont pas visibles par 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 active. Cela accélère l’ouverture du curseur et permet au jeu de résultats d’afficher les mises à jour apportées aux tables sous-jacentes. Bien que les curseurs vers l’avant uniquement ne prennent pas en charge le défilement vers l’arrière, les applications peuvent revenir au début du jeu de résultats en fermant et en rouvrant le curseur.

Si vous spécifiez FORWARD_ONLY sans les mots clés STATIC, KEYSET ou DYNAMIC, le curseur fonctionne comme un curseur dynamique. Quand FORWARD_ONLY ou SCROLL ne sont pas spécifiés, FORWARD_ONLY est la valeur par défaut, sauf si les mot clé STATIC, KEYSETou DYNAMIC sont spécifiées. Les curseurs STATIC, KEYSET et DYNAMIC sont par défaut de type SCROLL. Contrairement aux API de base de données comme ODBC et ADO, FORWARD_ONLY est pris en charge avec les curseurs Transact-SQL STATIC, KEYSET et DYNAMIC.

STATIC

Spécifie que le curseur affiche toujours le jeu de résultats tel qu’il était quand le curseur a été ouvert pour la première fois, et effectue une copie temporaire des données à utiliser par le curseur. Toutes les demandes adressées au curseur sont répondues à partir de cette table temporaire dans tempdb. Par conséquent, les insertions, mises à jour et suppressions apportées aux tables de base ne sont pas reflétées dans les données retournées par des extractions effectuées sur ce curseur, et ce curseur ne détecte pas les modifications apportées à l’appartenance, à l’ordre ou aux valeurs du jeu de résultats une fois le curseur ouvert. Les curseurs statiques peuvent détecter leurs propres mises à jour, suppressions et insertions, bien qu’ils ne soient pas nécessaires pour le faire.

Par exemple, supposez qu’un curseur statique extrait une ligne et qu’une autre application met ensuite à jour cette ligne. Si l’application réextrait la ligne à partir du curseur statique, les valeurs qu’elle voit sont inchangées, malgré les modifications apportées par l’autre application. Tous les types de défilement sont pris en charge.

KEYSET

Spécifie que l'appartenance au curseur et l'ordre des lignes sont fixés lors de l'ouverture du curseur. L’ensemble de clés qui identifient de manière unique les lignes est intégré à une table tempdb appelée jeu de clés. Ce curseur fournit une fonctionnalité entre un curseur statique et un curseur dynamique grâce à sa capacité à détecter les modifications. Comme un curseur statique, il ne détecte pas toujours les modifications apportées à l’appartenance et à l’ordre du jeu de résultats. Comme un curseur dynamique, il détecte les modifications apportées aux valeurs des lignes dans le jeu de résultats.

Les curseurs de jeux de clés sont gérés par un ensemble d’identificateurs uniques (clés) appelé jeu de clés. Les clés sont créées à partir d'un ensemble de colonnes qui identifient uniquement les lignes de l'ensemble de résultats. Le jeu de clés est l’ensemble des valeurs de clés de toutes les lignes retournées par l’instruction de requête. Avec les curseurs de jeux de clés, une clé est générée et enregistrée pour chaque ligne du curseur et stockée sur la station de travail cliente ou sur le serveur. Quand vous accédez à chaque ligne, la clé stockée est utilisée pour extraire les valeurs de données actuelles de la source de données. Dans un curseur de jeux de clés, l’appartenance au jeu de résultats est figée quand le jeu de clés est plein. Par la suite, les ajouts ou mises à jour qui affectent l’appartenance ne font pas partie du jeu de résultats tant qu’ils ne sont pas rouverts.

Les modifications apportées aux valeurs de données (par le propriétaire du jeu de clés ou par d’autres processus) sont visibles à mesure que l’utilisateur fait défiler le jeu de résultats :

• Si une ligne est supprimée, une tentative d’extraction de la ligne renvoie une @@FETCH_STATUS valeur -2 due au fait que la ligne supprimée apparaît comme un écart dans le jeu de résultats. La clé pour la ligne existe dans le jeu de clés, mais la ligne n’existe plus dans le jeu de résultats.

• Les insertions effectuées en dehors du curseur (par d’autres processus) sont visibles uniquement si le curseur est fermé puis rouvert. Les insertions effectuées à partir de l’intérieur du curseur sont visibles à la fin du jeu de résultats.

• Les mises à jour de valeurs de clés effectuées hors du curseur sont semblables à la suppression de l'ancienne ligne suivie de l'insertion d'une nouvelle. La ligne avec les nouvelles valeurs n’est pas visible et tente d’extraire la ligne avec les anciennes valeurs retournent une valeur @@FETCH_STATUS .-2 Les nouvelles valeurs sont visibles si la mise à jour est effectuée via le curseur en spécifiant la clause WHERE CURRENT OF.

DYNAMIC

Définit un curseur qui reflète toutes les modifications de données apportées aux lignes dans son jeu de résultats à mesure que vous faites défiler le curseur et que vous extrayez un nouvel enregistrement, indépendamment du fait que les modifications se produisent à partir de l’intérieur du curseur ou qu’elles soient effectuées par d’autres utilisateurs en dehors du curseur. Par conséquent, toutes les instructions d’insertion, de mise à jour et de suppression émises par l’ensemble des utilisateurs sont visibles à l’aide du curseur. Les valeurs des données, l'ordre et l'appartenance aux lignes peuvent changer à chaque extraction. L’option ABSOLUTE fetch n’est pas prise en charge avec les curseurs dynamiques. Mises à jour effectuées en dehors du curseur ne sont pas visibles tant qu’elles ne sont pas validées (sauf si le niveau d’isolation des transactions du curseur est défini sur UNCOMMITTED).

Par exemple, supposons qu’un curseur dynamique récupère deux lignes, puis qu’une autre application met à jour l’une de ces lignes et supprime l’autre. Si le curseur dynamique récupère ensuite ces lignes, il ne trouve pas la ligne supprimée, mais affiche les nouvelles valeurs de la ligne mise à jour.

FAST_FORWARD

Spécifie un curseur FORWARD_ONLY, READ_ONLY pour lequel les optimisations de performances sont activées. FAST_FORWARD ne peut pas être spécifié si SCROLL ou FOR_UPDATE est également spécifié. Ce type de curseur n’autorise pas les modifications de données à partir du curseur.

READ_ONLY

Interdit les mises à jour par l'intermédiaire de ce curseur. Le curseur ne peut pas être référencé dans une WHERE CURRENT OF clause d’une instruction ou DELETE d’une UPDATE instruction. Cette option remplace la possibilité par défaut de mise à jour d'un curseur.

SCROLL_LOCKS

Spécifie que la réussite des mises à jour ou des suppressions positionnées effectuées via le curseur est garantie. SQL Server verrouille les lignes lorsqu’elles sont lues dans le curseur pour vérifier leur disponibilité lors 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 suppressions positionnées effectuées via le curseur ne réussissent pas, si la ligne a été mise à jour depuis qu’elle a été lue dans le curseur. SQL Server ne verrouille pas les lignes au fur et à mesure qu’elles sont lues dans le curseur. Il utilise à la place des comparaisons des valeurs de la colonne timestamp, ou une valeur de somme de contrôle si la table n’a pas de colonne timestamp, 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 mise à jour ou la suppression positionnée que vous avez tentée échoue. OPTIMISTIC ne peut pas être spécifié s’il FAST_FORWARD est également spécifié.

TYPE_WARNING

Indique qu'un message d'avertissement est envoyé au client lorsque le curseur est converti implicitement du type demandé vers un autre type.

select_statement

Instruction standard SELECT qui définit le jeu de résultats du curseur. Les mot clé , , et INTO ne sont pas autorisés COMPUTEdans select_statement d’une déclaration de FOR BROWSEcurseur. COMPUTE BY

SQL Server convertit implicitement le curseur en un autre type si les clauses de l’instruction select_statement sont incompatibles avec la fonctionnalité du type de curseur demandé.

FOR UPDATE [ OF column_name [ ,...n ] ]

Définit les colonnes qui peuvent être mises à jour par le curseur. Si OF <column_name> [, <... n>] est fourni, seules les colonnes listées permettent les modifications. Si vous spécifiez UPDATEsans liste de colonnes, toutes les colonnes peuvent être mises à jour, sauf si l’option de concurrence READ_ONLY a été spécifiée.

Notes

DECLARE CURSOR définit les attributs d’un curseur côté serveur Transact-SQL, notamment son comportement quant au défilement et la requête utilisée pour créer le jeu de résultats sur lequel le curseur opère. L’instruction OPEN remplit le jeu de résultats, et l’instruction FETCH retourne une ligne du jeu de résultats. L’instruction CLOSE libère le jeu de résultats actuel associé au curseur. L’instruction DEALLOCATE libère les ressources utilisées par le curseur.

Le premier format de l’instruction DECLARE CURSOR utilise la syntaxe ISO pour déclarer les comportements du curseur. Le second format de DECLARE CURSOR utilise les extensions Transact-SQL qui permettent de définir les curseurs en utilisant les mêmes types de curseurs que ceux des fonctions de curseur API de base de données ODBC ou ADO.

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

Si une DECLARE CURSOR syntaxe Transact-SQL n’utilise READ_ONLYpas , OPTIMISTICou SCROLL_LOCKSsi la valeur par défaut est la suivante :

• Si l’instruction 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, et ainsi de suite), le curseur est READ_ONLY.

• Les curseurs STATIC et FAST_FORWARD sont par défaut de type READ_ONLY.

• Les curseurs DYNAMIC et KEYSET sont par défaut de type OPTIMISTIC.

Les noms de curseurs ne peuvent être référencés que par d’autres instructions Transact-SQL. Ils ne peuvent pas être référencés par les fonctions d’API 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 des fonctions ou méthodes OLE DB, ODBC ou ADO. Les lignes de curseur ne peuvent pas être extraites à l’aide des fonctions d’extraction ou des méthodes des API ; les lignes ne peuvent être extraites que par des instructions Transact-SQL FETCH .

Une fois qu’un curseur est déclaré, ces procédures stockées système peuvent être utilisées pour déterminer les caractéristiques du curseur.

Les variables peuvent être utilisées dans le cadre de l’select_statement qui déclare un curseur. Les valeurs des variables de curseur ne changent pas une fois qu’un curseur est déclaré.

Autorisations

Les autorisations DECLARE CURSOR sont octroyées par défaut à tout utilisateur qui a des autorisations SELECT sur les vues, les tables et les colonnes utilisées par le curseur.

Limites

Vous ne pouvez pas utiliser de curseurs ou de déclencheurs sur une table avec un index columnstore cluster. Cette restriction ne s’applique pas aux index columnstore non-cluster. Vous ne pouvez pas utiliser de curseurs ni de déclencheurs sur une table avec un index columnstore non-cluster.

Exemples

R. Utiliser le curseur de base et la syntaxe

Le jeu de résultats généré à l'ouverture du curseur ci-après contient toutes les lignes et toutes les colonnes de la table. Ce curseur peut être mis à jour, et toutes les mises à jour et les suppressions sont représentées sous la forme d'extractions effectuées sur ce curseur. FETCH NEXT est la seule extraction disponible, car l’option SCROLL n’est pas spécifiée.

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

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

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

SET NOCOUNT ON;

DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),
    @message VARCHAR(80), @product NVARCHAR(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' +
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Contenu connexe

• @@FETCH_STATUS (Transact-SQL)
• CLOSE (Transact-SQL)
• Curseurs (Transact-SQL)
• DEALLOCATE (Transact-SQL)
• FETCH (Transact-SQL)
• SELECT (Transact-SQL)
• sp_configure (Transact-SQL)