CURSOR en Transact SQL

Un cursor es una estructura de datos que permite recorrer el conjunto de filas devueltas por una SELECT y procesar cada registro uno a uno.

Un cursor consta de diferentes pasos que son recogidos en la siguiente sintaxis:

 -- Declaración de variables
 DECLARE variables
 -- Declaración nombre del cursor
 DECLARE nombreCursor CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
 -- Declaración SELECT a iterar
 FOR SELECT [ FOR UPDATE [ OF <Nombre_Columna>[ ,…n ] ] ] [;]
 -- Apertura del cursor
 OPEN nombreCursor
 -- Iteración de registros e inserción en variables
 FETCH NEXT FROM nombreCursor
 -- Cierre del cursor
 CLOSE nombreCursor
 --desasignación del cursor
 DEALLOCATE nombreCursor

Atendiendo a los diferentes argumentos que puede recibir un cursor podemos categorizarlos según:

• Alcance de cursores.
• Opciones de recuperación de datos.
• Tipos de cursores.
• Tipos de restricciones.

Alcance de cursores

• LOCAL: El alance está limitado únicamente al procedimiento almacenado, trigger o lote que lo ha definido.

• GLOBAL: El alcance es a nivel de conexión, pudiendo utilizarse en cualquier procedimiento almacenado, trigger o lote.

Opciones de recuperación de datos

• FORWARD_ONLY: especifica que el cursor solo puede desplazarse hacia delante de la primera a la última fila. Solo permite como opción de recuperación de datos: FETCH NEXT para leer un registro y pasar al siguiente

• SCROLL: proporciona las siguientes opciones de recuperación de datos:

  1. FIRST
  2. LAST
  3. PRIOR
  4. NEXT
  5. RELATIVE
  6. ABSOLUTE

Si se omite el argumento SCROLL, el cursor sería FORWARD_ONLY, permitiendo unicamente la opción NEXT

Tipos de cursores

• STATIC: permiten desplazarse hacia adelante y hacia atrás. Estos cursores nunca reflejan los cambios en las filas, mostrando siempre el conjunto de resultados igual al momento de apertura del cursor.

• DYNAMIC: A diferencia de los estáticos, los cursores dinámicos, reflejarán todas las modificaciones realizadas en las filas del conjunto de resultados.

• FAST_FORWARD: cursor FORWARD_ONLY, READ_ONLY con optimizaciones de rendimiento.

• KEYSET: cursor a medio camino entre un cursor estático y dinámico. Este tipo de cursores no siempre detectarán cambios en la pertenencia y orden del conjunto de resultados, pero sí en los valores de las filas del mismo.

Tipos de bloqueo

• READ ONLY: Solo lectura, impidiendo que se realicen actualizaciones a través del cursor. Esta opción elimina la capacidad predeterminada de actualizar el cursor.

• SCROLL_LOCKS: bloquea las filas a medida que se van leyendo en el cursor para asegurar la integridad de los datos y que los UPDATES Y DELETES tengan éxito

• OPTIMISTIC: no bloquea las filas a medida que se van leyendo en el cursor, impidiendo que UPDATES Y DELETES tengan éxito si se realiza alguna actualización fuera del cursor.

Ejemplo

El siguiente ejemplo de cursor, tiene el objetivo de leer los registros correspondientes a la relación entre la tabla Clientes y Pedidos. Utilizaremos su creación para explicar brevemente los pasos imprescindibles necesarios.

--Declaración de variables
DECLARE @nombre VARCHAR (20),
        @apellidos VARCHAR (50),
        @producto VARCHAR (50),
        @precio FLOAT

-- Declaración nombre del cursor y SELECT a iterar
DECLARE cPedidosClientes CURSOR FOR
SELECT c.nombre,
       c.apellidos,
       p.producto,
       p.precio
FROM Clientes c INNER JOIN Pedidos p
ON c.idClientes = p.idClientes

-- Apertura del cursor
OPEN cPedidosClientes

-- Iteración de registros e inserción en variables
FETCH NEXT FROM cPedidosClientes
INTO  @nombre,
      @apellidos,
      @producto,
      @precio
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @nombre,
           @apellidos,
           @producto,
           @precio
    FETCH NEXT FROM cPedidosClientes
    INTO  @nombre,
          @apellidos,
          @producto,
          @precio
END
-- Cierre del cursor
CLOSE cPedidosClientes;
--desasignación del cursor
DEALLOCATE cPedidosClientes;

El resultado devuelto, seleccionando y ejecutando todas sus instrucciones es el siguiente:

Pasemos a continuación, a realizar una breve explicación de cada paso de nuestro cursor: cPedidosClientes

PASO 1: Declaración de variables

--Declaración de variables
DECLARE @nombre VARCHAR (20),
        @apellidos VARCHAR (50),
        @producto VARCHAR (50),
        @precio FLOAT

El primer paso consiste en declarar las variables necesarias para rellenar con los datos devueltos por el cursor. Recalcar, que el tipo de dato de la variable tiene que ser compatible al tipo de dato del campo cuyo valor quiere almacenar.

PASO 2: Declaración nombre del cursor y SELECT

-- Declaración nombre del cursor y SELECT a iterar
DECLARE cPedidosClientes CURSOR FOR
SELECT c.nombre,
       c.apellidos,
       p.producto,
       p.precio
FROM Clientes c INNER JOIN Pedidos p
ON c.idClientes = p.idPedidos

El siguiente paso consiste en declarar el nombre del cursor y la SELECT que ejecutará y sobre la que iterará posteriormente para recorrer cada registro del conjunto de resultados. De manera opcional, este paso está sujeto a la utilización de diversas opciones y argumentos que definirán el cursor tal como hemos visto al principio de esta sección.

PASO 3: Apertura del cursor

-- Apertura del cursor
OPEN cPedidosClientes

PASO 4: Iteración de registros e inserción en variables

-- Iteración de registros e inserción en variables
FETCH NEXT FROM cPedidosClientes
INTO  @nombre,
      @apellidos,
      @producto,
      @precio
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @nombre,
           @apellidos,
           @producto,
           @precio
    FETCH NEXT FROM cPedidosClientes
    INTO  @nombre,
          @apellidos,
          @producto,
          @precio
END

El siguiente paso consiste en la iteración de los registros obtenidos en la consulta y la inserción de los datos devueltos en las variables declaradas. El funcionamiento del bucle es el siguiente:

• FETCH es un comando que coloca el cursor en una posición. Una vez leído y procesado el primer registro NEXT sitúa el cursor en la siguiente fila para procesarla.

• Una vez se procesa el primer registro, si todo va bien se rellenarán las variables con los valores devueltos por la consulta mientras la función FETCH_STATUS devolverá un 0, indicando que la fila se ha procesado correctamente.

• Mientras FETCH_STATUS devuelva 0, se irá leyendo y procesando cada registro en bucle hasta iterar la última fila del conjunto de resultados.

PASO 5: Cierre del cursor

-- Cierre del cursor
CLOSE cPedidosClientes;

Una vez procesada la última fila, es necesario salir del cursor cerrándolo. De está manera, no podrá recorrerse nuevamente el conjunto de resultados hasta su nueva reapertura.

PASO 6: Desasignación del cursor

--desasignación del cursor
DEALLOCATE cPedidosClientes;

El último paso consistirá en la eliminación de la definición del cursor y la liberación de todos los recursos del sistema asociados al mismo, eliminándolo de la memoria.