DECLARE CURSOR (Transact-SQL)

 

ESTE TEMA SE APLICA A:síSQL Server (a partir de 2008)síBase de datos SQL de AzurenoAlmacenamiento de datos SQL de Azure noAlmacenamiento de datos paralelos

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 una sintaxis basada en el estándar ISO como una sintaxis que utiliza un conjunto de extensiones Transact-SQL.

Topic link icon Convenciones de sintaxis de Transact-SQL

  
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 ] ] ]  
[;]  

cursor_name
Es el nombre de la Transact-SQL definido por el cursor de servidor. cursor_name debe ajustarse a las reglas para 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 de tempdb; por lo tanto, las modificaciones realizadas en las tablas base no se reflejan en los datos devueltos por las búsquedas realizadas en el cursor y 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 puede ser el desplazamiento especificado 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 dentro select_statement de una declaración de cursor.

SQL Serverconvierte implícitamente el cursor a otro tipo si las cláusulas en select_statement entra 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. If OF column_name [,.. .n] se especifica, 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 de la Transact-SQL definido por el cursor de servidor. cursor_name debe ajustarse a las reglas para 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. Es posible 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 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 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.

System_CAPS_ICON_note.jpg Nota


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

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 SCROLL, FORWARD_ONLY es la opción predeterminada, salvo que se incluyan 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 utiliza. Todas las solicitudes al cursor se responden desde esta tabla temporal de tempdb; por lo tanto, las modificaciones realizadas en las tablas base no se reflejan en los datos devueltos por las búsquedas realizadas en el cursor y este cursor no admite modificaciones.

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 está integrado en una tabla de tempdb conocido como el keyset.

System_CAPS_ICON_note.jpg 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 -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 antiguos devuelven un @@FETCH_STATUS -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 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.

System_CAPS_ICON_note.jpg Nota


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 Serverbloquea las filas al leerlas en el cursor para garantizar su disponibilidad para posteriores modificaciones. 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 timestamp valores de columna o un valor de suma de comprobación si la tabla no tiene ningún timestamp columna, 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. 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 dentro select_statement de una declaración de cursor.

System_CAPS_ICON_note.jpg Nota


Puede usar una sugerencia de consulta dentro de una declaración de cursor; Sin embargo, si también utiliza la cláusula FOR UPDATE OF, especifique la opción (query_hint) después de FOR UPDATE OF.

SQL Serverconvierte implícitamente el cursor a otro tipo si las cláusulas en select_statement entra 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.

Para actualizar [OF column_name [,... n]]
Define las columnas actualizables en el cursor. If OF column_name [,... n] se proporciona, 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.

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 el desplazamiento palabras clave o INSENSITIVE antes de la palabra clave CURSOR, no puede usar todas las palabras clave entre CURSOR y FOR select_statement palabras clave. Si especifica todas las palabras clave entre CURSOR y FOR select_statement palabras clave, no se puede especificar SCROLL o INSENSITIVE antes 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 sistemaDescription
sp_cursor_listDevuelve la lista de los cursores que están visibles actualmente en la conexión y sus atributos.
sp_describe_cursorDescribe los atributos de un cursor, por ejemplo si es de solo avance o de desplazamiento.
sp_describe_cursor_columnsDescribe los atributos de las columnas en el conjunto de resultados del cursor.
sp_describe_cursor_tablesDescribe las tablas base a las que tiene acceso el cursor.

Pueden utilizar variables como parte de la select_statement que declara un cursor. Los valores de las variables de cursor no cambian después de que se declara el cursor.

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.

No puede utilizar los cursores o desencadenadores en una tabla con un índice de almacén de columnas agrupado. Esta restricción no se aplica a los índices no agrupados; Puede utilizar los cursores y desencadenadores en una tabla con un índice no clúster de almacén de columnas.

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;  

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

Adiciones de comunidad

AGREGAR
Mostrar: