DECLARE CURSOR (Transact-SQL)

Define los atributos de un cursor de servidor de Transact-SQL, como su comportamiento de desplazamiento y la consulta utilizada para generar el conjunto de resultados sobre el que opera el cursor. DECLARE CURSOR acepta tanto la sintaxis basada en el estándar ISO como la sintaxis que utiliza un conjunto de extensiones Transact-SQL.

Convenciones de sintaxis de Transact-SQL

Sintaxis

ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]

Argumentos

• cursor_name
  Es el nombre del cursor de servidor de Transact-SQL definido. cursor_name debe cumplir las reglas de los identificadores.

• INSENSITIVE
  Define un cursor que hace una copia temporal de los datos que utiliza. Todas las solicitudes que se realizan al cursor se responden desde esta tabla temporal de tempdb; por tanto, las modificaciones realizadas en las tablas base no se reflejan en los datos devueltos por las operaciones de captura realizadas en el cursor y además este cursor no admite modificaciones. Cuando se utiliza la sintaxis de ISO, si se omite INSENSITIVE, las eliminaciones y actualizaciones confirmadas realizadas en las tablas subyacentes (por cualquier usuario) se reflejan en capturas posteriores.

• SCROLL
  Especifica que están disponibles todas las opciones de captura (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Si no se especifica SCROLL en una instrucción DECLARE CURSOR de ISO, la única opción de captura que se admite es NEXT. No es posible especificar SCROLL si se incluye también FAST_FORWARD.

• select_statement
  Es una instrucción SELECT estándar que define el conjunto de resultados del cursor. Las palabras clave FOR BROWSE e INTO no están permitidas en la instrucción select_statement de una declaración de cursor.

  SQL Server convierte implícitamente el cursor en otro tipo si las cláusulas de la instrucción select_statement entran en conflicto con la funcionalidad del tipo de cursor solicitado.

• READ ONLY
  Evita que se efectúen actualizaciones a través de este cursor. No es posible hacer referencia al cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE. Esta opción reemplaza la capacidad predeterminada de actualizar el cursor.

• UPDATE [OF column_name [,...n]]
  Define las columnas actualizables en el cursor. Si se especifica OF column_name [,...n], solo las columnas enumeradas admiten modificaciones. Si se especifica UPDATE sin indicar una lista de columnas, se pueden actualizar todas las columnas.

• cursor_name
  Es el nombre del cursor de servidor de Transact-SQL definido. cursor_name debe cumplir las reglas de los identificadores.

• LOCAL
  Especifica que el ámbito del cursor es local para el proceso por lotes, el procedimiento almacenado o el desencadenador en que se creó el cursor. El nombre del cursor solo es válido en este ámbito. Se puede hacer referencia al cursor mediante variables de cursor locales en el proceso por lotes, procedimiento almacenado o desencadenador, o en el parámetro OUTPUT de un procedimiento almacenado. El parámetro OUTPUT se utiliza para devolver el cursor local al proceso por lotes, procedimiento almacenado o desencadenador que realiza la llamada, el cual puede asignar el parámetro a una variable de cursor para hacer referencia al cursor después de que el procedimiento almacenado finalice. La asignación del cursor se cancela implícitamente cuando el proceso por lotes, procedimiento almacenado o desencadenador finaliza, a menos que el cursor se haya devuelto en un parámetro OUTPUT. En ese caso, la asignación del cursor se cancela cuando se cancela la asignación de la última variable que hace referencia al mismo o cuando ésta se sale del ámbito.

• GLOBAL
  Especifica que el ámbito del cursor es global para la conexión. Puede hacerse referencia al nombre del cursor en cualquier procedimiento almacenado o proceso por lotes que se ejecute en la conexión. La asignación del cursor solo se cancela implícitamente cuando se produce la desconexión.

  [!NOTA]

  Si no se especifica GLOBAL ni LOCAL, el valor predeterminado se controla mediante la configuración de la opción de base de datos default to local cursor.

• FORWARD_ONLY
  Especifica que el cursor solo se puede desplazar de la primera a la última fila. FETCH NEXT es la única opción de captura admitida. Si se especifica FORWARD_ONLY sin las palabras clave STATIC, KEYSET o DYNAMIC, el cursor funciona como un cursor DYNAMIC. Cuando no se especifica FORWARD_ONLY ni tampoco SCROLL, FORWARD_ONLY es la opción predeterminada, salvo que se especifiquen las palabras clave STATIC, KEYSET o DYNAMIC. Los cursores STATIC, KEYSET y DYNAMIC utilizan SCROLL como valor predeterminado. A diferencia de las API de base de datos, como ODBC y ADO, FORWARD_ONLY se puede utilizar con los cursores STATIC, KEYSET y DYNAMIC de Transact-SQL.

• STATIC
  Define un cursor que hace una copia temporal de los datos que va a utilizar. Todas las solicitudes que se realizan al cursor se responden desde esta tabla temporal de tempdb; por tanto, las modificaciones realizadas en las tablas base no se reflejan en los datos devueltos por las operaciones de captura realizadas en el cursor y además este cursor no admite modificaciones.

• KEYSET
  Especifica que la pertenencia y el orden de las filas del cursor se fijan cuando se abre el cursor. El conjunto de claves que identifica las filas de forma única está integrado en la tabla denominada keyset de tempdb.

  [!NOTA]

  Si la consulta hace referencia por lo menos a una tabla sin un índice único, el cursor de conjunto de claves se convierte en cursor estático.

  Los cambios realizados en valores de las tablas base que no son de clave, ya sean realizados por el propietario del cursor o confirmados por otros usuarios, son visibles cuando el propietario se desplaza por el cursor. Las inserciones realizadas por otros usuarios no son visibles (no es posible hacer inserciones a través de un cursor de servidor de Transact-SQL). Si se elimina una fila, un intento para capturar la fila devuelve un @@FETCH_STATUS de -2. Las actualizaciones de valores de clave de fuera del cursor son similares a la eliminación de la fila anterior seguida por la inserción de la nueva fila. La fila con los nuevos valores no está visible, y los intentos de capturar la fila con los valores anteriores devuelven un @@FETCH_STATUS de -2. Los nuevos valores están visibles si la actualización se realiza a través del cursor especificando la cláusula WHERE CURRENT OF.

• DYNAMIC
  Define un cursor que, al desplazarse por él, refleja en su conjunto de resultados todos los cambios realizados en los datos de las filas. Los valores de los datos, el orden y la pertenencia de las filas pueden cambiar en cada operación de captura. La opción de captura ABSOLUTE no se puede utilizar en los cursores dinámicos.

• FAST_FORWARD
  Especifica un cursor FORWARD_ONLY, READ_ONLY con las optimizaciones de rendimiento habilitadas. No se puede especificar FAST_FORWARD si se especifica también SCROLL o FOR_UPDATE.

  [!NOTA]

  En SQL Server 2005 y versiones posteriores, FAST_FORWARD y FORWARD_ONLY pueden usarse en la misma instrucción DECLARE CURSOR.

• READ_ONLY
  Evita que se efectúen actualizaciones a través de este cursor. No es posible hacer referencia al cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE. Esta opción reemplaza la capacidad predeterminada de actualizar el cursor.

• SCROLL_LOCKS
  Especifica que existan garantías de que las actualizaciones o las cancelaciones posicionadas realizadas a través del cursor se lleven a cabo correctamente. SQL Server bloquea las filas mientras se leen en el cursor para garantizar su disponibilidad en modificaciones posteriores. No es posible especificar SCROLL_LOCKS si se especifica también FAST_FORWARD o STATIC.

• OPTIMISTIC
  Especifica que las actualizaciones o las cancelaciones posicionadas realizadas a través del cursor no se lleven a cabo correctamente si la fila se ha actualizado desde que se leyó en el cursor. SQL Server no bloquea las filas cuando se leen en el cursor. En su lugar, utiliza comparaciones de valores de columna timestamp o un valor de suma de comprobación si la tabla no tiene columnas timestamp, para determinar si la fila se ha modificado después de leerla en el cursor. Si la fila se ha modificado, el intento de actualización o eliminación posicionada genera un error. No es posible especificar OPTIMISTIC si se especifica también FAST_FORWARD.

• TYPE_WARNING
  Especifica que se envía un mensaje de advertencia al cliente si el cursor se convierte implícitamente del tipo solicitado a otro.

• select_statement
  Es una instrucción SELECT estándar que define el conjunto de resultados del cursor. Las palabras clave COMPUTE, COMPUTE BY, FOR BROWSE e INTO no están permitidas en la instrucción select_statement de una declaración de cursor.

  [!NOTA]

  Puede utilizar una sugerencia de consulta en una declaración de cursor; sin embargo, si también utiliza la cláusula FOR UPDATE OF, debe especificar OPTION (query_hint) después de FOR UPDATE OF.

  SQL Server convierte implícitamente el cursor a otro tipo si las cláusulas de la instrucción select_statement entran en conflicto con la funcionalidad del tipo de cursor solicitado. Para obtener más información, vea el tema relativo a las conversiones de cursor implícitas.

• FOR UPDATE [OF column_name [,...n]]
  Define las columnas actualizables en el cursor. Si se especifica OF column_name [,...n], solo las columnas enumeradas admiten modificaciones. Si se especifica UPDATE sin una lista de columnas, se pueden actualizar todas las columnas, a menos que se haya especificado la opción de simultaneidad READ_ONLY.

Comentarios

DECLARE CURSOR define los atributos de un cursor de servidor de Transact-SQL, como su comportamiento de desplazamiento y la consulta utilizada para generar el conjunto de resultados sobre el que opera el cursor. La instrucción OPEN llena el conjunto de resultados y la instrucción FETCH devuelve una fila del conjunto de resultados. La instrucción CLOSE libera el conjunto de resultados actual asociado al cursor. La instrucción DEALLOCATE libera los recursos que utiliza el cursor.

La primera forma de la instrucción DECLARE CURSOR usa la sintaxis de ISO para declarar comportamientos del cursor. La segunda forma de DECLARE CURSOR utiliza extensiones de Transact-SQL que permiten definir cursores con los mismos tipos de cursor utilizados en las funciones de cursor de la API de base de datos de ODBC o ADO.

No se pueden combinar las dos formas. Si especifica las palabras clave SCROLL o INSENSITIVE antes de la palabra clave CURSOR, no puede usar ninguna palabra clave entre las palabras clave CURSOR y FOR de la instrucción select_statement. Si especifica alguna palabra clave entre CURSOR y FOR de la instrucción select_statement, no puede especificar SCROLL o INSENSITIVE delante de la palabra clave CURSOR.

Si una instrucción DECLARE CURSOR con sintaxis de Transact-SQL no especifica READ_ONLY, OPTIMISTIC o SCROLL_LOCKS, el valor predeterminado es el siguiente:

• Si la instrucción SELECT no acepta actualizaciones (permisos insuficientes, acceso a tablas remotas que no aceptan actualizaciones, etc.), el cursor es de tipo READ_ONLY.

• El valor predeterminado de los cursores de tipo STATIC y FAST_FORWARD es READ_ONLY.

• El valor predeterminado de los cursores de tipo KEYSET y DYNAMIC es OPTIMISTIC.

Solo se puede hacer referencia a nombres de cursores mediante otras instrucciones Transact-SQL. No se puede hacer referencia a nombres de cursores mediante funciones de la API de base de datos. Por ejemplo, después de declarar un cursor, no se puede hacer referencia al nombre del cursor desde funciones o métodos de OLE DB, ODBC o ADO. No se pueden capturar las filas del cursor con las funciones o métodos de captura de las API; las filas solo se pueden capturar mediante instrucciones FETCH de Transact-SQL.

Una vez que se ha declarado un cursor, se pueden utilizar estos procedimientos almacenados del sistema para determinar las características del cursor.

Procedimientos almacenados del sistema	Descripción
sp_cursor_list	Devuelve la lista de los cursores que están visibles actualmente en la conexión y sus atributos.
sp_describe_cursor	Describe los atributos de un cursor, por ejemplo si es de solo avance o de desplazamiento.
sp_describe_cursor_columns	Describe los atributos de las columnas en el conjunto de resultados del cursor.
sp_describe_cursor_tables	Describe las tablas base a las que tiene acceso el cursor.

Se pueden utilizar variables como parte de la instrucción select_statement que declara un cursor. Los valores de las variables de cursor no cambian después de que se declara el cursor.

Permisos

Los permisos para utilizar DECLARE CURSOR corresponden de manera predeterminada a los usuarios que dispongan de permisos para utilizar SELECT en las vistas, tablas y columnas utilizadas en el cursor.

Ejemplos

A.Uso de cursores simples y su sintaxis

El conjunto de resultados generado al abrir este cursor contiene todas las filas y todas las columnas de la tabla. Este cursor se puede actualizar, y todas las actualizaciones y eliminaciones se representan en las búsquedas realizadas para este cursor. FETCH NEXT es la única búsqueda disponible porque la opción SCROLL no se ha especificado.

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

B.Uso de cursores anidados para elaborar resultados de informes

En el ejemplo siguiente se muestra cómo se pueden anidar los cursores para elaborar informes complejos. El cursor interno se declara para cada proveedor.

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;

Vea también

Referencia

@@FETCH_STATUS (Transact-SQL)

CLOSE (Transact-SQL)

Cursores (Transact-SQL)

DEALLOCATE (Transact-SQL)

FETCH (Transact-SQL)

SELECT (Transact-SQL)

sp_configure (Transact-SQL)