jueves, 3 de marzo de 2016

Procedimientos Almacenados Parte II - Definidos por el usuario

Buenos días queridos lectores, el día de hoy continuaremos con la segunda parte del post de Procedimientos Almacenados, en el post anterior vimos algunos ejemplos de Procedimientos Almacenados del Sistema, igual que en las funciones, nosotros podremos crear nuestros propios procedimientos. Así que comencemos


Procedimientos Almacenados definidos por el usuario

Son procedimientos que se implementan en forma personalizada según la necesidad del usuario, aquí podrá emplear cualquier instrucción vista hasta el momento

Ejemplo 1:
Procedimiento almacenado que permite devolver los cinco costos más altos registrados en la tabla RESERVA.

IF OBJECT_ID('TOP5RESERVAS') IS NOT NULL
BEGIN
       DROP PROCEDURE TOP5RESERVAS
END
GO

CREATE PROCEDURE TOP5RESERVAS
AS
       SET ROWCOUNT 5
       SELECT * FROM RESERVA ORDER BY RESERVA.COSTO DESC
GO

En el script se implementa el procedimiento almacenado TOP5RESERVAS el cual tendrá como misión mostras los 5 costos más altos registrados en la tabla RESERVA, hay que tener en cuenta que no se debe usar la clausula TOP para dicho proceso.

Se asigna el número de registros 5 con el siguiente script SET ROWCOUNT 5 y luego se implementa la sentencia SELECT que permite mostrar los registros ordenados por la columna COSTO y limitados por la asignación del ROWCOUNT en 5

Para la ejecución del procedimiento almacenado colocar el siguiente script:

EXEC TOP5RESERVAS

La siguiente imagen muestra el resultado de la ejecución del procedimiento almacenado:


Ejemplo 2:
Implementar un procedimiento almacenado que permita mostrar los registros de la tabla AEROPUERTO donde se visualice el nombre del país, defina de manera adecuada la cabecera del listado.



IF OBJECT_ID('MUESTRAAEROPUERTO') IS NOT NULL
BEGIN
       DROP PROCEDURE MUESTRAAEROPUERTO
END
GO

CREATE PROCEDURE MUESTRAAEROPUERTO
AS
       SELECT AER.IDAERO AS [CODIGO],
                    AER.NOMBRE AS [AEROPUERTO],
                    PAI.PAIS AS [PAIS]
                    FROM AEROPUERTO AER
                    INNER JOIN PAIS PAI ON AER.IDPAIS = PAI.IDPAIS
GO

En el script se implementa el procedimiento almacenado que tiene por misión mostrar las columnas de la tabla AEROPUERTO y ademas mostrar el nombre del país desde la tabla PAIS, esto no pasa de ser una consulta simple dentro de un procedimiento almacenado, pero la idea principal es resumir la forma de ejecución de dicha consulta.

El script para ejecutar el procedimiento almacenado es:

EXEC MUESTRAAEROPUERTO

Como pueden ver queridos lectores esa consulta se resume en la linea de EXEC, de manera que una consulta recurrente, pueden meterla en un procedimiento almacenado y ahorrarse muchas líneas de código.

Otra forma de ejecutar el mismo procedimiento es colocando directamente el nombre del procedimiento sin necesidad de colocar la instrucción EXEC así como se muestra en el siguiente script:

MUESTRAAEROPUERTO
GO

El resultado del procedimiento almacenado se muestra en la siguiente imagen:


Ejercicio 3:
Implemente un procedimiento almacenado que permita mostrar los registros de la tabla PASAJERO donde se visualice el nombre del país por medio de la función MUESTRAPAIS, defina de manera adecuada la cabecera del listado

IF OBJECT_ID('MUESTRAPAIS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.MUESTRAPAIS
END
GO

CREATE FUNCTION MUSTRAPAIS(@ID CHAR(4))
RETURNS VARCHAR(30)
AS
BEGIN
       RETURN(SELECT PA.PAIS
                    FROM PAIS PA
                    WHERE PA.IDPAIS=@ID)
END
GO

IF OBJECT_ID('MUESTRAPASAJERO')IS NOT NULL
BEGIN
       DROP PROCEDURE MUESTRAPASAJERO
END
GO

CREATE PROCEDURE MUESTRAPASAJERO
AS
       SELECT PAS.IDPASAJERO AS [CODIGO],
                    PAS.NOMBRES AS [PASAJERO],
                    DBO.MUSTRAPAIS(PAS.IDPAIS) AS [PAIS],
                    PAS.EMAIL
                    FROM PASAJERO PAS
GO


El procedimiento almacenado MUESTRAPASAJERO, adiciona además de sus columnas originales, la invocación de la función en el script anterior; claro esta que siempre se debe especificar su proveedor al momento de crearlo (DBO) y también su parámetro que en este caso es el IDPAIS obtenido desde la tabla PASAJERO, como notará, la función devolverá un valor que se comportará como columna al momento de mostrar el resultado.

Para colocar el procedimiento, debe colocar el siguiente script:

EXEC MUESTRAPASAJERO
GO

La siguiente imagen muestra el resultado de la ejecución:


Ejercicio 4:
Implemente un procedimiento almacenado que muestre el listado de los países y su total de pasajeros.

--1.
IF OBJECT_ID('PASAJEROSXPAIS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.PASAJEROSXPAIS
END
GO

--2.
CREATE PROCEDURE PASAJEROSXPAIS
AS
       SELECT PAI.PAIS AS[PAIS] ,COUNT(*) AS [TOTAL]
             FROM PASAJERO PAS
             JOIN PAIS PAI ON PAS.IDPAIS=PAI.IDPAIS
             GROUP BY PAI.PAIS
GO

En el punto dos se implementa el procedimiento PASAJEROXPAIS en donde se usa la función agregada COUNT que permite el conteo de los pasajeros según la agrupación especificada en la clausula GROUP BY PAI.PAIS

Para visualizar el resultado deberá ejecutar el siguiente script:

EXEC PASAJEROSXPAIS
GO

La imagen siguiente muestra el resultado de la ejecución del procedimiento:


Ejercicio 5:
Implemente un procedimiento almacenado que permite mostrar el monto acumulado por años desde la tabla PAGO



IF OBJECT_ID('MONTOXAÑO') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.MONTOXPAIS
END
GO

--2.
CREATE PROCEDURE MONTOXAÑO
AS
       SELECT YEAR(PAG.FECHA) AS [AÑO],
             SUM(PAG.MONTO) AS [MONTO ACUMULADO]
             FROM PAGO PAG
             GROUP BY YEAR(PAG.FECHA)  
GO

En el punto dos se implementa el procedimiento almacenado MONTOXAÑO en la cual se usa la función SUM(PAG.MONTO) para acumular los montos de la tabla PAGO según la agrupación YEAR(PAG.FECHA).

Para visualizar el resultado deberá ejecutar el siguiente script:

EXEC MONTOXAÑO
GO

La siguiente imagen muestra el resultado de la ejecución:




Ejercicio 6:
Implementar un Procedimiento Almacenado que permita mostrar los países que aún no tienen pasajero registrado

--1.
IF OBJECT_ID('PAISNOREGISTRADO') IS NOT NULL
BEGIN
       DROP PROCEDURE DBO.NUMPASAJEROSXPAIS
END
GO

CREATE PROCEDURE PAISNOREGISTRADO
AS
       SELECT PAI.IDPAIS,PAI.PAIS
             FROM PAIS PAI
             LEFT JOIN PASAJERO PAS ON PAI.IDPAIS=PAS.IDPAIS
             WHERE PAS.IDPAIS IS NULL
GO    


En el punto dos se implementa el procedimiento almacenado PAISNOREGISTRADO en la cual se usa la clausula LEFT JOIN para poder mostrar aquellos países que no tienen pasajeros a través de a condición WHERE PAS.IDPAIS IS NULL

Para visualizar el resultado deberá ejecutar el siguiente script:

EXEC PAISNOREGISTRADO
GO

La siguiente imagen muestra el resultado de la ejecución:


Hasta aquí la segunda parte de los procedimientos almacenados creados por el usuario, hemos visto hasta el momento los procedimientos si parámetros, en el siguiente post, veremos la creación de procedimientos con parámetros de entrada y salida. 

Espero que haya sido de su utilidad y agrado, espero también compartan, comenten, den +1 o por lo menos clic en algún anuncio que les interese.

Les dejo el link de la primera parte, por si no la leyeron:



Banderas de Visitantes

Flag Counter