Portée des noms de curseur Transact-SQL
Microsoft SQL Server 2005 prend en charge les mots clés GLOBAL et LOCAL dans l'instruction DECLARE CURSOR afin de définir la portée du nom du curseur. GLOBAL spécifie que le nom du curseur est global pour la connexion. LOCAL spécifie que le nom du curseur est LOCAL pour la procédure stockée, le déclencheur ou le lot d'instructions contenant l'instruction DECLARE CURSOR.
Avant la version 7.0 de Microsoft SQL Server, les noms des curseurs Transact-SQL étaient globaux pour la connexion. Vous pouviez exécuter une procédure stockée qui crée un curseur, puis appeler une autre procédure stockée pour extraire les lignes de ce curseur :
USE AdventureWorks;
GO
CREATE PROCEDURE OpenCrsr AS
DECLARE SampleCrsr CURSOR FOR
SELECT TOP (20)LastName
FROM Person.Contact
WHERE LastName LIKE 'S%';
OPEN SampleCrsr;
GO
CREATE PROCEDURE ReadCrsr AS
FETCH NEXT FROM SampleCrsr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM SampleCrsr
END
GO
EXEC OpenCrsr; /* DECLARES and OPENS SampleCrsr. */
GO
EXEC ReadCrsr; /* Fetches the rows from SampleCrsr. */
GO
CLOSE SampleCrsr;
GO
DEALLOCATE SampleCrsr;
GO
Les curseurs locaux offrent une importante protection des curseurs implémentés dans les procédures stockées et les déclencheurs. Les curseurs globaux ne peuvent pas être référencés en dehors de la procédure stockée ou du déclencheur dans lesquels ils sont déclarés. Ils peuvent donc être modifiés par inadvertance à l'aide d’instructions situées en dehors de la procédure stockée ou du déclencheur. Les curseurs locaux sont plus sûrs que les curseurs globaux car ils ne peuvent pas être référencés en dehors d'une procédure stockée, à moins d'être délibérément renvoyés au programme appelant sous la forme d'un paramètre de sortie de curseur.
Étant donné que les curseurs globaux peuvent être référencés en dehors d'une procédure stockée ou d'un déclencheur, ils peuvent avoir des effets secondaires involontaires pouvant influencer les autres instructions. Par exemple, une procédure stockée crée un curseur global appelé xyz et laisse le curseur ouvert lorsqu'elle s'arrête. La tentative de déclaration d'un autre curseur global portant le nom xyz à l'issue de l'exécution de la procédure stockée échoue et génère une erreur de nom en double.
Les curseurs globaux et locaux ont des espaces de noms différents. Il est donc possible de trouver à la fois un curseur global et un curseur local portant le même nom au même moment. Les instructions Transact-SQL qui acceptent un paramètre de nom de curseur prennent également en charge le mot clé GLOBAL pour identifier la portée du nom. Si GLOBAL n'est pas spécifié, et s'il existe à la fois un curseur local et un curseur global dont le nom est indiqué dans le paramètre de nom de curseur, c'est le curseur local qui est référencé.
L'option de base de données CURSOR_DEFAULT, définie au moyen de l'instruction ALTER DATABASE, contrôle la valeur par défaut prise par l'instruction DECLARE CURSOR lorsque ni l'option LOCAL, ni l'option GLOBAL n'est spécifiée. La valeur actuelle de cette option de base de données est stockée dans la vue de catalogue sys.databases. Si la valeur dans la colonne local_cursor_default de la vue de catalogue sys.databases est true, les curseurs Transact-SQL sont LOCAL par défaut. Si l'option est false, les curseurs Transact-SQL sont GLOBAL par défaut. Dans SQL Server 2005, l'option de base de données elle-même prend la valeur FALSE (GLOBAL) par assurer la continuité avec les versions antérieures de SQL Server.
Les procédures stockées qui utilisent les instructions DECLARE et OPEN pour déclarer et ouvrir des curseurs locaux peuvent passer ces curseurs sous la forme de paramètres de sortie au programme appelant (procédure stockée, déclencheur ou lot d'instructions). Cette opération s'effectue à l'aide d'un paramètre OUTPUT défini avec le nouveau type de données CURSOR VARYING. Les variables de curseur ne peuvent être utilisées que comme paramètres de sortie OUTPUT. Elles ne peuvent pas servir de paramètres d'entrée. Le curseur doit être ouvert lorsque la procédure stockée se termine afin d'être renvoyée dans un paramètre OUTPUT. Les variables locales peuvent également être déclarées avec le nouveau type de données CURSOR afin de contenir une référence à un curseur local.
USE AdventureWorks;
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT TOP (20) LastName
FROM Person.Contact
WHERE LastName LIKE 'S%';
OPEN @OutCrsr;
GO
/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR;
/* Execute the procedure created earlier to fill
the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT;
/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @CrsrVar
END;
CLOSE @CrsrVar;
DEALLOCATE @CrsrVar;
GO
Les API de bases de données ne prennent pas en charge les paramètres de sortie des curseurs dans les procédures stockées. Une procédure stockée contenant un paramètre de sortie de curseur ne peut pas être exécutée directement à partir d'une fonction API de bases de données. Ces procédures stockées ne peuvent être exécutées qu'à partir d'une autre procédure stockée, d'un déclencheur ou d'un lot ou script Transact-SQL.
Un curseur GLOBAL est disponible tant qu'il n'a pas été explicitement désalloué ou que la connexion n'a pas été fermée. Un curseur LOCAL est implicitement désalloué à la fin de l'exécution de la procédure stockée, du déclencheur ou du lot d'instructions dans lesquels il a été créé, à moins d'avoir été renvoyé sous forme de paramètre. Le curseur LOCAL est ensuite implicitement désalloué lorsque le paramètre ou la variable faisant référence à ce dernier dans le code d'appel de la procédure sort de l'étendue.
Voir aussi
Autres ressources
ALTER DATABASE (Transact-SQL)
DECLARE CURSOR (Transact-SQL)