Artículo
09/29/2010

Cursores de Transact-SQL

Los cursores de Transact-SQL se utilizan principalmente en procedimientos almacenados, desencadenadores y Scripts de Transact-SQL a fin de poner el contenido de un conjunto de resultados a disposición de otras instrucciones de Transact-SQL.

El proceso típico para utilizar un cursor de Transact-SQL en un procedimiento almacenado o un desencadenador es:

Indicar las variables de Transact-SQL que contendrán los datos que ha devuelto el cursor. Indicar una variable para cada columna del conjunto de resultados. Establecer variables lo suficientemente grandes para que puedan contener los valores que devuelve la columna y con un tipo de datos que se pueda convertir implícitamente desde el tipo de datos de la columna.
Asociar un cursor de Transact-SQL a una instrucción SELECT mediante la instrucción DECLARE CURSOR. La instrucción DECLARE CURSOR también define las características del cursor como, por ejemplo, el nombre y si es de sólo lectura o de sólo avance.
Utilizar la instrucción OPEN para ejecutar la instrucción SELECT y llenar el cursor.
Utilizar la instrucción FETCH INTO para recuperar filas individuales y mover los datos de cada columna a la variable especificada. Posteriormente, otras instrucciones de Transact-SQL pueden hacer referencia a esas variables para obtener acceso a los valores de datos recuperados. Los cursores Transact-SQL no admiten la recuperación de bloques de filas.
Cuando haya terminado con el cursor, utilice la instrucción CLOSE. Al cerrar un cursor se liberan algunos recursos, como el conjunto de resultados del cursor y sus bloqueos en la fila actual, pero la estructura del cursor sigue lista para procesar en caso de volver a emitir una instrucción OPEN. Dado que el cursor sigue estando presente, no puede volver a utilizar su nombre en este momento. La instrucción DEALLOCATE libera completamente todos los recursos asignados al cursor, incluido su nombre. Una vez cancelada la asignación de un cursor, se debe emitir una instrucción DECLARE para volver a generarlo.

Supervisar la actividad de cursores de Transact-SQL

Puede utilizar el procedimiento almacenado del sistema sp_cursor_list para obtener una lista de los cursores visibles en la conexión actual, y sp_describe_cursor, sp_describe_cursor_columns y sp_describe_cursor_tables para determinar las características de un cursor.

Una vez abierto el cursor, la función @@CURSOR_ROWS o la columna cursor_rows que devuelve sp_cursor_list o sp_describe_cursor indica el número de filas del cursor.

Tras cada instrucción FETCH, se actualiza @@FETCH_STATUS para reflejar el estado de la última recuperación. También se puede obtener esta información de estado en la columna fetch_status que devuelve sp_describe_cursor. @@FETCH_STATUS informa condiciones como, por ejemplo, si se recupera más allá de la primera o última fila del cursor. @@FETCH_STATUS es global para la conexión y se restablece con cada recuperación que se realiza en cualquier cursor abierto para la conexión. Si va a necesitar el estado posteriormente, guarde @@FETCH_STATUS en una variable de usuario antes de ejecutar otra instrucción en la conexión. Aunque la siguiente instrucción puede que no sea FETCH, podría ser una instrucción INSERT, UPDATE o DELETE que active un desencadenador con instrucciones FETCH que restablezcan @@FETCH_STATUS. La columna fetch_status que devuelve sp_describe_cursor es específica del cursor establecido y no se ve afectada por las instrucciones FETCH que hacen referencia a otros cursores. Sin embargo, sp_describe_cursor se ve afectada por las instrucciones FETCH que hacen referencia al mismo cursor, por lo que se debe utilizar con cuidado.

Cuando se completa una instrucción FETCH, el cursor se ubica en la fila recuperada. La fila recuperada se conoce como la fila actual. Si no se ha declarado el cursor como de sólo lectura, se puede ejecutar una instrucción UPDATE o DELETE con la cláusula WHERE CURRENT OF cursor_name para modificar la fila actual.

El nombre que la instrucción DECLARE CURSOR asigna a un cursor de Transact-SQL puede ser global o local. Cualquier lote, procedimiento almacenado o desencadenador que se ejecute en la misma conexión hace referencia a nombres de cursores globales. No se puede hacer referencia a nombres de cursores locales fuera del lote, procedimiento almacenado o desencadenador en que se declaró el cursor. Por lo tanto, los cursores locales de los desencadenadores y procedimientos almacenados están protegidos contra referencias no deseadas fuera del procedimiento almacenado o desencadenador.

Utilizar la variable cursor

MicrosoftSQL Server también admite variables con el tipo de datos cursor. Existen dos métodos distintos para asociar un cursor con una variable cursor:

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks.Person.Contact

SET @MyVariable = MyCursor
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks.Person.Contact;
DEALLOCATE MyCursor;

Tras asociar un cursor a una variable cursor, se puede utilizar la variable cursor en lugar del nombre del cursor en las instrucciones de cursor de Transact-SQL. El tipo de datos cursor también se puede asignar a los parámetros de salida de un procedimiento almacenado, los cuales pueden asociarse asimismo con un cursor. Esto permite que los procedimientos almacenados expongan cursores locales de una forma controlada.

Hacer referencia a cursores de Transact-SQL

Sólo se hace referencia a nombres de cursores de Transact-SQL y variables mediante instrucciones de Transact-SQL; no se les puede hacer referencia desde funciones de la API de OLE DB, ODBC y ADO. Por ejemplo, si se utilizan DECLARE CURSOR y OPEN en un cursor de Transact-SQL, no se podrán utilizar las funciones SQLFetch o SQLFetchScroll de ODBC para recuperar una fila del cursor de Transact-SQL. Las aplicaciones que necesitan procesamiento de cursores y utilizan estas API deben utilizar la compatibilidad de cursor integrada en la API de base de datos en lugar de cursores de Transact-SQL.

Puede utilizar cursores de Transact-SQL en aplicaciones si utiliza FETCH y enlaza cada columna que devuelve FETCH a una variable de programa. Sin embargo, dado que la instrucción FETCH de Transact-SQL no admite lotes, ésta es la forma menos eficaz de devolver datos a una aplicación. Para recuperar cada fila es preciso realizar un viaje de ida y vuelta al servidor. Es más eficaz utilizar las funciones del cursor integradas en la API de la base de datos que admitir la recuperación de lotes de filas.

Los cursores de Transact-SQL son absolutamente eficaces cuando se incluyen en procedimientos almacenados y desencadenadores. Esto se debe a que todo se compila en un plan de ejecución en el servidor y no hay tráfico de red asociado con la recuperación de filas.

Cursores de Transact-SQL y opciones SET

En SQL Server, se genera un error cuando se emite una instrucción FETCH en la que se registra un cambio de valores a partir del momento en que se abrió el cursor. Este error se produce en cualquiera de las siguientes opciones que afectan al plan o en las opciones requeridas por las vistas indizadas y las columnas calculadas. Para evitarlo, no cambie las opciones SET mientras el cursor esté abierto.

Opciones que afectan al plan

ARITHABORT

NUMERIC_ROUNDABORT

FORCEPLAN

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

ANSI_WARNINGS

ANSI_PADDING

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

DATEFIRST