jueves, 18 de febrero de 2016

Cursores en SQL SERVER Parte II

Buenos días queridos lectores, el día de hoy les hablaré sobre la segunda parte del post de CURSORES, como habíamos dicho con anterioridad, un Cursor es una herramienta que nos permite recorrer una consulta y realizar operaciones en cada registro de la misma. En el post anterior ya habíamos visto algunos ejemplos, pues continuaremos por la misma línea viendo aún mas ejemplos, pues comencemos:


Ejercicio 3
Script que permita implementar un cursor que reporte el código, nombre de pasajero y su email de solo aquellos servidores ingresados por el usuario en una variable local.


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

GO

En el script se adiciono la variable @SERVIDOR inicializada con HOTMAIL para almacenar los pasajeros de dicho correo.

En e punto dos se especifican solo tres columnas de la tabla PASAJERO condicionado por la columna EMAIL para solo almacenar dentro del cursor a los pasajeros cuyo email sea el valor de la variable @SERVIDOR en este caso mostrará los pasajeros que tienen como servidor de correo a Hotmail. 

En la siguiente imagen se muestra el reporte ejecutado por el cursor:


Si se cambia el valor de la variable @SERVIDOR por GMAIL el resultado sería:

Ejercicio 4
Script que permita implementar el cursor donde imprima el siguiente reporte:


Observe que se deben quebrar los registros por año de reserva y por cada bloque emitir un total de pasajeros ese año. Al final imprimir el total acumulado. Usaremos las siguientes tablas:



--1.
DECLARE @NOM CHAR(30), @AÑO INT, @TOTAL INT, @CAÑO INT=0, @AAÑO INT=0, @ACTUAL INT
--2.
DECLARE MICURSOR CURSOR
       FOR SELECT PAS.NOMBRES, YEAR(RES.FECHA), COUNT(*)
             FROM RESERVA RES
             INNER JOIN PAGO PAG ON RES.IDRESERVA = PAG.IDRESERVA
             INNER JOIN PASAJERO PAS ON PAS.IDPASAJERO= PAG.IDPASAJERO
             GROUP BY PAS.NOMBRES, YEAR(RES.FECHA)
--3.
OPEN MICURSOR
--4.
FETCH MICURSOR INTO @NOM, @AÑO,@TOTAL
SET @ACTUAL=@AÑO
PRINT 'PASAJERO                                AÑO                        TOTAL'
PRINT '---------------------------------------------------'
PRINT 'AÑO: ' + CAST(@AÑO AS CHAR(4))
--5.
WHILE @@FETCH_STATUS=0
BEGIN
       SET @AAÑO+=@TOTAL
       IF @AÑO=@ACTUAL
             BEGIN
                    PRINT @NOM+SPACE(5)+CAST(@AÑO AS CHAR (4))+ SPACE(5)+CAST(@TOTAL AS VARCHAR(30))
                    SET @CAÑO+=@TOTAL
             END
       ELSE
       BEGIN
             PRINT 'EL TOTAL DE PASAJEROS DEL AÑO ' + CAST(@ACTUAL AS CHAR(4))+ ' ES ' + CAST (@CAÑO AS CHAR(4))
             PRINT ' '
             PRINT 'AÑO:' + CAST(@AÑO AS CHAR(4))
             SET @ACTUAL=@AÑO
             SET @CAÑO=@TOTAL
       END
       FETCH MICURSOR INTO @NOM, @AÑO, @TOTAL
END
--6.
PRINT 'EL TOTAL DE PASAJEROS DEL AÑO ' + CAST(@AÑO AS CHAR(4))+ ' ES ' + CAST(@CAÑO AS VARCHAR(6))
PRINT 'EL TOTAL ACUMULADO ES ' +CAST(@AAÑO AS CHAR(6))
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

En el punto uno se declaran las variables locales @NOM, @AÑO, @TOTAL, que permitirán almacenar el nombre del pasajero, el año de su reserva y el total de reservas de dicho cliente respectivamente, también se declaran @CAÑO INT=0 para contabilizar las reservas por año, @AAÑO INT=0 para acumular el total de todos los años y @ACTUAL que permitirá permutar entre los años encontrados en la tabla RESERVA. Recuerde que todo contador o acumulador siempre deberá inicializar en cero.

En el punto dos se declara el cursor MICURSOR y se almacena la consulta.

En el punto cuatro se captura el primer registro del cursor con FETCH MICURSOR INTO @NOM, @AÑO, @TOTAL, hasta aquí el cursos ya tiene registrados los valores en las variables @NOM,@AÑO,@TOTAL, por lo tanto ahora se puede capturar el año y enviarlo a la variable @ACTUAL con SET @ACTUAL = @AÑO seguidamente de la función CAST a dicha variable.

En el punto cinco se implementa la estructura repetitiva WHILE para poder controlar los ciclos de acuerdo a la cantidad de filas que tiene el cursor. Luego se acumula el valor de la columna total de la siguiente forma SET @AAño+=@TOTAL esta variable tendrá el acumulado de los totales que en este caso será 13 que representa la suma de todos los totales del cursor, este valor no debe estar condicionado. Luego se condiciona la variable @AÑO comparando si se parece al año establecido en @ACTUAL ya que si cumple la condición tendrá que imprimir las filas coincidentes y así generar el primer bloque de filas correspondientes al primer año encontrado e imprimirlas, ademas de acumular los totales de ese año con SET @CAÑO+=@TOTAL.

En caso la comparación del @AÑO con lo asignado en @ACTUAL no sean iguales, querrá decir que las filas están mostrando un año distinto y antes de actualizar las variables para el siguiente año se tiene que imprimir el resumen de este primer año con el siguiente script.

 PRINT 'EL TOTAL DE PASAJEROS DEL AÑO ' + CAST(@ACTUAL AS CHAR(4))+ ' ES ' + CAST (@CAÑO AS CHAR(4))
             PRINT ' '
             PRINT 'AÑO:' + CAST(@AÑO AS CHAR(4))

Luego se actualizan los valores de las variables involucradas en @ACTUAL y @CAÑO ya que son nuevos valores frente a las filas anteriores mostradas, todo esto se realiza con 
SET @ACTUAL=@AÑO
SET@CAÑO=@TOTAL, el primero cambia el año actual por el nuevo año encontrado desde la condicional y el segundo actualiza el primer valor encontrado en la fila nueva, es decir el primer @TOTAL.

Finalmente saliendo de la condicional se debe obtener el siguiente registro, observe que este script no tiene condición ya que cumpla o no siempre se debe avanzar a la siguiente fila del cursor FETCH MICURSOR INTO @NOM, @AÑO, @TOTAL.

En el punto seis se imprime el reporte del último año encontrado además del total acumulado en el ciclo de repeticiones además de cerrar el cursor y liberarlo. Todo esto se implementa con el siguiente script:

PRINT 'EL TOTAL DE PASAJEROS DEL AÑO ' + CAST(@AÑO AS CHAR(4))+ ' ES ' + CAST(@CAÑO AS VARCHAR(6))
PRINT 'EL TOTAL ACUMULADO ES ' +CAST(@AAÑO AS CHAR(6))
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

Ejercicio 5
Script que permita implementar un cursor que imprima el siguiente reporte:









--1.
DECLARE @IDPAS CHAR(5), @NOM CHAR(30), @PAIS CHAR(30),@EMAI CHAR(30), @SERVIDOR CHAR(15), @SERV CHAR(15),
             @CSERV INT=0, @CTOTAL INT=0
--2.
DECLARE MICURSOR CURSOR
       FOR SELECT PAS.IDPASAJERO, PAS.NOMBRES, PAI.PAIS, PAS.EMAIL,
                           CASE WHEN PAS.EMAIL LIKE '%HOTMAIL%' THEN 'HOTMAIL'
                                  WHEN PAS.EMAIL LIKE '%GMAILMAIL%' THEN 'GMAIL'
                                  ELSE 'OTRO'
                                  END AS [SERVIDOR DE CORREO]
             FROM PASAJERO PAS
             INNER JOIN PAIS PAI ON PAS.IDPAIS= PAI.IDPAIS
             ORDER BY [SERVIDOR DE CORREO]
--3.
OPEN MICURSOR
--4.
FETCH MICURSOR INTO @IDPAS, @NOM,@PAIS, @EMAI, @SERVIDOR
SET @SERV=@SERVIDOR
PRINT 'CODIGO PASAJERO                                PAIS                                           EMAIL'
PRINT '----------------------------------------------------------------------------------'
PRINT 'SERVIDOR:' + @SERV
--5.

WHILE @@FETCH_STATUS=0
BEGIN
       SET @CTOTAL+=1
       IF @SERVIDOR=@SERV
       BEGIN
             PRINT @IDPAS+SPACE(5)+@NOM+SPACE(5)+@PAIS+@EMAI
             SET @CSERV+=1
       END
       ELSE
       BEGIN
             PRINT 'EL TOTAL DE PASAJEROS CON SERVIDOR' + @SERV+ ' ES ' + CAST(@CSERV AS CHAR(4))
             PRINT ' '
             PRINT 'SERVIDOR:'+@SERVIDOR
             SET @SERV=@SERVIDOR
             SET @CTOTAL = @CSERV
             SET @CSERV=0
       END
       FETCH MICURSOR INTO @IDPAS, @NOM, @PAIS,@EMAI,@SERVIDOR
END
--6.
PRINT 'EL TOTAL DE PASAJEROS CON SERVIDOR ' +@SERV+ ' ES ' + CAST(@CSERV AS CHAR(4))
PRINT 'EL TOTAL DE PASAJEROS ES: ' + CAST(@CTOTAL AS CHAR(4))
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

En el punto uno se declaran las variables que representaran a las columnas de la consulta. Además de las variables que permitirán almacenar el servidor actual (@SERV), el contador de dicho servidor(@CSERV) inicializado en cero por ser un contador y el contador total (@CTOTAL) inicializada también en cero.

DECLARE MICURSOR CURSOR
       FOR SELECT PAS.IDPASAJERO, PAS.NOMBRES, PAI.PAIS, PAS.EMAIL,
                           CASE WHEN PAS.EMAIL LIKE '%HOTMAIL%' THEN 'HOTMAIL'
                                  WHEN PAS.EMAIL LIKE '%GMAILMAIL%' THEN 'GMAIL'
                                  ELSE 'OTRO'
                                  END AS [SERVIDOR DE CORREO]

En el punto dos se declara el cursor MICURSOR, dentro del cursor se almacena la consulta en la cual se debe adicionar la columna servidor a la consulta, esto se realiza con la estrucuta CASE donde separa HOTMAIL de GMAIL y OTROS para que los pasajeros se filtren por esa columna, recuerde que el cursos captura las nuevas columnas de la consulta, no exclusivamente columnas de la tabla. Hay que tener en cuenta que dicha columna debe ordenarse ya que de otra forma emitiría un reporte con duplicidades. Observe el ORDER BY que hace referencia al nombre de la cabecera definida en el CASE

DECLARE MICURSOR CURSOR
       FOR SELECT PAS.IDPASAJERO, PAS.NOMBRES, PAI.PAIS, PAS.EMAIL,
                           CASE WHEN PAS.EMAIL LIKE '%HOTMAIL%' THEN 'HOTMAIL'
                                  WHEN PAS.EMAIL LIKE '%GMAILMAIL%' THEN 'GMAIL'
                                  ELSE 'OTRO'
                                  END AS [SERVIDOR DE CORREO]
             FROM PASAJERO PAS
             INNER JOIN PAIS PAI ON PAS.IDPAIS= PAI.IDPAIS
             ORDER BY [SERVIDOR DE CORREO]

En el punto cuatro se obtiene el primer registro del cursor almacenándolo en las variables previamente declaradas. se actualiza el servidor actual con el servidor obtenido del cursor. Y luego se imprimirá la cabecera del reporte.

FETCH MICURSOR INTO @IDPAS, @NOM,@PAIS, @EMAI, @SERVIDOR
SET @SERV=@SERVIDOR
PRINT 'CODIGO PASAJERO                                PAIS                                           EMAIL'
PRINT '----------------------------------------------------------------------------------'
PRINT 'SERVIDOR:' + @SERV

En el punto cinco se entra el ciclo repetitivo para obtener los demás registros del cursor, en primer lugar deberá comenzar a contar los registros

WHILE @@FETCH_STATUS=0
BEGIN
       SET @CTOTAL+=1

Luego se condicionará el tipo de servidor encontrado por cada registro del cursor, si estos se parecen, se imprimirán en el informe del primer bloque de coincidencias además de contabilizar sus propios registros.


IF @SERVIDOR=@SERV
       BEGIN
             PRINT @IDPAS+SPACE(5)+@NOM+SPACE(5)+@PAIS+@EMAI
             SET @CSERV+=1
       END

En caso contrario que el servidor no sea el mismo que el encontrado en el cursor, entonces imprimirá los totales encontrados en dicho servidor además de actualizar las variables para el siguiente bloque de registros a imprimir, no se olvide que el FETCH debe ubicarse fuera de la condicional ya que la obtención de registros del cursos no esta condicionado a nada


       ELSE
       BEGIN
             PRINT 'EL TOTAL DE PASAJEROS CON SERVIDOR' + @SERV+ ' ES ' + CAST(@CSERV AS CHAR(4))
             PRINT ' '
             PRINT 'SERVIDOR:'+@SERVIDOR
             SET @SERV=@SERVIDOR
             SET @CTOTAL = @CSERV
             SET @CSERV=0
       END
       FETCH MICURSOR INTO @IDPAS, @NOM, @PAIS,@EMAI,@SERVIDOR
END

En el punto seis se imprimen los valores finales del reporte, en el cual esta involucrado el total de pasajeros con el último servidor. No olvidarse que tiene que cerrar y liberar el cursor.

PRINT 'EL TOTAL DE PASAJEROS CON SERVIDOR ' +@SERV+ ' ES ' + CAST(@CSERV AS CHAR(4))
PRINT 'EL TOTAL DE PASAJEROS ES: ' + CAST(@CTOTAL AS CHAR(4))
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

Ejercicio 6
Script que permita implementar un cursor donde imprima el primer, quinto y último registro de la tabla PASAJERO. Use first, Absolute y Last para el Informe

--1.
DECLARE @N INT=5
DECLARE MICURSOR CURSOR SCROLL
       FOR SELECT * FROM PASAJERO
--2.
OPEN MICURSOR
--3.
FETCH FIRST FROM MICURSOR
FETCH ABSOLUTE @N FROM MICURSOR
FETCH LAST FROM MICURSOR
--4
CLOSE MICURSOR
DEALLOCATE MICURSOR
GO

En el punto uno se declara una variable @N que tiene por misión ingresar un número de registro a mostrar desde el cursor y además de la declaración del cursor utilizando el operador SCROLL que permitirá desplazarse por los registros almacenados en el cursor.

DECLARE @N INT=5
DECLARE MICURSOR CURSOR SCROLL
       FOR SELECT * FROM PASAJERO

En el punto dos solo se apertura el cursor con OPEN MICURSOR.

En el punto tres se invoca al primer registro con el operador FIRST desde el cursor, ahi mismo sobre el valor de la vaiable @N usando ABSOLUTE y el final del los registros con el operador LAST

FETCH FIRST FROM MICURSOR
FETCH ABSOLUTE @N FROM MICURSOR
FETCH LAST FROM MICURSOR

En el punto cuatro se cierra y se libera el cursor

CLOSE MICURSOR
DEALLOCATE MICURSOR

Finalmente la siguiente imagen muestra el resultado de la ejecución del cursor, como notará se parece mucho a tres consultas SELECT ejecutadas en simultáneo:


Hasta aquí el post queridos lectores, espero haya sido de su utilidad y agrado, espero compartan, comenten, den +1 o por lo menos algún clic en algún anuncio de su interés.

En la próxima entrega hablaremos de las funciones en SQL. Hasta la próxima y saludos

Banderas de Visitantes

Flag Counter