Portée des noms de curseur Transact-SQL
MicrosoftSQL Server prend en charge les mots clés GLOBAL et LOCAL dans l'instruction DECLARE CURSOR pour définir l'étendue du nom du curseur. GLOBAL spécifie que le nom du curseur est global pour la connexion. LOCAL indique que le nom du curseur est LOCAL pour la procédure stockée, le déclencheur ou le lot contenant l'instruction DECLARE CURSOR.
Avant la version 7.0 de MicrosoftSQL 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 pour les 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 à l'appelant sous forme de 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 qui influencent 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. Une tentative de déclaration d'un autre curseur global portant le nom xyz une fois la procédure stockée terminé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, l'option de base de données elle-même prend la valeur FALSE (GLOBAL) pour 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). 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 libéré 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 traitement dans lesquels il a été créé, à moins d'avoir été renvoyé sous forme de paramètre. Le curseur LOCAL est ensuite implicitement libéré 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.