miércoles, 17 de febrero de 2016

Cursores en SQL SERVER Parte I

Buenas tardes queridos lectores, espero se encuentren bien en este lunes 15 de febrero, el día de hoy les hablaré de los cursores en SQL, es una herramienta bastante útil y saber utilizarlos es básico, pues bien, comencemos


Cursores

Un cursor es una herramienta que nos permite recorrer una consulta y realizar operaciones en cada registro de la misma. Hay algunas consideraciones para el uso de los cursores
Consideraciones
  • Los cursores permiten situarse en filas específicas del conjunto de resultados.
  • Recuperan una fila o un bloque de filas de la posición actual en el conjunto de resultados.
  • Aceptan modificaciones de los datos en la posición actual del conjunto de resultados.
  • Aceptan diferentes grados de visibilidad para los cambios que realizan otros usuarios en la información de la base de datos que representa el conjunto de resultados
Proceso de implementación de un cursor

Para implementar un cursor se necesitan pasos específicos, siempre será recomendado implementarlo dentro de un procedimiento (lo veremos en post futuros). Veamos los pasos necesarios para la creación de un cursor:
  1. Declarar el cursos y asignar un conjunto de resultados provenientes de la instrucción SELECT.
  2. Aperturar el cursor para que se active; use el operador OPEN.
  3. Recuperar las filas del cursor que desea visualizar y usar la estructura repetitiva WHILE para dicho trabajo.
  4. Tenga en cuenta que se puede realizar operaciones de actualización de la fila en la posición actual del cursor.
  5. Cerrar el cursor usando el operador CLOSE.
  6. Liberar el cursor usando el operador DELLOCATE.
Pero como siempre, se aprende mejor haciendo ejercicios, veamos un ejemplo

Ejercicio 1
Script que permita implementar un cursor básico donde se imprima el primer registro de la tabla PASAJERO

DECLARE MICURSORBAS CURSOR
       FOR SELECT * FROM PASAJERO
OPEN MICURSORBAS
FETCH NEXT FROM MICURSORBAS
CLOSE MICURSORBAS
DEALLOCATE MICURSORBAS
GO

En el script se declara a variable MICURSORBAS que tiene como filas los registros de la tabla PASAJERO, toda declaración se realiza con la instrucción DECLARE. Luego se tiene que aperturar el cursor con el operador OPEN, seguidamente se obtiene el primer registro desde el cursor usando el operador FETCH, finalmente se cierra el cursor con CLOSE y se libera con el operador DEALLOCATE. Este script podrá ejecutarlo las veces que quiera pero todo en bloque para que no ocasine algún error, en caso que no se cierre el cursor, al volverlo a ejecutar marcará un error, debido a que el cursos y se encuentra abierto y no se puede volver a abrir. Veamos el resultado del script


Si no cerraramos el cursor, provocaría el siguiente error:


Ejercicio 2
Script que permita implementar un cursor donde imprima en forma de reporte los registros de la tabla PASAJERO. Debe adicionar el nombre del país en la consulta

--1.
DECLARE @IDPA CHAR(5), @NOM CHAR(30), @PAI CHAR(30), @TEL CHAR(15), @EMA VARCHAR(40)
--2.
DECLARE MICURSOR CURSOR
       FOR SELECT PAS.IDPASAJERO, PAS.NOMBRES, PAI.PAIS, PAS.TELEFONO, PAS.EMAIL
             FROM PASAJERO PAS
             INNER JOIN PAIS PAI ON PAS.IDPAIS = PAI.IDPAIS
--3
OPEN MICURSOR
--4
FETCH MICURSOR INTO @IDPA, @NOM, @PAI, @TEL, @EMA
PRINT 'CODIGO                     PASAJERO                          PAIS                                                  TELEFONO                          EMAIL'
PRINT '--------------------------------------------------------------------------------------------------------------------'          
--5
WHILE @@FETCH_STATUS=0
BEGIN
       PRINT @IDPA+SPACE(5)+@NOM+SPACE(5)+@PAI+SPACE(5)+@TEL+SPACE(5)+@EMA
       FETCH  FROM MICURSOR INTO @IDPA, @NOM, @PAI, @TEL, @EMA
END
--6
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

En el punto uno se declaran las variables locales que representaran el valor de las columnas de la consulta, como notará se ha declarado como CHAR a columnas que en la tabla son VARCHAR, esto es para que mantenga una estética al mostrar el informe ya que al ser dinámico el espacio con VARCHAR descuadra el informe cuando el texto de la columna es largo.

En el punto dos se declara el cursor con la consulta a la tabla PASAJERO y PAIS ya que se necesita obtener el nombre del país para obtenerlo en la consulta.

En el punto tres se apertura el cursor

En el punto cuatro se obtiene el primer registro asignado al valor de las columnas en las variables @IDPA, @NOM, @PAI, @TEL, @EMA, hay que tener en cuenta que las variables debes estar en el orden establecido por la consulta en el punto dos. Seguidamente se imprime la cabecera con la función PRINT con el siguiente script

PRINT 'CODIGO                     PASAJERO                          PAIS                                                  TELEFONO                          EMAIL'
PRINT '--------------------------------------------------------------------------------------------------------------------'        

En el punto cinco se implementa la estructura repetitiva WHILE para poder imprimir todos los registros asignados al cursor, para poder determinar los ciclos de repeticiones se tiene que evaluar la función @@FETH_STATUS se ejecutara el ciclo mientras @@FETCH_STATUS=0, veamos los posibles valores para @@FETCH_STATUS


Valor
Descripción
0
Al evaluar FETCH no se encontró error alguno
-1
La instrucción FETCH no se ejecutó correctamente o la fila estaba más allá del conjunto de resultados
-2
Falta la fila recuperada
Según lo mostrado en la tabla WHILE recorrerá mientras que la función @@FETCH_STATUS mantenga el valor cero, dentro de este ciclo se imprime el reporte con la función PRINT, aquí se especificarán las variables que participarán en el reporte, observe que se utiliza la función SPACE(5) para poder espaciar una columna de otra en el reporte final.

En el punto seis se cierra el cursor con el operador CLOSE y se libera recurso con DEALLOCATE. La imagen siguiente muestra el resultado del cursor:


Si además del reporte mostrado, quisiéramos mostrar el total de pasajeros al final del reporte se tendrá que modificar el cursor como sigue:

--1.
DECLARE @IDPA CHAR(5), @NOM CHAR(30), @PAI CHAR(30), @TEL CHAR(15), @EMA VARCHAR(40), @TOTAL INT = 0
--2.
DECLARE MICURSOR CURSOR
       FOR SELECT PAS.IDPASAJERO, PAS.NOMBRES, PAI.PAIS, PAS.TELEFONO, PAS.EMAIL
             FROM PASAJERO PAS
             INNER JOIN PAIS PAI ON PAS.IDPAIS = PAI.IDPAIS
--3
OPEN MICURSOR
--4
FETCH MICURSOR INTO @IDPA, @NOM, @PAI, @TEL, @EMA
PRINT 'CODIGO                     PASAJERO                  PAIS                     TELEFONO                          EMAIL'
PRINT '--------------------------------------------------------------------------------------------------------------------'         
--5
WHILE @@FETCH_STATUS=0
BEGIN
             SET @TOTAL+=1
             PRINT @IDPA+SPACE(5)+@NOM+SPACE(5)+@PAI+SPACE(5)+@TEL+SPACE(5)+@EMA
             FETCH  FROM MICURSOR INTO @IDPA, @NOM, @PAI, @TEL, @EMA
END
--6
PRINT 'EL TOTAL DE PASAJEROS ES: >>' + CAST(@TOTAL AS CHAR(6))
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

En el punto uno se adiciona la declaración de la variable @TOTAL inicializada en cero para almacenar el conteo de los pasajeros de la siguiente forma @TOTAL INT=0

El el punto cinco se adiciona el contador de la variable @TOTAL aumentando en uno por cada ciclo de la estructura WHILE con SET @TOTAL+=1

El el punto seis se adiciona la función PRINT que tiene pos misión mostrar el total de pasajeros obtenidos por el conteo.

La imagen muestra el reporte de los pasajeros además del total de pasajeros al final de la línea de reporte.


Este capitulo es demasiado extenso, ya que es un tema importante e interesante, así que lo dividiré en dos partes, espero que esta digamos "Introducción" les haya servido amigos y haya sido de su agrado, les agradezco que comenten, compartan, den +1 o algún clic en algún tema que les interese

Cursores en SQL Parte II

Banderas de Visitantes

Flag Counter