Alcance de los nombres de cursor de Transact-SQL
MicrosoftSQL Server admite las palabras clave GLOBAL y LOCAL en la instrucción DECLARE CURSOR para definir el ámbito del nombre del cursor. GLOBAL especifica que el nombre del cursor es global para la conexión. LOCAL especifica que el nombre del cursor es LOCAL para el procedimiento almacenado, el desencadenador o el lote que contiene la instrucción DECLARE CURSOR.
En las versiones anteriores a MicrosoftSQL Server 7.0, los nombres de los cursores de Transact-SQL eran globales para la conexión. Se podía ejecutar un procedimiento almacenado para crear un cursor y, a continuación, llamar a otro procedimiento almacenado que recuperara las filas de dicho cursor:
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
Los cursores locales ofrecen una buena protección para los cursores implementados en procedimientos almacenados y desencadenadores. Se puede hacer referencia a los cursores globales fuera del procedimiento almacenado o desencadenador en el que se declararon los cursores. Como consecuencia, es posible que las instrucciones externas al procedimiento almacenado o desencadenador los modifiquen accidentalmente. Los cursores locales resultan más seguros que los globales porque no se puede hacer referencia a ellos fuera de un procedimiento almacenado, a menos que se devuelvan deliberadamente al proceso que realiza la llamada como parámetro de salida de tipo cursor.
Dado que se puede hacer referencia a los cursores globales fuera de un procedimiento almacenado o desencadenador, éstos pueden tener efectos secundarios no deseados en otras instrucciones. Por ejemplo, un procedimiento almacenado crea un cursor global con el nombre xyz y deja el cursor abierto cuando termina. Los intentos de declarar otro cursor global con el nombre xyz, una vez que el procedimiento almacenado ha terminado, generan un error de nombre duplicado.
Los cursores globales y locales poseen espacios de nombres independientes, por lo que es posible tener un cursor global y uno local con el mismo nombre al mismo tiempo. Las instrucciones de Transact-SQL que aceptan un parámetro de nombre de cursor también admiten la palabra clave GLOBAL para identificar el ámbito del nombre. Si no se especifica GLOBAL y existe un cursor global y otro local con el mismo nombre especificado en el parámetro de nombre de cursor, se hará referencia al cursor local.
La opción de base de datos CURSOR_DEFAULT, establecida con la instrucción ALTER DATABASE, controla los valores predeterminados de la instrucción DECLARE CURSOR si no se ha especificado LOCAL ni GLOBAL. El valor actual de esta opción de base de datos se almacena en la vista de catálogo sys.databases. Si el valor de la columna local_cursor_default de la vista de catálogo sys.databases es true, Transact-SQL los cursores serán locales de forma predeterminada. Si el valor de la opción es false, Transact-SQL los cursores serán globales de forma predeterminada. En SQL Server, es valor predeterminado de la opción de base de datos es FALSE (GLOBAL) para emular el comportamiento de las versiones anteriores de SQL Server.
Los procedimientos almacenados que declaran (DECLARE) y abren (OPEN) cursores locales pueden pasar los cursores para que los utilice el procedimiento almacenado, el desencadenador o el lote que realiza la llamada. Esto se realiza mediante el parámetro OUTPUT definido con el nuevo tipo de datos CURSOR VARYING. Las variables de cursor sólo se pueden utilizar como parámetros OUTPUT. No se pueden utilizar para parámetros de entrada. Una vez finalizado el procedimiento almacenado, es preciso abrir el cursor para poder pasarlo a un parámetro OUTPUT. También es posible declarar variables locales con el nuevo tipo de datos CURSOR para que contengan una referencia a un cursor 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
Las API de bases de datos no admiten parámetros de salida de tipo cursor en los procedimientos almacenados. Un procedimiento almacenado que contiene un parámetro de salida de tipo cursor no puede ejecutarse directamente desde una función API de bases de datos. Sólo se pueden ejecutar estos procedimientos almacenados desde otro procedimiento almacenado, un desencadenador o un lote o script de Transact-SQL.
Un cursor GLOBAL está disponible hasta que se cancela explícitamente su asignación o se cierra la conexión. La asignación de los cursores LOCAL se cancela implícitamente cuando finaliza el procedimiento almacenado, el desencadenador o el lote en el que se crearon, a menos que se haya pasado el cursor como parámetro. Se cancelará implícitamente la asignación de un cursor LOCAL cuando se ejecute fuera de ámbito el parámetro o la variable que hace referencia al cursor en el código que llamó al procedimiento.