DECLARE CURSOR (Transact-SQL)
Se aplica a: SQL Server
Azure SQL Database
Azure SQL Instancia administrada
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 ] ] ]
[ ; ]
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentació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 BROWSE
y 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.
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 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
, KEYSET
o 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áusulaWHERE CURRENT OF
.
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.
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 UNCOMMITTED
en ).
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.
Nota:
Tanto FAST_FORWARD
como FORWARD_ONLY
se pueden usar en la misma instrucción DECLARE 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 BROWSE
cursor. COMPUTE BY
COMPUTE
Nota:
Puede usar una sugerencia de consulta dentro de una declaración de cursor. Sin embargo, si también usa la FOR UPDATE OF
cláusula , especifique OPTION (<query_hint>)
después FOR UPDATE OF
de .
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
, OPTIMISTIC
o 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 esREAD_ONLY
.El valor predeterminado de los cursores
STATIC
yFAST_FORWARD
esREAD_ONLY
.El valor predeterminado de los cursores
DYNAMIC
yKEYSET
esOPTIMISTIC
.
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.
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, como si es un cursor 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. |
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;