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
No hay comentarios.:
Publicar un comentario