DECLARE CURSOR (Transact-SQL)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

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

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis ISO:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ_ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Sintaxis extendida de Transact-SQL:

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

Nombre del cursor de Transact-SQL Server definido. cursor_name debe respetar las reglas de los identificadores.

INSENSITIVE

Define un cursor que hace una copia temporal de los datos que utiliza. Todas las solicitudes al cursor se responden desde esta tabla temporal en tempdb. Por lo tanto, las modificaciones de la tabla base no se reflejan en los datos devueltos por las capturas realizadas en este cursor y este cursor no permite modificaciones. Cuando se usa la sintaxis ISO, si se omite INSENSITIVE, las eliminaciones y actualizaciones confirmadas que se hayan realizado en las tablas subyacentes (por cualquier usuario) se reflejan en capturas posteriores.

SCROLL

Especifica que todas las opciones de captura (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) están disponibles. Si SCROLL no se especifica en una iso DECLARE CURSOR, NEXT es la única opción de captura admitida. SCROLL no se puede especificar si FAST_FORWARD también se especifica. Si SCROLL no se especifica, solo está disponible la opción NEXT fetch y el cursor se convierte en FORWARD_ONLY.

select_statement

Instrucción estándar SELECT que define el conjunto de resultados del cursor. Las palabras clave FOR BROWSEy INTO no se permiten en select_statement de una declaración de cursor.

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.

READ_ONLY

Evita que se efectúen actualizaciones a través de este cursor. No se puede hacer referencia al cursor en una WHERE CURRENT OF cláusula de una UPDATE instrucción 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 OF <column_name> [, <... n> ] se especifica , solo las columnas enumeradas permiten modificaciones. Si se especifica UPDATE sin indicar una lista de columnas, se pueden actualizar todas las columnas.

cursor_name

Nombre del cursor de Transact-SQL Server definido. cursor_name debe respetar las reglas de los identificadores.

LOCAL

Especifica que el alcance del cursor es local para el proceso por lotes, procedimiento almacenado o 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 del lote, procedimiento almacenado, desencadenador o parámetro OUTPUT del procedimiento almacenado. El parámetro OUTPUT se usa para devolver el cursor local al proceso por lotes, procedimiento almacenado o desencadenador que realiza la llamada, que puede asignar el parámetro a una variable de cursor para hacer referencia al cursor cuando finalice el procedimiento almacenado. 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. Si se pasa de nuevo en un OUTPUT parámetro, el cursor se desasigna cuando la última variable que hace referencia a ella se desasigna o sale del ámbito.

GLOBAL

Especifica que el alcance del cursor es global para la conexión. Se puede hacer referencia al nombre del cursor en cualquier procedimiento almacenado o lote que se ejecute durante la conexión. La asignación del cursor solo se cancela implícitamente cuando se produce la desconexión.

FORWARD_ONLY

Especifica que el cursor solo se puede desplazar hacia delante y de la primera a la última fila. FETCH NEXT es la única opción de captura admitida. Todas las instrucciones insert, update y delete realizadas por el usuario actual (o confirmadas por otros usuarios) que afectan a las filas del conjunto de resultados, son visibles a medida que se capturan las filas. Sin embargo, dado que el cursor no se puede desplazar hacia atrás, los cambios realizados en las filas de la base de datos después de capturar la fila no son visibles a través del cursor. Los cursores de solo avance son dinámicos de forma predeterminada, lo que significa que todos los cambios se detectan cuando se procesa la fila actual. Esto proporciona una apertura del cursor más rápida y permite que el conjunto de resultados muestre las actualizaciones realizadas en las tablas subyacentes. Aunque los cursores de solo avance no admiten el desplazamiento hacia atrás, las aplicaciones pueden volver al principio del conjunto de resultados cerrando y reabrindo el cursor.

Si se especifica FORWARD_ONLY sin las palabras clave STATIC, KEYSET o DYNAMIC, el cursor opera como un cursor dinámico. Cuando FORWARD_ONLY o SCROLL no se especifican, FORWARD_ONLY es el valor predeterminado, a menos que se especifiquen las palabras clave STATIC, KEYSETo DYNAMIC . El valor predeterminado de los cursores STATIC, KEYSET y DYNAMIC es SCROLL. Al contrario que las API de bases de datos, como ODBC y ADO, FORWARD_ONLY es compatible con los cursores STATIC, KEYSET y DYNAMIC de Transact-SQL.

STATIC

Especifica que el cursor siempre muestra el conjunto de resultados como estaba al abrir el cursor por primera vez, y realiza una copia temporal de los datos que va a usar el cursor. Todas las solicitudes al cursor se responden desde esta tabla temporal en tempdb. Por lo tanto, las inserciones, las actualizaciones y las eliminaciones realizadas en las tablas base no se reflejan en los datos devueltos por las capturas realizadas en este cursor, y este cursor no detecta los cambios realizados en la pertenencia, el orden o los valores del conjunto de resultados después de abrir el cursor. Los cursores estáticos pueden detectar sus propias actualizaciones, eliminaciones e inserciones, aunque no son necesarios para hacerlo.

Por ejemplo, suponga que un cursor estático captura una fila y, después, otra aplicación la actualiza. Si la aplicación vuelve a capturar la fila del cursor estático, los valores que ve son iguales, a pesar de los cambios realizados por la otra aplicación. Se admiten todos los tipos de desplazamiento.

KEYSET

Especifica que la pertenencia y el orden de las filas del cursor se fijan cuando se abre este cursor. El conjunto de claves que identifican de forma única las filas se integra en una tabla conocida tempdb como conjunto de claves. Este cursor proporciona la funcionalidad entre un cursor estático y dinámico en su capacidad para detectar los cambios. Al igual que un cursor estático, no siempre detecta cambios en la pertenencia y el orden del conjunto de resultados. Como un cursor dinámico, detecta cambios en los valores de las filas del conjunto de resultados.

Los cursores controlados por conjunto de claves se supervisan mediante un conjunto de identificadores únicos (claves) denominado conjunto de claves. Las claves se generan a partir de un conjunto de columnas que identifican las filas del conjunto de resultados de forma unívoca. El conjunto de claves es el conjunto de valores de clave de todas las filas devueltas por la instrucción de consulta. Con los cursores controlados por conjunto de claves, se crea y se guarda una clave para cada fila del cursor y se almacena en la estación de trabajo cliente o en el servidor. Al acceder a cada fila, se usa la clave almacenada para capturar los valores de datos actuales desde el origen de datos. En un cursor controlado por conjunto de claves, la pertenencia al conjunto de resultados se inmoviliza cuando se completa totalmente el conjunto de claves. Después, las adiciones o actualizaciones que afectan a la pertenencia no forman parte del conjunto de resultados hasta que se vuelva a abrir.

Los cambios en los valores de datos (realizados por el propietario del conjunto de claves o por otros procesos) son visibles cuando el usuario se desplaza por el conjunto de resultados:

• Si se elimina una fila, un intento de capturar la fila devuelve un @@FETCH_STATUS de -2 porque la fila eliminada aparece como un hueco en el conjunto de resultados. La clave para la fila existe en el conjunto de claves, pero la fila ya no existe en el conjunto de resultados.

• Las operaciones de inserción realizadas fuera del cursor (por otros procesos) son visibles solo si el cursor se cierra y se vuelve a abrir. Las operaciones de inserción realizadas desde dentro del cursor son visibles al final del conjunto de resultados.

• 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 e intenta capturar la fila con los valores antiguos devuelve un @@FETCH_STATUS de -2. Los nuevos valores son visibles si la actualización se realiza a través del cursor especificando la cláusula WHERE CURRENT OF.

DYNAMIC

Define un cursor que refleja todos los cambios realizados en las filas de su conjunto de resultados cuando se desplaza alrededor del cursor y se captura un registro nuevo, independientemente de si los cambios se producen desde dentro del cursor o los realizan otros usuarios fuera del cursor. Por tanto, todas las instrucciones UPDATE, INSERT y DELETE que realizan todos los usuarios son visibles a través del cursor. Los valores de los datos, el orden y la pertenencia de las filas pueden cambiar en cada captura. La ABSOLUTE opción fetch no se admite con cursores dinámicos. Las actualizaciones realizadas fuera del cursor no son visibles hasta que se confirmen (a menos que el nivel de aislamiento de transacción del cursor esté establecido UNCOMMITTEDen ).

Por ejemplo, supongamos que un cursor dinámico captura dos filas y otra aplicación actualiza una de esas filas y elimina la otra. Si el cursor dinámico captura esas filas, no encuentra la fila eliminada, pero muestra los nuevos valores de la fila actualizada.

FAST_FORWARD

Especifica un cursor FORWARD_ONLY, READ_ONLY con optimizaciones de rendimiento habilitadas. FAST_FORWARD no se puede especificar si SCROLL también se especifica o FOR_UPDATE . Este tipo de cursor no permite modificaciones de datos desde dentro del cursor.

READ_ONLY

Evita que se efectúen actualizaciones a través de este cursor. No se puede hacer referencia al cursor en una WHERE CURRENT OF cláusula de una UPDATE instrucción 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. SCROLL_LOCKS no se puede especificar si FAST_FORWARD también se especifica o STATIC .

OPTIMISTIC

Especifica que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor no se realizan correctamente, si la fila se actualizó desde que se leyó en el cursor. SQL Server no bloquea las filas cuando se leen en el cursor. En su lugar, usa 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, la actualización o eliminación posicionada fracasa. OPTIMISTIC no se puede especificar si FAST_FORWARD también se especifica.

Si STATIC se especifica junto con el OPTIMISTIC argumento cursor, la combinación de los dos se convierte implícitamente en el equivalente de la combinación de uso STATIC y argumentos, o los STATIC argumentos y READ_ONLY FORWARD_ONLY .

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.

No se envía ninguna advertencia al cliente cuando se usa la combinación de argumentos de OPTIMISTIC cursor y STATIC y el cursor se convierte implícitamente en el equivalente de un STATIC READ_ONLY cursor o STATIC FORWARD_ONLY . La conversión a READ_ONLY se convierte en un FAST_FORWARD cursor y READ_ONLY desde la perspectiva de los clientes.

select_statement

Instrucción estándar SELECT que define el conjunto de resultados del cursor. Las palabras clave , , y INTO no se permiten en select_statement de una declaración de FOR BROWSEcursor. COMPUTE BYCOMPUTE

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.

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.

Observaciones

DECLARE CURSOR define los atributos de un cursor de servidor de Transact-SQL, como su comportamiento de desplazamiento y la consulta usada para generar el conjunto de resultados sobre el que opera el cursor. La instrucción OPEN rellena 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 usa el cursor.

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

No se pueden mezclar las dos formas. Si especifica las SCROLL palabras clave o INSENSITIVE antes de la CURSOR palabra clave , no puede usar ninguna palabra clave entre las CURSOR palabras clave y FOR <select_statement> . Si especifica alguna palabra clave entre las CURSOR palabras clave y FOR <select_statement> , no puede especificar SCROLL ni INSENSITIVE antes de la CURSOR palabra clave .

Si un DECLARE CURSOR uso de la sintaxis de Transact-SQL no especifica READ_ONLY, OPTIMISTICo SCROLL_LOCKS, el valor predeterminado es el siguiente:

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

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

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

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

Después de declarar un cursor, estos procedimientos almacenados del sistema se pueden usar para determinar las características del cursor.

Las variables se pueden usar como parte del select_statement que declara un cursor. Los valores de las variables de cursor no cambian después de declarar un cursor.

Permisos

Los permisos para usar DECLARE CURSOR se asignan de forma predeterminada a los usuarios que tengan permisos para usar SELECT en las vistas, tablas y columnas usadas en el cursor.

Limitaciones

No puede usar cursores ni desencadenadores en una tabla con un índice de almacén de columnas agrupado. Esta restricción no se aplica a los índices de almacén de columnas no agrupados. No puede usar cursores ni desencadenadores en una tabla con un índice de almacén de columnas no agrupado.

Ejemplos

A Uso de cursores básicos y 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 captura disponible porque no se especifica la SCROLL opción .

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

B. Uso de cursores anidados para generar la salida del informe

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;

Contenido relacionado

• @@FETCH_STATUS (Transact-SQL)
• CLOSE (Transact-SQL)
• Cursores (Transact-SQL)
• DEALLOCATE (Transact-SQL)
• FETCH (Transact-SQL)
• SELECT (Transact-SQL)
• sp_configure (Transact-SQL)