Utilisation de curseurs et de boucles dans Transact-SQL. Curseurs dans les procédures stockées MySQL Curseurs dans le serveur SQL

La commande DECLARE CURSOR permet de récupérer les enregistrements d'une table ligne par ligne pour les manipuler. Cela permet un traitement ligne par ligne au lieu du traitement traditionnel des ensembles de données effectué par SQL.

En toute première approximation, les étapes suivantes sont utilisées lorsque vous travaillez avec le curseur.

Le curseur est créé avec la commande DECLARE. Le curseur est ouvert avec la commande OPEN.

Les opérations avec le curseur sont effectuées à l'aide de la commande FETCH. Le curseur est fermé avec la commande CLOSE.

La commande DECLARE CURSOR spécifie une instruction SELECT. Chaque ligne renvoyée par l'instruction SELECT peut être récupérée et traitée individuellement. L'exemple Oracle suivant déclare un curseur dans un bloc de déclaration avec plusieurs autres variables. Ensuite, dans le bloc BEGIN...END suivant, le curseur est ouvert, une sélection est effectuée à partir de celui-ci et le curseur est fermé.

CURSOR title_price_cursor IS SELECT titre, prix FROM titres

OÙ le prix N'EST PAS NULL ; title_price_val title_price_cursor ROWTYPE ; nouveau_prix NUMBER(10.2);

OUVRIR title_price_Cursor ;

FETCH title_price_cur-sor INTO title_price_val ;

new_price:= "title_price_val.price" * 1.25 INSÉRER DANS LES VALEURS new_title_price

(title_price_val.title, new_price) FERMER title_price_cursor ; FIN;

Cet exemple utilisant PL/SQL, nous n'expliquerons pas une grande partie du code dans ce livre. Cependant, le bloc DECLARE affiche clairement la déclaration du curseur. Dans un bloc PL/SQL exécutable, le curseur est initialisé avec la commande OPEN, les valeurs sont récupérées avec la commande FETCH, et enfin le curseur est fermé avec la commande CLOSE.

L'instruction SELECT est la base du curseur, il est donc recommandé de la tester minutieusement avant de l'inclure dans l'instruction DECLARE CURSOR. L'instruction SELECT peut opérer sur la table ou la vue sous-jacente. Par conséquent, les curseurs en lecture seule peuvent fonctionner avec des vues non modifiables. Une instruction SELECT peut contenir des clauses telles que ORDER BY, GROUP BY et HAVING tant que ces clauses ne mettent pas à jour la table source. Si le curseur est défini comme FOR UPDATE, il est alors recommandé de supprimer ces clauses de l'instruction SELECT.

Les curseurs locaux sont souvent utilisés comme paramètres de sortie des procédures stockées. Par conséquent, vous pouvez définir et remplir un curseur dans une procédure stockée et le transmettre au travail par lots ou à la procédure stockée qui l'a appelé.

Dans l'exemple DB2 simple suivant, nous déclarerons un curseur qui recherche les numéros de département, les noms de département et les numéros de responsable dans le groupe_admin "XO1".

DÉCLARE dept_cursor CURSOR

FOR SELECT numéro_service, nom_service, numéro_gestionnaire

OÙ admin_group="X01"

ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC ;

L'exemple Microsoft SQL Server suivant déclare et ouvre un curseur pour la table des éditeurs. Le curseur sélectionne le premier enregistrement qui correspond à l'instruction SELECT dans la table des éditeurs et l'insère dans une autre table. Il passe ensuite à l'enregistrement suivant, puis au suivant, jusqu'à ce que tous les enregistrements aient été traités. Enfin, le curseur est fermé et la mémoire est libérée (la commande DEALLOCATE n'est utilisée que dans Microsoft SQL Server).

DÉCLARE @nom_éditeur VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM éditeurs OÙ pays "USA"

FETCH NEXT FROM pub_cursor INTO editor_name

WHILE @s>FETCH_STATUS=0

INSÉRER DANS LES VALEURS des éditeurs_étrangers("j>nom_éditeur)

FERMER pub_cursor DEALLOCATE pub_cursor

Dans cet exemple, vous pouvez voir le curseur se déplacer dans un ensemble d'enregistrements. (Cet exemple n'a pour but que de démontrer l'idée, puisqu'en réalité il existe La meilleure façon solution à ce problème, à savoir l'instruction INSERT, SELECT.)

ELLE S'APPLIQUE À : SQL Server (depuis 2008)Base Données SQL Azure SQL Data WarehouseEntrepôt de données parallèle

Définit les attributs d'un curseur de serveur Transact-SQL, tels que les propriétés d'affichage et la requête utilisée pour créer le jeu de résultats sur lequel le curseur opère. L'instruction DECLARE CURSOR prend en charge à la fois la syntaxe standard ISO et la syntaxe qui utilise le jeu d'extensions de langage Transact-SQL.

Syntaxe ISO DECLARE nom_curseur [ INSENSIBLE ] [ DÉFILEMENT ] CURSEUR POUR instruction_sélectionnée [ POUR ( LECTURE SEULE | MISE À JOUR [ OF nom_colonne [ ,...n ] ] ) ] [;] Syntaxe étendue Transact-SQL DECLARE nom_curseur CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | DÉFILEMENT ] [ STATIQUE | CLÉ | DYNAMIQUE | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF nom_colonne [ ,...n ] ] ] [;]

nom_curseur
nom_curseur

INSENSIBLE
tempdb; ainsi, les modifications apportées aux tables sous-jacentes ne sont pas reflétées dans les données renvoyées par les sélections de ce curseur, et ce curseur n'est pas modifiable. Lors de l'utilisation de la syntaxe ISO, à moins que l'option INSENSITIVE ne soit spécifiée, les mises à jour validées et les suppressions apportées aux tables de base apparaissent dans les sélections suivantes.

FAIRE DÉFILER
Indique que toutes les options d'échantillonnage sont disponibles (PREMIER, DERNIER, PRIEUR, SUIVANT, RELATIF, ABSOLU). Si l'instruction ISO DECLARE CURSOR ne spécifie pas d'option SCROLL, seule l'option d'extraction NEXT est prise en charge. L'option SCROLL ne peut pas être spécifiée avec l'option FAST_FORWARD.

instruction_sélection
Une instruction SELECT standard qui spécifie le jeu de résultats d'un curseur. Les mots-clés FOR BROWSE et INTO ne sont pas autorisés dans instruction_sélection déclaration du curseur.

instruction_sélection conflit avec un curseur du type demandé.

LECTURE SEULEMENT

Mise à jour ]
nom de colonne [, .. .n] est spécifié, seules les colonnes répertoriées autorisent les modifications. Si l'instruction UPDATE est utilisée sans liste de colonnes, alors la mise à jour est possible pour toutes les colonnes.

nom_curseur
Nom Transact-SQL du curseur de serveur spécifique. nom_curseur doit suivre les règles relatives aux identifiants.

LOCALE
Indique que le curseur est local au package, à la procédure stockée ou au déclencheur dans lequel il a été créé. Le nom du curseur n'est valide que dans cette zone. Un curseur peut être référencé par des variables locales de package, des procédures stockées, des déclencheurs ou le paramètre de sortie d'une procédure stockée. Le paramètre OUTPUT est utilisé pour transmettre un curseur local au package appelant, à la procédure stockée ou au déclencheur, qui peut ensuite attribuer le paramètre à une variable de curseur pour un accès ultérieur au curseur une fois la procédure stockée terminée. Le curseur est implicitement libéré lorsque le lot, la procédure stockée ou le déclencheur termine son exécution, à moins que le curseur n'ait été transmis au paramètre OUTPUT. Si le curseur a été passé au paramètre OUTPUT, le curseur est libéré lorsque toutes les variables le référençant sont libérées ou lorsque la portée est quittée.

MONDIAL
Indique que le curseur est global à la connexion. Le nom du curseur peut être utilisé par n’importe quelle procédure stockée ou package exécuté sur la connexion. Le curseur n'est implicitement libéré que si la connexion est rompue.

FORWARD_ONLY
Spécifie que le curseur ne peut être affiché que de la première ligne à la dernière. Seule l'option de récupération FETCH NEXT est prise en charge. Si FORWARD_ONLY est spécifié sans les mots-clés STATIC, KEYSET ou DYNAMIC, le curseur se comporte comme un curseur DYNAMIC. Si ni l'argument FORWARD_ONLY ni l'argument SCROLL ne sont spécifiés, la valeur par défaut est l'argument FORWARD_ONLY sauf si les mots clés STATIC, KEYSET ou DYNAMIC sont présents. Les curseurs STATIC, KEYSET et DYNAMIC ont une valeur par défaut de SCROLL. Contrairement aux API de base de données telles que ODBC et ADO, le mode FORWARD_ONLY est pris en charge par les curseurs Transact-SQL suivants : STATIC, KEYSET et DYNAMIC.

STATIQUE
Définit un curseur qui crée une copie temporaire des données à utiliser par le curseur. Toutes les requêtes adressées au curseur accèdent à la table temporaire spécifiée dans tempdb; ainsi, les modifications apportées aux tables sous-jacentes ne sont pas reflétées dans les données renvoyées par les sélections de ce curseur, et ce curseur n'est pas modifiable.

JEU DE CLÉS
Indique que l'appartenance ou l'ordre des lignes dans le curseur reste inchangé lors de son ouverture. Un ensemble de clés identifiant de manière unique les lignes est intégré à la table dans tempdb appelé clés.

Les modifications apportées aux valeurs non clés dans les tables de base effectuées par le propriétaire du curseur ou validées par d'autres utilisateurs sont affichées lorsque le propriétaire du curseur les consulte. Les modifications apportées par d'autres utilisateurs ne sont pas reflétées (les modifications ne peuvent pas être apportées à l'aide du curseur du serveur Transact-SQL). Si une ligne est supprimée, la tentative de récupération des lignes renvoie @@FETCH_STATUS -2. Les mises à jour des valeurs clés au-delà des limites du curseur sont similaires à la suppression d'une ancienne ligne, puis à l'insertion d'une nouvelle ligne. La ligne avec les nouvelles valeurs n'est pas visible et tente de récupérer la ligne avec les anciennes valeurs renvoie @@FETCH_STATUS -2. Les mises à jour sont visibles immédiatement si elles sont effectuées via le curseur à l'aide de la clause WHERE CURRENT OF.

DYNAMIQUE
Définit un curseur qui affiche toutes les modifications de données apportées aux lignes du jeu de résultats lors de l'affichage de ce curseur. Les valeurs des données, l'ordre et l'appartenance aux lignes dans chaque sélection peuvent varier. L'option de sélection ABSOLU n'est pas prise en charge par les curseurs dynamiques.

AVANCE RAPIDE
Indique un curseur FORWARD_ONLY, READ_ONLY pour lequel l'optimisation des performances est activée. L'option FAST_FORWARD ne peut pas être spécifiée avec les options SCROLL ou FOR_UPDATE.

LECTURE SEULEMENT
Empêche les modifications apportées via ce curseur. La clause WHERE CURRENT OF ne peut pas faire référence à un curseur dans une instruction UPDATE ou DELETE. Cette option est prioritaire sur la fonctionnalité d'actualisation du curseur par défaut.

SCROLL_LOCKS
Indique que les mises à jour positionnées ou les suppressions effectuées à l'aide du curseur sont garanties de succès. SQL Server verrouille les lignes au fur et à mesure qu'elles sont lues dans le curseur pour garantir que ces lignes sont disponibles pour les modifications ultérieures. L'option SCROLL_LOCKS ne peut pas être spécifiée avec l'option FAST_FORWARD ou STATIC.

OPTIMISTE
Spécifie que les mises à jour positionnées ou les suppressions effectuées à l'aide du curseur échoueront si la ligne a été mise à jour depuis sa lecture dans le curseur. SQL Server ne verrouille pas les lignes lors de leur lecture dans le curseur. Des comparaisons sont utilisées à la place horodatage valeurs de colonne ou sommes de contrôle, s'il n'est pas dans le tableau horodatage colonne pour déterminer si la ligne a changé depuis sa lecture dans le curseur. Si la ligne a été modifiée, les tentatives de mise à jour ou de suppression positionnées échoueront. L'option OPTIMISTIC ne peut pas être spécifiée avec l'option FAST_FORWARD.

TYPE_AVERTISSEMENT
Spécifie qu'un avertissement sera envoyé au client si le curseur est implicitement converti d'un type demandé à un autre.

instruction_sélection
Une instruction SELECT standard qui spécifie le jeu de résultats d'un curseur. Les mots-clés COMPUTE, COMPUTE BY, FOR BROWSE et INTO ne sont pas autorisés dans instruction_sélection déclaration du curseur.

SQL Server convertit implicitement le curseur en un autre type si les clauses de instruction_sélection conflit avec un curseur du type demandé. Pour plus d’informations, consultez Conversions implicites de curseur.

POUR LA MISE À JOUR]
Définit les colonnes du curseur à mettre à jour. Si DE nom de colonne [, ... n] est fourni, seules les colonnes répertoriées autorisent les modifications. Si l'instruction UPDATE est utilisée sans liste de colonnes, la mise à jour est possible pour toutes les colonnes, sauf si l'option de concurrence READ_ONLY a été spécifiée.

L'instruction DECLARE CURSOR définit les attributs d'un curseur de serveur Transact-SQL, tels que les propriétés d'affichage 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 en renvoie une ligne. L'instruction CLOSE efface le jeu de résultats actuel associé au curseur. L'instruction DEALLOCATE libère les ressources utilisées par le curseur.

La première forme de l'instruction DECLARE CURSOR utilise la syntaxe ISO pour spécifier les paramètres du curseur. La deuxième forme de l'instruction DECLARE CURSOR utilise des extensions du langage Transact-SQL qui vous permettent de définir des curseurs en utilisant les mêmes types que ceux utilisés dans les fonctions de curseur des API de base de données telles que ODBC et ADO.

Ces deux formes ne peuvent être mélangées. Si vous spécifiez SCROLL ou omettez des mots-clés avant le mot-clé CURSOR, vous ne pouvez pas utiliser de mots-clés entre CURSOR et également pour instruction_sélection mots clés. Lors de la spécification de mots-clés entre le CURSEUR, ainsi que pour instruction_sélection mots-clés, vous ne pouvez pas spécifier SCROLL ou INSENSITIVE avant le mot-clé CURSOR.

Si vous utilisez la syntaxe Transact-SQL pour l'instruction DECLARE CURSOR et ne spécifiez pas les options READ_ONLY, OPTIMISTIC ou SCROLL_LOCKS, la valeur par défaut suivante est supposée.

Si l'instruction SELECT ne prend pas en charge les mises à jour (ou dispose d'autorisations insuffisantes, ou accède à des tables distantes qui ne prennent pas en charge les mises à jour, etc.), le curseur est défini sur READ_ONLY.

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

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

Les curseurs ne peuvent être référencés que par d'autres instructions Transact-SQL. Les fonctions de l'API de base de données ne peuvent pas référencer des curseurs. Par exemple, une fois qu'un curseur est déclaré, les fonctions et méthodes OLE DB, ODBC ou ADO ne peuvent pas faire référence à son nom. Les lignes du curseur ne peuvent pas être sélectionnées à l'aide des fonctions et méthodes API correspondantes ; À cette fin, vous devez utiliser les instructions Transact-SQL FETCH.

Les procédures stockées suivantes peuvent être utilisées pour définir les propriétés d'un curseur après sa déclaration.

Les variables peuvent être utilisées dans le cadre instruction_sélection, dans lequel le curseur est déclaré. Les valeurs des variables du curseur ne changent pas après leur déclaration.

Par défaut, les autorisations DECLARE CURSOR sont accordées à tous les utilisateurs disposant de l'autorisation SELECT sur les vues, tables et colonnes utilisées par le curseur.

Vous ne pouvez pas utiliser de curseurs ou de déclencheurs sur une table avec un index columnstore clusterisé. Cette limitation ne s'applique pas aux index non clusterisés ; Vous pouvez utiliser des curseurs et des déclencheurs sur une table avec un index columnstore non clusterisé.

A. Utilisation d'un curseur et d'une syntaxe simples

Le jeu de résultats créé lorsque vous ouvrez ce curseur comprend toutes les lignes et colonnes du tableau. Ce curseur peut être mis à jour, toutes les mises à jour et suppressions sont représentées dans la sélection de ce curseur. FETCH``NEXT est récupéré uniquement car le paramètre SCROLL n'a pas été spécifié.

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

B. Utilisation de curseurs imbriqués pour afficher un rapport

L'exemple suivant utilise des curseurs imbriqués pour afficher un rapport complexe. Un curseur interne est déclaré pour chaque fournisseur.

METTRE NOCOUNT SUR ; DÉCLARE @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 ); IMPRIMER" -------- Rapport sur les produits du fournisseur --------"; DECLARE supplier_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID ; OPEN supplier_cursor FETCH NEXT FROM supplier_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 COMMENCER L'IMPRESSION " " SELECT @message = "----- Produits du fournisseur : "+ @vendor_name IMPRIMER @message -- Déclarer un curseur interne basé sur -- sur supplier_id à partir du curseur externe. 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 -- Valeur variable du curseur extérieur OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 IMPRIMER "<>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Obtenez le fournisseur suivant. FETCH NEXT FROM supplier_cursor INTO @vendor_id, @vendor_name FIN FERMER vendeur_cursor ; DEALLOCATE vendeur_cursor ;

Le curseur est un lien vers la zone de mémoire contextuelle. Dans certaines implémentations du langage de programmation SQL (Oracle, Microsoft SQL Server) - l'ensemble de résultats obtenu lors de l'exécution d'une requête et le pointeur d'enregistrement actuel qui lui est associé. Je dirais qu'un curseur est une table virtuelle qui représente un stockage alternatif de données. Dans ce cas, le curseur vous permet d'accéder à ses données comme s'il s'agissait des données d'un tableau normal.
Les curseurs sont utilisés dans les procédures stockées. Assez de théorie, regardons un exemple :
Nous avons une base de données (la base de données n'est pas bonne, c'est l'une de mes travail de laboratoire, mais notre professeur de base de données a insisté sur une telle structure)

/*information bancaire*/
CREATE TABLE `banque` (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


CLÉ PRIMAIRE (`BankId`)

)MOTEUR=InnoDB
JEU DE CARACTÈRES "utf8" COLLATE "utf8_bin" ;
/*données sur les dépôts */
CREATE TABLE `distribution bancaire` (
`BankId` ENTIER (11) NON NULL,
`Présent` ENTIER (11) PAR DÉFAUT NULL ,
`ContributeAmount` DECIMAL (10,0) NON NULL ,
`ClientId` ENTIER (11) NON NULL ,
CLÉ PRIMAIRE(`BankId`, `ClientId`),
CLÉ `BankId` (`BankId`),
CLÉ `ClientId` (`ClientId`),
CONTRAINTE `bankdistribution_fk` CLÉ ÉTRANGÈRE (`BankId`) RÉFÉRENCES `bank` (`BankId`),
CONTRAINTE `bankdistribution_fk1` CLÉ ÉTRANGÈRE (`ClientId`) RÉFÉRENCES `client` (`ClientId`)
)MOTEUR=InnoDB
/*données sur les investisseurs*/
CRÉER TABLE `client` (
`ClientId` INTEGER (3) NON NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NON NULL ,
`Nom` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Nom` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Téléphone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Adresse` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` ENTIER (5) NON NULL,
CLÉ PRIMAIRE(`ClientId`, `CreditCardId`),
CLÉ `ClientId` (`ClientId`)

)MOTEUR=InnoDB
AUTO_INCREMENT=11 JEU DE CARACTÈRES "utf8" COLLATE "utf8_bin"

Disons que nous devons recevoir chaque banque tour à tour et effectuer certaines actions avec elle, la requête suivante pourrait nous aider

Sélectionnez « banque ».* DEPUIS « banque » NOMBRE LIMITE DE THE_RECORD_WE NEED,1

. Ainsi, en utilisant LIMIT WE NEED_RECORD NUMBER, 1, nous extrayons tour à tour chaque enregistrement d'un cycle de la table bancaire et effectuons les actions dont nous avons besoin avec, tout en augmentant la valeur de WE NEED_RECORD NUMBER de 1. Nous allons maintenant faire la même chose mais en utilisant un curseur

Commencer
/* variables où nous extrayons les données */
Déclarez vBankId entier ;
Déclarez vBankName VARCHAR(50);
Déclarez vAddress VARCHAR(50);
Déclarez vPhone VARCHAR (50);
/* variable hadler - a*/
Déclarer done entier par défaut 0 ;
/*Déclaration du curseur*/
Déclarez le curseur BankCursor pour sélectionner `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` où 1 ;
/*Objectif du HANDLER, qui sera expliqué ci-dessous*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1 ;
/* ouvre le curseur */
Ouvrir la banqueCurseur ;
/*récupérer des données*/
PENDANT QUE terminé = 0 FAIRE

nous prenons les mesures dont nous avons besoin
FIN PENDANT QUE ;
/*fermeture du curseur*/
Fermer BankCursor ;
FIN ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

Erreur : 1329 SQLSTATE : 02000 (ER_SP_FETCH_NO_DATA)

Message : Aucune donnée - aucune ligne récupérée, sélectionnée ou traitée

SQLSTATE : 02000 se déclenche lorsque la fin du curseur est atteinte ou lorsque select ou update renvoie une chaîne vide.

La ligne suivante, nous avons déclaré le curseur DECLARE curseur_name CURSOR FOR select_statement;
Ouvrir le curseur Ouvrir nom_curseur ;
Ensuite, jusqu'à la fin du curseur (WHILE done = 0 DO), nous extrayons les données et les traitons.
Vous devez fermer le curseur avant de quitter la procédure stockée. Fermez le nom_curseur ;

Cela n'a pas l'air compliqué. Mais il existe de nombreux pièges associés à SQLSTATE "02000".

PENDANT QUE terminé = 0 FAIRE
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone ;

Sélectionnez (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution où BankId = vBankId limite 1 ;
nous faisons quelques actions
FIN PENDANT QUE ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

Tout va bien et correct d'un point de vue syntaxique. Mais d’un point de vue logique, non. Il peut arriver que les déposants n'aient pas ouvert de compte dans une banque, alors pour Select (ContributeAmount) INTO vContributeAmountSUM FROM distribution bancaire où BankId = vBankId limite 1 ; SQLSTATE : 02000 se déclenchera, la variable done sera définie sur 1 et la boucle while se terminera plus tôt que prévu. Cela peut être évité en procédant comme suit

PENDANT QUE terminé = 0 FAIRE
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone ;
/* extraire pour la banque le montant de l'un de ses dépôts */


si (vContributeAmountSUM > 0) alors
/* extraire pour la banque le montant de l'un de ses dépôts */

fin si ;
nous faisons quelques actions
FIN PENDANT QUE ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

Avec la première requête, nous vérifions s'il y a des contributions (s'il n'y en a pas, alors vContributeAmountSUM == 0) et seulement s'il y en a, nous récupérons les données.

Supposons maintenant que nous devions supprimer le montant total des comptes dans différentes banques pour chaque client.
Déclarez le curseur ClientSummCursor pour Select sum

Déclarez ClientSummCursor Cursor pour Select sum (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client sur (client.ClientId = bankdistribution.`ClientId`) où 1 groupe par `bankdistribution`. `IDClient` ;

Ouvrez ClientSummCursor ;
PENDANT QUE terminé = 0 FAIRE
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone ;
/* extraire pour la banque le montant de l'un de ses dépôts */
Sélectionnez Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution où BankId = vBankId limite 1 ;
/* vérifie s'il y a réellement des dépôts dans cette banque */
si (vContributeAmountSUM > 0) alors
/* extraire pour la banque le montant de l'un de ses dépôts */
Sélectionnez ContributeAmount INTO vContributeAmountSUM FROM bankdistribution où BankId = vBankId limite 1 ;
fin si ;


nous faisons quelques actions.
FIN PENDANT QUE ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

La même situation peut se produire lorsque les données du curseur ClientSummCursor se terminent plus tôt que les données du BankCursor, SQLSTATE : 02000 est déclenché, la variable done est définie sur 1 et la boucle while se termine plus tôt que prévu. Cela peut être évité en procédant comme suit

Ouvrez ClientSummCursor ;
PENDANT QUE terminé = 0 FAIRE
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone ;
/* extraire pour la banque le montant de l'un de ses dépôts */
Sélectionnez Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution où BankId = vBankId limite 1 ;
/* vérifie s'il y a réellement des dépôts dans cette banque */
si (vContributeAmountSUM > 0) alors
/* extraire pour la banque le montant de l'un de ses dépôts */
Sélectionnez ContributeAmount INTO vContributeAmountSUM FROM bankdistribution où BankId = vBankId limite 1 ;
fin si ;
/* avant d'extraire les données du deuxième curseur, rappelez-vous l'état sqlstate */
SET old_status = terminé ;
/* extraire les données dont nous avons besoin */
FETCH ClientSummCursor INTO vSum, vClientId ;
/* vérifie si les données ont été récupérées et si sqlstate 0200 a échoué */
si (fait = 0) alors
nous faisons quelques actions.
fin si ;
/* avant la fin du while, restaure la valeur de la variable done */
set done = old_status ;
FIN PENDANT QUE ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

Merci à tous ceux qui ont lu jusqu'ici, j'espère que cela sera utile à quelqu'un.

L'implémentation d'un curseur dans une base de données ressemble à une classe Java qui possède un ensemble de données et des méthodes pour les traiter. Où curseur SQL utilise les données comme un tableau régulier. Les curseurs peuvent être utilisés dans les déclencheurs, les procédures stockées et les fonctions.

Conformément à la norme SQL, lorsque vous travaillez avec des curseurs, les actions de base suivantes sont effectuées :

• déclaration du curseur ;
• ouvrir un curseur avec des données de lecture ;
• échantillonnage ligne par ligne des données du curseur ;
• modifier les données de ligne à l'aide du curseur ;
• fermer le curseur, après quoi il devient inaccessible ;
• relâcher le curseur, c'est-à-dire supprimer un curseur de la mémoire car sa fermeture ne libère pas forcément la mémoire qui lui est associée.

Dans différentes implémentations, la définition le curseur peut avoir quelques différences. Par exemple, il est parfois nécessaire de libérer explicitement la mémoire allouée à un curseur. Une fois le curseur libéré, la mémoire qui lui est associée est également libérée. Cela permet de réutiliser le nom du curseur. Dans d'autres implémentations, lorsque le curseur est fermé, la mémoire est implicitement libérée.

Dans certains cas, vous ne pouvez pas vous passer d'un curseur. Cependant, si possible, évitez d'utiliser un curseur et travaillez avec des commandes standards de traitement de données : SELECT, UPDATE, INSERT, DELETE. Cela est dû au fait que les curseurs ne permettent pas d'opérations de modification sur l'ensemble du volume de données et que la rapidité d'exécution des traitements de données à l'aide d'un curseur est sensiblement inférieure à celle de moyens standards SQL.

Si un programme peut modifier les données chargées dans le curseur, il est alors appelé modifiable. Lorsqu'on parle de curseurs, il ne faut pas oublier l'isolation des transactions. Un utilisateur modifie un enregistrement à l'aide d'un curseur, tandis qu'un autre utilisateur lit cet enregistrement à l'aide de son propre curseur. De plus, il peut modifier le même enregistrement, ce qui nécessite le maintien de l'intégrité des données.

Déclarer un curseur

Les curseurs doivent être déclarés avant de pouvoir être utilisés. Le standard SQL utilise la syntaxe suivante pour créer un curseur :

Déclarez le curseur nom_curseur pour select_statement ])]

Cette expression déclare un curseur déclarer le curseur avec le nom "cursor_name".

INSENSIBLE un curseur statique est créé qui ne permet pas d'effectuer des modifications. De plus, les modifications apportées par d'autres utilisateurs ne sont pas affichées. Si le mot clé INSENSITIVE est manquant, un curseur dynamique est créé.

Lorsque vous utilisez un mot-clé FAIRE DÉFILER le curseur créé peut défiler dans n'importe quelle direction, vous permettant d'appliquer n'importe quelle commande de sélection. Si cet argument est omis, alors le curseur sera séquentiel, c'est-à-dire sa visualisation ne sera possible que dans une seule direction - du début à la fin.

Expression instruction_sélection indique une structure pour lire des informations comme select ... from ... . Il ne doit pas contenir l'opérateur dans, puisque le curseur a son propre opérateur aller chercher pour remplir les variables avec les données du curseur.

Lors de la spécification d'un argument POUR LIRE_ONLY un curseur en lecture seule sera créé et aucune modification des données ne sera autorisée. Un curseur dynamique peut être déclaré comme curseur en lecture seule, permettant d'afficher les modifications apportées par un autre utilisateur.

Créer un curseur avec un argument POUR LA MISE À JOUR vous permet d'apporter des modifications aux données du curseur soit dans les colonnes spécifiées, soit en l'absence d'argument OF nom_colonne, dans toutes les colonnes.

Vous pouvez déclarer plusieurs curseurs dans un sous-programme. Mais chaque curseur doit avoir un nom unique. Pour ouvrir un curseur vous devez utiliser l'opérateur ouvrir qui ouvre le curseur précédemment déclaré :

Curseur ouvert

SQL définit la syntaxe suivante pour ouvrir un curseur « cursor open » :

Ouvrez le nom_curseur ;

Récupérer des données à partir d'un curseur, récupérer le curseur

La syntaxe pour lire les données d'un curseur dans certaines variables est la suivante :

Récupère le nom_du curseur dans var_name [, var_name] ... ;

Opérateur aller chercher sélectionne les données du curseur ouvert dans les variables situées après dans et déplace le curseur à la position suivante.

Curseur fermé

Opérateur fermer ferme le curseur. Si l'opérateur n'est pas explicitement spécifié, le curseur se ferme automatiquement à la fermeture du bloc de programme correspondant.

Fermez le nom_curseur ;

Après la fermeture, le curseur devient inaccessible. Lors de la fermeture, tous les verrous installés pendant l'exécution du curseur sont libérés. Seuls les curseurs ouverts peuvent être fermés. Un curseur fermé mais non relâché peut être rouvert. Il n'est pas permis de fermer un curseur non ouvert.

Chaque SGBD a ses propres particularités d'utilisation d'un curseur.

Fonctionnalités d'utilisation des curseurs dans Oracle

Il existe quatre attributs de curseur en PL/SQL %TROUVÉ, %PAS TROUVÉ, %EST OUVERT Et %ROWCOUNT. Les attributs du curseur sont déclarés comme les opérateurs %TYPE et %ROWTYPE, à droite du nom du curseur.

Attribut %TROUVÉ

Attribut %NOTFOUND

L'attribut %NOTFOUND est exactement le contraire de %FOUND.

Attribut %ISOPEN

L'attribut %ISOPEN indique uniquement si le curseur est ouvert ou non.

Attribut %ROWCOUNT

Attribut %ROWCOUNT est un attribut numérique qui renvoie le nombre de lignes lues par le curseur à un moment donné.

Exemple de curseur SQL dans un SGBD Oracle

Déclarez v_id managers.id %TYPE ; v_name managers.name%TYPE ; v_comm managers.comm%TYPE ; curseur crs pour sélectionner l'identifiant, le nom, la somme (comm) en tant que communication des gestionnaires où les données entre "2014-11-01" et "2014-11-30" sont regroupées par identifiant, nom ; commencer les crs ouverts ; boucle EXIT WHEN crs%NOTFOUND ; FETCH crs dans v_id, v_name, v_comm ; insérer dans les valeurs bonus(id, name, comm) (crs.id, crs.name, crs.comm) ; terminer la boucle ; commettre; fermer les crs ; fin;

Fonctionnalités d'utilisation des curseurs dans SQL Server

Les curseurs utilisés dans MSSQL peuvent être séquentiels ou déroulants. Séquentiel vous permet de sélectionner des données dans une seule direction : du début à la fin. Les curseurs déroulants permettent un mouvement dans les deux sens et vous permettent d'accéder à une ligne arbitraire dans le jeu de résultats du curseur.

SQL Server prend en charge les curseurs statiques, dynamiques, séquentiels et pilotés par jeu de clés.

Dans une conception de curseur statique, les informations sont stockées sous forme d'instantané à un moment donné. Par conséquent, les modifications apportées à la base de données par un autre utilisateur ne sont pas visibles. Pendant l'ouverture du curseur, le serveur verrouille toutes les lignes incluses dans son jeu de résultats complet. Un curseur statique ne change pas après sa création et affiche toujours l'ensemble de données qui existait au moment de son ouverture. Si d'autres utilisateurs modifient les données incluses dans le curseur dans la table source, cela n'affectera pas le curseur statique. Il est impossible d'apporter des modifications à un curseur statique, il s'ouvre donc toujours en mode lecture seule.

Un curseur dynamique nécessite une surcharge réseau et des ressources logicielles supplémentaires. Lors de l'utilisation de curseurs dynamiques, une copie complète des données n'est pas créée, mais les sélections dans les tables sources ne sont effectuées que lorsque l'utilisateur accède à certaines données. Pendant la récupération, le serveur verrouille les lignes et toutes les modifications apportées par l'utilisateur à l'ensemble de résultats complet du curseur seront visibles dans le curseur. Cependant, une fois que le curseur a récupéré les données, les modifications apportées par un autre utilisateur ne seront plus reflétées dans le curseur.

Un curseur contrôlé par un jeu de touches a des propriétés entre statique et dynamique. Les enregistrements sont identifiés au moment de l'échantillonnage et les modifications sont donc suivies. Ce type de curseur est utile lors de la mise en œuvre d'un défilement vers l'arrière. Dans ce cas, les ajouts et suppressions de données ne sont pas visibles jusqu'à ce que les informations soient mises à jour et que le curseur sélectionne nouvelle version enregistrements si des modifications y ont été apportées.

Les curseurs statiques sont mieux utilisés pour les systèmes de traitement de l'information, c'est-à-dire pour les systèmes de reporting ou à des fins statistiques et analytiques. Un curseur statique est plus efficace pour récupérer de grandes quantités de données. Dans les systèmes d'achats électroniques ou de réservation d'objets (places, billets), il est nécessaire de percevoir dynamiquement les informations mises à jour au fur et à mesure des modifications. Dans de tels cas, un curseur dynamique est utilisé. Dans ces applications, la quantité de données transférées est généralement faible et accessible au niveau de l'enregistrement individuel.

Les curseurs séquentiels ne permettent pas de récupérer les données dans le sens inverse, uniquement du début à la fin du curseur. Un curseur séquentiel ne stocke pas un ensemble de toutes les lignes de données. Ils sont lus dans la base de données dès qu'une sélection est effectuée dans le curseur, ce qui permet de refléter dynamiquement toutes les modifications apportées par les utilisateurs à la base de données à l'aide des commandes INSERT, UPDATE, DELETE. Le curseur lit l'état des données le plus récent.

Déclaration du curseur

Déclarez le curseur nom_curseur pour SELECT_statement ]]

Lorsque vous utilisez un mot-clé LOCALE Un curseur local sera créé et visible uniquement dans le bloc, le déclencheur, la procédure stockée ou la fonction définie par l'utilisateur. Mot-clé MONDIAL, définit un curseur global qui existe jusqu'à la fermeture de la connexion actuelle.

Opérateur FORWARD_ONLY définit un curseur séquentiel qui permet de récupérer les données uniquement dans le sens allant de la première ligne à la dernière. Lors de l'utilisation de l'opérateur FAIRE DÉFILER un curseur déroulant est créé qui permet d'accéder aux données dans n'importe quel ordre et dans n'importe quelle direction.

Le type de curseur est déterminé par les opérateurs :

• STATIQUE - création d'un curseur statique ;
• DYNAMIQUE - création d'un curseur dynamique ;
• KEYSET - création d'un curseur clé.

Si pour le curseur LECTURE SEULEMENT spécifier un argument AVANCE RAPIDE, alors le curseur créé sera optimisé pour accès rapide aux données. Cet argument ne peut pas être utilisé conjointement avec des arguments FORWARD_ONLY Et OPTIMISTE.

Si le curseur est créé avec l'opérateur OPTIMISTE, il est alors interdit de modifier ou de supprimer les lignes qui ont été modifiées après l'ouverture du curseur.

Lors de la spécification d'un argument TYPE_AVERTISSEMENT le serveur signalera un changement de type de curseur implicite s'il est incompatible avec la requête SELECT.

Récupérer des données à partir d'un curseur, récupérer

Immédiatement après avoir ouvert le curseur, vous pouvez obtenir son contenu à l'aide de la commande suivante :

Lors de l'utilisation de l'opérateur D'ABORD la première ligne du jeu de résultats du curseur sera renvoyée, qui devient la ligne actuelle. Lorsque spécifié DERNIER la dernière ligne du curseur sera renvoyée. Cela devient également la ligne actuelle.

Lors de la spécification d'un opérateur SUIVANT la ligne immédiatement après celle actuelle dans le jeu de résultats sera renvoyée. Cette ligne devient la ligne actuelle. Commande par défaut ALLER CHERCHER utilise exactement cette méthode de récupération des lignes.

Lors de la spécification d'un opérateur AVANT la ligne précédant la ligne actuelle sera renvoyée. Cette ligne devient la ligne actuelle.

Opérateur ABSOLU (numéro_ligne | @numéro_ligne_variable) renvoie une ligne par son numéro ordinal absolu dans le jeu de résultats complet du curseur. Le numéro de ligne peut être spécifié à l'aide d'une constante ou comme nom d'une variable dans laquelle le numéro de ligne est stocké. La variable doit être un type de données entier. Les valeurs positives et négatives sont indiquées. Lors de la spécification d'une valeur positive, la chaîne est comptée à partir du début de l'ensemble, tandis qu'une valeur négative est comptée à partir de la fin. La ligne sélectionnée devient la ligne courante. Si une valeur nulle est spécifiée, aucune ligne n'est renvoyée.

Argument RELATIF (nombre de lignes | @nombre variable de lignes) renvoie la ligne décalée du nombre de lignes spécifié après la ligne actuelle. Si vous spécifiez un nombre négatif de lignes, la ligne correspondant au nombre de lignes spécifié avant la ligne actuelle sera renvoyée. Spécifier une valeur nulle renverra la ligne actuelle. La ligne renvoyée devient la ligne actuelle.

Pour ouvrir un curseur global, vous devez spécifier un mot-clé avant son nom MONDIAL. Le nom du curseur peut également être spécifié à l'aide d'une variable.

En expression DANS @nom_variable [,...n] une liste de variables est définie dans laquelle les valeurs de colonne correspondantes de la ligne renvoyée seront stockées. L'ordre des variables doit correspondre à l'ordre des colonnes dans le curseur et le type de données de la variable doit correspondre au type de données dans la colonne du curseur.

Modification et suppression de données à l'aide d'un curseur

Pour modifier des données à l'aide d'un curseur, vous devez émettre une commande UPDATE au format suivant :

En une seule opération, les valeurs de plusieurs colonnes de la ligne actuelle du curseur peuvent être modifiées, mais elles doivent toutes appartenir à la même table.

Pour supprimer des données à l'aide d'un curseur, utilisez la commande DELETE au format suivant :

En conséquence, la ligne actuelle du curseur sera supprimée.

Libérer de la mémoire, désallouer

Pour supprimer un curseur de la mémoire, utilisez la commande

Désallouer le nom_curseur ;

@@FETCH_STATUS attribut

Pour déterminer la présence de lignes dans le curseur, vous devez utiliser une variable globale @@FETCH_STATUS, qui prend une valeur non nulle s'il n'y a plus de lignes dans le curseur. Si l'ensemble des lignes n'est pas encore épuisé, alors @@FETCH_STATUS est égal à zéro.

Exemple de curseur dans SQL Server

Déclarez @company varchar(50), @manager varchar(50), @message varchar(256); déclarer le curseur crs_clients local pour sélectionner l'entreprise, le gestionnaire parmi les clients où ville = "Moscou" ordre par entreprise, gestionnaire ; imprimer « Liste des clients » ; ouvrez crs_clients ; récupérer ensuite de crs_clients dans @company, @manager ; tandis que @@FETCH_STATUS = 0 start select @message = "Société " + @company + " manager " + @manager ; imprimer @message ; -- passer à l'enregistrement suivant, récupérer ensuite de crs_clients vers @company, @manager ; fin; fermez crs_clients ; désallouer crs_clients ;

La définition du curseur est donnée. Une description de ses types et de son comportement est donnée : curseurs statiques, dynamiques, séquentiels et clés. Les principes du contrôle du curseur sont décrits : création et ouverture d'un curseur, lecture de données, fermeture d'un curseur. Des exemples de programmation de curseur sont donnés.

Notion de curseur

Une requête sur une base de données relationnelle renvoie généralement plusieurs lignes (enregistrements) de données, mais l'application ne traite qu'un seul enregistrement à la fois. Même s'il s'agit de traiter plusieurs lignes en même temps (par exemple, afficher des données sous forme de feuilles de calcul), leur nombre reste limité. De plus, lors de la modification, de la suppression ou de l'ajout de données, l'unité de travail est la série. Dans cette situation, la notion de curseur vient au premier plan, et dans ce contexte, le curseur est un pointeur vers une ligne.

Un curseur en SQL est une zone de la mémoire de la base de données conçue pour contenir la dernière instruction SQL. Si l'instruction actuelle est une requête de base de données, une ligne de données de requête appelée valeur actuelle ou ligne de curseur actuelle est également stockée en mémoire. La zone spécifiée en mémoire est nommée et accessible aux programmes d'application.

Généralement, les curseurs sont utilisés pour sélectionner dans une base de données un sous-ensemble des informations qui y sont stockées. A tout moment, une ligne de curseur peut être vérifiée par le programme d'application. Les curseurs sont souvent utilisés dans Instructions SQL, intégré aux programmes d'application écrits dans des langages procéduraux. Certains d'entre eux sont implicitement créés par le serveur de base de données, tandis que d'autres sont définis par les programmeurs.

Conformément à la norme SQL, lorsque l'on travaille avec des curseurs, on distingue les principales actions suivantes :

• création ou déclaration du curseur;
• curseur d'ouverture, c'est à dire. le remplir avec des données stockées dans une mémoire à plusieurs niveaux ;
• sélection à partir du curseur et modifier les lignes de données avec;
• fermer le curseur, après quoi il devient inaccessible aux programmes utilisateur ;
• libérer le curseur, c'est à dire. supprimer le curseur en tant qu'objet car sa fermeture ne libère pas forcément la mémoire qui lui est associée.

La définition d'un curseur peut varier légèrement selon les implémentations. Par exemple, un développeur doit parfois libérer explicitement la mémoire allouée à un curseur. Après relâcher le curseur sa mémoire associée est également libérée. Cela permet de réutiliser son nom. Dans d'autres implémentations, lorsque fermer le curseur la libération de la mémoire se produit implicitement. Immédiatement après la récupération, il devient disponible pour d'autres opérations : ouvrir un autre curseur etc.

Dans certains cas, l'utilisation d'un curseur est inévitable. Cependant, si possible, cela doit être évité et fonctionner avec des commandes de traitement de données standards : SELECT, UPDATE, INSERT, DELETE. Outre le fait que les curseurs ne permettent pas d'opérations de modification sur l'ensemble du volume de données, la rapidité d'exécution des traitements de données à l'aide d'un curseur est sensiblement inférieure à celle des outils SQL standards.

Implémentation de curseurs dans l'environnement MS SQL Server

SQL Server prend en charge trois types de curseurs :

• Les curseurs SQL sont principalement utilisés dans les déclencheurs, les procédures stockées et les scripts ;
• les curseurs du serveur fonctionnent sur le serveur et implémentent l'interface de programmation d'applications pour ODBC, OLE DB, DB_Library ;
• Les curseurs clients sont implémentés sur le client lui-même. Ils récupèrent l'ensemble des lignes de résultats sur le serveur et le stockent localement, ce qui accélère le traitement des données en réduisant le temps perdu consacré aux opérations réseau.

Différents types d'applications multi-utilisateurs nécessitent différents types d'accès parallèle aux données. Certaines applications nécessitent un accès immédiat aux informations sur les modifications apportées à la base de données. Ceci est typique des systèmes de réservation de billets. Dans d'autres cas, comme les systèmes de reporting statistique, la stabilité des données est importante car si elles sont constamment modifiées, les programmes ne seront pas en mesure d'afficher les informations de manière efficace. Différentes applications nécessitent différentes implémentations de curseurs.

Dans SQL Server, les types de curseurs varient en termes de fonctionnalités qu'ils offrent. Le type de curseur est déterminé au stade de sa création et ne peut être modifié. Certains types de curseurs peuvent détecter les modifications apportées par d'autres utilisateurs aux lignes incluses dans le jeu de résultats. Cependant, SQL Server suit uniquement les modifications apportées à ces lignes pendant l'accès à la ligne et n'autorise pas la modification des modifications une fois que la ligne a déjà été lue.

Les curseurs sont divisés en deux catégories : séquentiel et défilable. Consécutif vous permettent de sélectionner des données dans une seule direction - du début à la fin. Curseurs déroulants offrent une plus grande liberté d'action - il est possible de se déplacer dans les deux sens et de passer à une ligne arbitraire du jeu de résultats du curseur. Si le programme est capable de modifier les données vers lesquelles pointe le curseur, il est dit défilant et modifiable. En parlant de curseurs, il ne faut pas oublier l'isolation des transactions. Lorsqu'un utilisateur modifie un enregistrement, un autre le lit à l'aide de son propre curseur, et de plus, il peut modifier le même enregistrement, ce qui nécessite le maintien de l'intégrité des données.

SQL Server prend en charge les formats statiques, dynamiques, séquentiel et contrôlé par un jeu de clés.

Dans le schéma avec curseur statique les informations sont lues une fois dans la base de données et stockées sous forme d'instantané (à un moment donné), de sorte que les modifications apportées à la base de données par un autre utilisateur ne sont pas visibles. Pendant un certain temps ouvrir le curseur le serveur définit un verrou sur toutes les lignes incluses dans son jeu de résultats complet. Curseur statique ne change pas après la création et affiche toujours l'ensemble de données qui existait au moment de son ouverture.

Si d'autres utilisateurs modifient les données incluses dans le curseur dans la table source, cela n'affectera pas le curseur statique.

DANS curseur statique Il n'est pas possible d'apporter des modifications, il s'ouvre donc toujours en mode lecture seule.

Curseur dynamique maintient les données dans un état « en direct », mais cela nécessite des ressources réseau et logicielles. En utilisant curseurs dynamiques une copie complète des données source n'est pas créée, mais une sélection dynamique est effectuée à partir des tables sources uniquement lorsque l'utilisateur accède à certaines données. Pendant la récupération, le serveur verrouille les lignes et toutes les modifications apportées par l'utilisateur à l'ensemble de résultats complet du curseur seront visibles dans le curseur. Cependant, si un autre utilisateur a apporté des modifications après que le curseur ait récupéré les données, elles ne seront pas reflétées dans le curseur.

Curseur contrôlé par un jeu de touches, se situe à mi-chemin entre ces extrêmes. Les enregistrements sont identifiés au moment de l'échantillonnage et les modifications sont donc suivies. Ce type de curseur est utile lors de la mise en œuvre du défilement arrière - les ajouts et suppressions de lignes ne sont alors pas visibles jusqu'à ce que les informations soient mises à jour, et le pilote sélectionne une nouvelle version de l'enregistrement si des modifications y ont été apportées.

Curseurs séquentiels ne sont pas autorisés à récupérer des données dans le sens inverse. L'utilisateur ne peut sélectionner que les lignes du début à la fin du curseur. Curseur série ne stocke pas un ensemble de toutes les lignes. Ils sont lus dans la base de données dès qu'ils sont sélectionnés dans le curseur, ce qui permet de refléter dynamiquement toutes les modifications apportées par les utilisateurs à la base de données à l'aide des commandes INSERT, UPDATE, DELETE. Le curseur affiche l'état le plus récent des données.

Curseurs statiques fournir une vue stable des données. Ils conviennent parfaitement aux systèmes d'« entreposage » d'informations : applications pour les systèmes de reporting ou à des fins statistiques et analytiques. En plus, curseur statique s’adapte mieux que d’autres à l’échantillonnage de grandes quantités de données. En revanche, les systèmes électroniques d’achat ou de réservation de billets nécessitent une perception dynamique des informations mises à jour au fur et à mesure des modifications. Dans de tels cas, il est utilisé curseur dynamique. Dans ces applications, la quantité de données transférées est généralement faible et accessible au niveau de la ligne (enregistrement individuel). L'accès en groupe est très rare.

Gestion du curseur dans l'environnement MS SQL Server

Contrôle du curseur implémenté en exécutant les commandes suivantes :

• DÉCLARER - création ou déclaration du curseur;
• OUVRIR - curseur d'ouverture, c'est à dire. le remplir de données ;
• ALLER CHERCHER sélection à partir du curseur et modifier des lignes de données à l'aide du curseur ;
• FERMER - fermer le curseur;
• DÉSALLOCATION – libérer le curseur, c'est à dire. supprimer le curseur en tant qu'objet.

Déclaration du curseur

Le standard SQL fournit la commande suivante pour créer un curseur :

L'utilisation du mot-clé INSENSITIVE créera curseur statique. Modifications des données ne sont pas autorisés, de plus, les modifications apportées par d’autres utilisateurs ne sont pas affichées. Si le mot-clé INSENSITIVE est manquant, un curseur dynamique.

Lorsque vous spécifiez le mot-clé SCROLL, le curseur créé peut défiler dans n'importe quelle direction, vous permettant d'utiliser n'importe quelle commande de sélection. Si cet argument est omis, le curseur sera cohérent, c'est à dire. sa visualisation ne sera possible que dans une seule direction - du début à la fin.

L'instruction SELECT spécifie le corps de la requête SELECT, qui détermine l'ensemble de lignes résultant pour le curseur.

La spécification de FOR READ_ONLY crée un curseur en lecture seule et n'autorise aucune modification des données. Il diffère du statique, bien que ce dernier ne permette pas non plus de modifier les données. Peut être déclaré comme curseur en lecture seule curseur dynamique, ce qui permettra d'afficher les modifications apportées par un autre utilisateur.

Créer un curseur avec un argument FOR UPDATE vous permet d'exécuter dans le curseur changement de données soit dans les colonnes spécifiées, soit, en l'absence de l'argument OF column_name, dans toutes les colonnes.

Dans l'environnement MS SQL Server, la syntaxe suivante pour la commande de création de curseur est acceptée :

<создание_курсора>::= DECLARE nom_curseur CURSOR FOR SELECT_statement ]]

L’utilisation du mot-clé LOCAL créera un curseur local visible uniquement dans la portée du package, du déclencheur, de la procédure stockée ou de la fonction définie par l’utilisateur qui l’a créé. Lorsqu'un package, un déclencheur, une procédure ou une fonction se termine, le curseur est implicitement détruit. Pour transmettre le contenu du curseur en dehors de la construction qui l'a créé, vous devez attribuer un argument OUTPUT à son paramètre.

Si le mot-clé GLOBAL est spécifié, un curseur global est créé ; il existe jusqu'à ce que la connexion actuelle soit fermée.

La spécification de FORWARD_ONLY crée curseur série; Les données ne peuvent être échantillonnées que dans le sens allant de la première ligne à la dernière.

La spécification de SCROLL crée curseur déroulant; Les données sont accessibles dans n’importe quel ordre et dans n’importe quelle direction.

La spécification de STATIC crée curseur statique.

La spécification de KEYSET crée un curseur clé.

La spécification de DYNAMIC crée curseur dynamique.

Si vous spécifiez l'argument FAST_FORWARD pour un curseur READ_ONLY, le curseur créé sera optimisé pour un accès rapide aux données. Cet argument ne peut pas être utilisé conjointement avec les arguments FORWARD_ONLY ou OPTIMISTIC.

Un curseur créé avec l'argument OPTIMISTIC empêche la modification ou la suppression des lignes qui ont été modifiées après ouvrir le curseur.

En spécifiant l'argument TYPE_WARNING, le serveur informera l'utilisateur d'un changement implicite du type de curseur s'il est incompatible avec la requête SELECT.

Ouverture du curseur

Pour ouvrir le curseur et en le remplissant avec les données de la requête SELECT spécifiée lors de la création du curseur, utilisez la commande suivante :

Après ouvrir le curseur L'instruction SELECT associée est exécutée, dont la sortie est stockée dans une mémoire multiniveau.

Récupérer des données à partir d'un curseur

Juste après ouvrir le curseur vous pouvez sélectionner son contenu (le résultat de l'exécution de la requête correspondante) à l'aide de la commande suivante :

Spécifier FIRST renverra la toute première ligne du jeu de résultats complet du curseur, qui devient la ligne actuelle.

La spécification de LAST renvoie la ligne la plus récente du curseur. Cela devient également la ligne actuelle.

La spécification de NEXT renvoie la ligne immédiatement après la ligne actuelle dans le jeu de résultats complet. Maintenant, cela devient courant. Par défaut, la commande FETCH utilise cette méthode pour récupérer les lignes.

Le mot-clé PRIOR renvoie la ligne précédant la ligne actuelle. Cela devient actuel.

Argument ABSOLU (numéro_ligne | @numéro_ligne_variable) renvoie une ligne par son numéro ordinal absolu dans le jeu de résultats complet du curseur. Le numéro de ligne peut être spécifié à l'aide d'une constante ou comme nom d'une variable dans laquelle le numéro de ligne est stocké. La variable doit être un type de données entier. Les valeurs positives et négatives sont indiquées. Lors de la spécification d'une valeur positive, la chaîne est comptée à partir du début de l'ensemble, tandis qu'une valeur négative est comptée à partir de la fin. La ligne sélectionnée devient la ligne courante. Si une valeur nulle est spécifiée, aucune ligne n'est renvoyée.

Argument RELATIF (nombre de lignes | @nombre variable de lignes) renvoie la ligne correspondant au nombre de lignes spécifié après la ligne actuelle. Si vous spécifiez un nombre négatif de lignes, la ligne correspondant au nombre de lignes spécifié avant la ligne actuelle sera renvoyée. Spécifier une valeur nulle renverra la ligne actuelle. La ligne renvoyée devient la ligne actuelle.

À ouvrir le curseur global, vous devez spécifier le mot-clé GLOBAL avant son nom. Le nom du curseur peut également être spécifié à l'aide d'une variable.

En conception DANS @nom_variable [,...n] une liste de variables est spécifiée dans laquelle les valeurs de colonne correspondantes de la ligne renvoyée seront stockées. L'ordre de spécification des variables doit correspondre à l'ordre des colonnes dans le curseur et le type de données de la variable doit correspondre au type de données dans la colonne du curseur. Si la construction INTO n'est pas spécifiée, alors le comportement de la commande FETCH ressemblera à celui de la commande SELECT - les données sont affichées à l'écran.

Modification et suppression de données

Pour apporter des modifications à l'aide d'un curseur, vous devez émettre une commande UPDATE au format suivant :

Plusieurs colonnes de la ligne actuelle du curseur peuvent être modifiées en une seule opération, mais elles doivent toutes appartenir à la même table.

Pour supprimer des données à l'aide d'un curseur, utilisez la commande DELETE au format suivant :

En conséquence, la ligne définie actuellement dans le curseur sera supprimée.

Fermer le curseur

Après la fermeture, le curseur devient inaccessible aux utilisateurs du programme. Lors de la fermeture, toutes les serrures installées lors de son fonctionnement sont supprimées. La fermeture ne peut être appliquée qu'aux curseurs ouverts. Fermé mais pas curseur libéré pourra être rouverte. Il n'est pas permis de fermer un curseur non ouvert.

Relâchez le curseur

Fermer le curseur ne libère pas forcément la mémoire qui lui est associée. Certaines implémentations doivent explicitement le libérer à l'aide de l'instruction DEALLOCATE. Après relâcher le curseur La mémoire est également libérée, permettant de réutiliser le nom du curseur.

Pour contrôler si la fin du curseur a été atteinte, il est recommandé d'utiliser la fonction : @@FETCH_STATUS

La fonction @@FETCH_STATUS renvoie :

0 si la récupération a réussi ;

1 si la récupération a échoué en raison d'une tentative de récupération d'une ligne en dehors du curseur ;

2 si la récupération a échoué en raison d'une tentative d'accès à une ligne supprimée ou modifiée.

DÉCLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Liste de courses" DÉCLARE klient_cursor CURSOR LOCAL FOR SELECT Code client, société, nom de famille FROM Client WHERE Ville="Moscou" ORDER BY Société, nom de famille OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BEGIN SELECT @message="Client "+@fam+ "Company "+ @firm PRINT @message SELECT @message="Nom du produit Date d'achat Coût" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Produit.Nom, Transaction.Date, Produit .Price* Transaction.Quantité AS Coût FROM Produit INNER JOIN Transaction ON Produit. Code produit=Transaction.Code produit WHERE Transaction.Code client=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "Aucun achat" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST (@d AS CHAR(12))+" "+ CAST (@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Coût total "+ CAST (@s AS CHAR(6)) PRINT @message -- passer au client suivant-- FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Exemple 13.6. Un curseur pour afficher une liste des biens achetés par les clients de Moscou et leur coût total.

Exemple 13.7. Développer un curseur déroulant pour les clients de Moscou. Si le numéro de téléphone commence par 1, supprimez le client avec ce numéro et dans la première entrée du curseur, remplacez le premier chiffre du numéro de téléphone par 4.

DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "Liste des clients" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Firm, Last Name, Phone FROM Client OÙ Ville ="Moscou" ORDER BY Company, Last name FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ " Company "+ @firm " Phone " + @tel PRINT @message -- si le numéro de téléphone commence par 1, -- supprimez le client avec ce numéro SI @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- passez au suivant client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- dans la première entrée, remplacez le premier chiffre du numéro de téléphone par 4 UPDATE Client SET Phone ='4' + DROITE (@ tel,LEN (@tel)-1)) OÙ ACTUEL DE klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Téléphone "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Exemple 13.7. Curseur déroulant pour les clients de Moscou.

Exemple 13.8. Usage curseur comme paramètre de sortie de la procédure. La procédure renvoie un ensemble de données - une liste de produits.

L'appel de la procédure et l'impression des données à partir du curseur de sortie s'effectuent comme suit :

DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n FIN FERMER @my_cur DEALLOCATE @my_cur