DECLARE CURSOR (Transact-SQL)

Actualizado: 5 de diciembre de 2005

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 SQL-92 como la sintaxis que utiliza un conjunto de extensiones de Transact-SQL.

Convenciones de sintaxis de Transact-SQL

Sintaxis

SQL 92 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. Para obtener más información sobre las reglas de los identificadores, vea Usar identificadores como nombres de objeto.

• 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 recuperación realizadas en el cursor y además este cursor no admite modificaciones. Cuando se utiliza la sintaxis de SQL-92, si se omite INSENSITIVE, las eliminaciones y actualizaciones confirmadas realizadas en las tablas subyacentes (por cualquier usuario) se reflejan en recuperaciones posteriores.

• SCROLL
  Especifica que están disponibles todas las opciones de recuperación (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Si no se especifica SCROLL en una instrucción DECLARE CURSOR de SQL-92, la única opción de recuperación 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 COMPUTE, COMPUTE BY, FOR BROWSE e INTO no están permitidas en el argumento select_statement de una declaración de cursor.

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

• 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 que se pueden actualizar en el cursor. Si se especifica OF column_name [,...n], sólo 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. Para obtener más información sobre las reglas de los identificadores, vea Usar identificadores como nombres de objeto.

• LOCAL
  Especifica que el ámbito del cursor es local para el proceso por lotes, procedimiento almacenado o desencadenador en que se creó el cursor. El nombre del cursor sólo 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 sólo 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. En SQL Server versión 7.0, el valor predeterminado de esta opción es FALSE por compatibilidad con versiones anteriores de SQL Server, en las que todos los cursores eran globales. El valor predeterminado de esta opción puede cambiar en futuras versiones de SQL Server. Para obtener más información, vea el tema relativo a la configuración de las opciones de la base de datos.

• FORWARD_ONLY
  Especifica que el cursor sólo se puede desplazar de la primera a la última fila. FETCH NEXT es la única opción de recuperación 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 recuperación 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 y, después, se intenta recuperar, se devuelve el valor -2 en @@FETCH_STATUS. Las actualizaciones de los valores de clave desde fuera del cursor se asemejan a una eliminación de la fila antigua seguida de una inserción de la nueva. La fila con los nuevos valores no es visible y los intentos de recuperar la fila con los valores antiguos devuelven el valor -2 en @@FETCH_STATUS. Los nuevos valores son visibles si la actualización se realiza a través del cursor, al especificar 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 recuperación. La opción de recuperación 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 2000, las opciones de cursor FAST_FORWARD y FORWARD_ONLY se excluyen mutuamente. Si se especifican ambas, se genera un error. En SQL Server 2005, las dos palabras clave se pueden utilizar 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 se garantiza que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor serán correctas. Microsoft SQL Server bloquea las filas cuando se leen en el cursor para garantizar que estarán disponibles para futuras modificaciones. No es posible especificar SCROLL_LOCKS si se especifica también FAST_FORWARD o STATIC.

• OPTIMISTIC
  Especifica que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor no se realizarán correctamente si la fila se ha actualizado después de ser leída en el cursor. SQL Server no bloquea las filas al leerlas 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 el argumento 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 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 que se pueden actualizar en el cursor. Si se especifica OF column_name [,...n], sólo 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.

Notas

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 utiliza la sintaxis de SQL-92 para declarar comportamientos de 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 utilizar ninguna palabra clave entre las palabras clave CURSOR y FOR de select_statement. Si especifica palabras clave entre las palabras clave CURSOR y FOR de 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.

Sólo 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 recuperar las filas del cursor con las funciones o métodos de recuperación de las API; las filas sólo se pueden recuperar 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.

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. En SQL Server 6.5 y versiones anteriores, los valores de las variables se actualizan cada vez que se vuelve a abrir un 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. Utilizar 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 recuperaciones realizadas en el cursor. FETCH NEXT es la única recuperación disponible debido a que no se ha especificado la opción SCROLL.

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

B. Utilizar cursores anidados para elaborar resultados de informes

Este ejemplo 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)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios