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 Parte I
• Procedimientos Almacenados Parte II. Definidos por el Usuario
• Procedimientos Almacenados Parte III. Con parámetros

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:

Procedimientos Almacenados en SQL Parte I