lunes, 7 de marzo de 2016

Procedimientos Almacenados en SQL SERVER Parte III- Procedimientos con Parámetros

Buenos días queridos lectores, vamos a continuar con el tema de los procedimientos almacenados en SQL SERVER, ya vimos los procedimientos almacenados que trae el sistema, vimos también que nosotros podemos crear nuestros propios procedimientos, ahora seguiremos viendo como crear nuestros propios procedimientos, pero con parámetros de entrada y salida, como siempre, pasaremos a los ejemplos que es donde se aprende mejor




Procedimientos almacenados con parámetros de entrada

Ejercicio 1:
Implemente un procedimiento almacenado que permita mostrar las reservas de un determinado pasajero, para este caso deberá considerar como parámetro de entrada, el nombre del pasajero, defina de manera adecuada a cabecera del listado e implemente un mensaje de error en caso de que el pasajero no exista.



IF OBJECT_ID('RESERVASXPASAJERO') IS NOT NULL
BEGIN
       DROP PROCEDURE DBO.RESERVASXPASAJERO
END
GO

CREATE PROCEDURE RESERVASXPASAJERO(@PASAJERO VARCHAR(30))
AS
BEGIN
       IF EXISTS(SELECT IDPASAJERO
                                               FROM PASAJERO
                                               WHERE NOMBRES=@PASAJERO)
              SELECT RES.*
                    FROM RESERVA RES
                    JOIN PAGO PAG ON RES.IDRESERVA=PAG.IDRESERVA
                    WHERE PAG.IDPASAJERO=(SELECT IDPASAJERO
                                                                   FROM PASAJERO
                                                                   WHERE NOMBRES=@PASAJERO)
             ELSE
             PRINT 'EL PASAJERO NO ESTA REGISTRADO EN LA BASE'
END   

GO

En el punto dos se impementa el procedimiento almacenado RESERVASXPASAJERO, el cual tiene como parámetro de entrada a @PASAJERO en el cual se enviara al procedimiento un nombre de pasajero. Luego se condiciona la existencia del pasajero dentro de la tabla del mismo nombre ya que si éste no existe se mostrará el mensaje EL PASAJERO NO ESTA REGISTRADO EN LA BASE, en caso contrario, se buscarán las reservas realizadas por dicho pasajero, para esto debe unir las tablas RESERVA y PAGO ya que para determinar las reservas por pasajeros se tiene que pasar por la tabla PAGO.

Para ejecutar el procedimiento almacenado con parámetros de entrada, se debe colocar el siguiente script:

EXEC RESERVASXPASAJERO 'FERNANDA TORRES LAZARO'

O también de la siguiente manera:

EXEC RESERVASXPASAJERO @PASAJERO='FERNANDA TORRES LAZARO'

La imagen muestra la ejecución del procedimiento almacenado


Ejercicio 2:
Implemente un procedimiento almacenado que permita registrar a un nuevo pasajero, para este caso deberá definir como parámetros de entrada todos los campos referentes al pasajero a excepción del IDPAIS; aquí deberá ingresar el nombre del país, en caso de que no exista emitir un mensaje de “PAIS NO REGISTRADO EN LA BASE”. Finalmente si el pasajero ser registra correctamente emitir el mensaje “PASAJERO REGISTRADO CON ÉXITO”.



Ejercicio 2:
Implemente un procedimiento almacenado que permita registrar a un nuevo pasajero, para este caso deberá definir como parámetros de entrada todos los campos referentes al pasajero a excepción del IDPAIS; aquí deberá ingresar el nombre del país, en caso de que no exista emitir un mensaje de “PAIS NO REGISTRADO EN LA BASE”. Finalmente si el pasajero ser registra correctamente emitir el mensaje “PASAJERO REGISTRADO CON ÉXITO”.

--2.
CREATE PROCEDURE NUEVOPASAJERO(
             @IDPAS CHAR(5),@NOM VARCHAR(40),@PAI VARCHAR(40),
             @TEL VARCHAR(15),@EMA VARCHAR(40)
             )
AS
       BEGIN TRY
       IF EXISTS(SELECT IDPAIS FROM PAIS WHERE PAIS=@PAI)
       BEGIN
             DECLARE @IDPAI CHAR(4)
             SELECT @IDPAI = IDPAIS FROM PAIS WHERE PAIS=@PAI
             INSERT INTO PASAJERO
                    VALUES (@IDPAS,@NOM,@IDPAI,@TEL,@EMA)
             PRINT 'PASAJERO REGISTRADO CON EXITO'
       END
       ELSE
             PRINT 'PAIS NO REGISTRADO EN LA BASE'
       END TRY
       BEGIN CATCH
             PRINT 'OCURRIO UN ERROR AL INSERTAR'
       END CATCH     
GO

En el punto dos se implementa el procedimiento almacenado NUEVOPASAJERO que permita registrar un nuevo pasajero en la tabla PASAJERO, pero debemos tener en cuenta que dicha tabla tiene 5 columnas; por lo tanto, la cantidad de parametros del procedimiento es la misma cantidad de columnas de la tabla, solo hay que tener en cuenta que el país se debe de ingresar por el nombre del mismo.

CREATE PROCEDURE NUEVOPASAJERO(
             @IDPAS CHAR(5),@NOM VARCHAR(40),@PAI VARCHAR(40),
             @TEL VARCHAR(15),@EMA VARCHAR(40)
             )
AS

Luego se implementó el BEGIN TRY para controlar los errores, esto se activará cuando ocurra un error al insertar el nuevo pasajero, podría suceder cuando el Primary Key se duplique, el país ingresado desde el parámetro @PAI también tiene que evaluarse ya que se debería enviar un mensaje si dicho país no existe en la tabla del mismo nombre.

 BEGIN TRY
       IF EXISTS(SELECT IDPAIS FROM PAIS WHERE PAIS=@PAI)
       BEGIN

Seguidamente se declara la variable @IDPAI que capturará el IDPAIS desde la consulta

DECLARE @IDPAI CHAR(4)
             SELECT @IDPAI = IDPAIS FROM PAIS WHERE PAIS=@PAI


Luego se aplica la inserción a la tabla PASAJERO pasando por la comprobación de la existencia del país en su tabla y haber capturado el IDPAIS, ya que en el procedimiento no se ingresó el código sino el nombre del país.

INSERT INTO PASAJERO
                    VALUES (@IDPAS,@NOM,@IDPAI,@TEL,@EMA)
             PRINT 'PASAJERO REGISTRADO CON EXITO'
       END
       ELSE
             PRINT 'PAIS NO REGISTRADO EN LA BASE'

Finalmente se tiene que ejecutar el procedimiento almacenado para lo cual tenemos dos ejemplos. El primero es;

EXEC NUEVOPASAJERO 'P0011','JANETH CRUZ','HONDURAS','999999999','JCRUZ@HOTMAIL.COM'
GO  

Y el segundo es:

EXEC NUEVOPASAJERO 'P0012','JANETH CRUZ','HAITI','999999999','JCRUZ@HOTMAIL.COM'
GO 

Este es el resultado si ejecutamos la primera instrucción:


Si ocurriera un error, como por ejemplo insertarlo otra vez y obtener duplicidad en el código P0012, aparecerá el siguiente error:


Si ejecutamos el segundo, el país no existe en la base de datos y nos aparecerá el siguiente error:


Ejercicio 3:
Implemente un procedimiento que permita registrar un nuevo país para lo cual deberá definir como parámetro de entrada al nombre del país, aquí se deberá comprobar que dicho país no haya sido registrado antes, si fuera el caso, emitir un mensaje de “PAIS YA REGISTRADO”, el código de este país es autogenerado; por lo tanto, no se ingresará como parámetro. Finalmente, si todo es correcto emitir un mensaje de “PAIS REGISTRADO CORRECTAMENTE”

IF OBJECT_ID('NUEVOPAIS') IS NOT NULL
BEGIN
       DROP PROCEDURE DBO.NUEVOPAIS
END
GO

CREATE PROCEDURE NUEVOPAIS(@PAIS VARCHAR(40))
AS
       --1.
       SET ROWCOUNT 1
       DECLARE @IDPAI CHAR(4),@NUEVOID CHAR(4)
       SELECT @IDPAI=IDPAIS FROM PAIS ORDER BY PAIS.IDPAIS DESC
       IF LEN(@IDPAI+1)=1 SET @NUEVOID='000'+CAST(@IDPAI+1 AS CHAR(4))   
       IF LEN(@IDPAI+1)=2 SET @NUEVOID='00'+CAST(@IDPAI+1 AS CHAR(4))    
       IF LEN(@IDPAI+1)=3 SET @NUEVOID='0'+CAST(@IDPAI+1 AS CHAR(4))
       --2.

       IF NOT EXISTS(SELECT PAIS FROM PAIS WHERE PAIS=@PAIS)
       BEGIN
             INSERT INTO PAIS VALUES (@NUEVOID,@PAIS)
             PRINT 'PAIS REGISTRADO CORRECTAMENTE'  
       END
       ELSE
             PRINT 'EL PAIS YA SE ENCUENTRA REGISTRADO'
GO

En el punto uno se implementa el script que permita autogenerar el código del nuevo país a registrar en la tabla, hay que tener en cuenta que el parámetro del procedimiento almacenado solo solicita el nombre del país a través del parámetro @PAIS por lo que la idea del script es autogenerar el código.

Lo primero que debemos entender es que necesitamos un script que permita capturar el ultimo código registrado en la tabla y adicionarle un número para generar el nuevo código; por ejemplo, si la tabla devuelve P0012, el script deberá generar el nuevo código P0013.

SET ROWCOUNT 1 permite especificar que solo se obtendrá una fila del conjunto resultante. Esto adicionado al siguiente script:

DECLARE @IDPAI CHAR(4),@NUEVOID CHAR(4)
       SELECT @IDPAI=IDPAIS FROM PAIS ORDER BY PAIS.IDPAIS DESC

Permite capturar el IDPAIS desde el ingreso del nombre del país, recordemos que no se ingresó el IDPAIS sólo el nombre del nuevo país a registrar por tal motivo se autogenera el código obtenido del script anterior, entonces el nuevo código se obtendrá cuando se asigna una de las acciones especificadas en la siguiente condicional:

IF LEN(@IDPAI+1)=SET @NUEVOID='000'+CAST(@IDPAI+AS CHAR(4))   
       IF LEN(@IDPAI+1)=SET @NUEVOID='00'+CAST(@IDPAI+AS CHAR(4))    
       IF LEN(@IDPAI+1)=SET @NUEVOID='0'+CAST(@IDPAI+AS CHAR(4))

En el punto dos se verifica la existencia del país ingresado desde el parámetro del procedimiento; todo esto realizado a la tabla PAIS. Una vez verificado que dicho país no este registrado y obtenido el nuevo ID del país nuevo se procederá a registrar dicho país.

 IF NOT EXISTS(SELECT PAIS FROM PAIS WHERE PAIS=@PAIS)
       BEGIN
             INSERT INTO PAIS VALUES (@NUEVOID,@PAIS)
             PRINT 'PAIS REGISTRADO CORRECTAMENTE'  
       END

Para probar si se inserta correctamente el país en su tabla se deberá ejecutar el siguiente script:

EXEC NUEVOPAIS 'HAITI'


Si lo ejecutamos otra vez, nos marcará error, ya que el país HAITI ya existe


Para comprobar que el país se agrego correctamente, ejecutamos el SELECT

SELECT * FROM PAIS

Y observamos que el país se encuentra correctamente registrado


Ejercicio 4:
Procedimiento que permita actualizar los datos de un determinado PASAJERO para lo cual deberá definir como parámetros de entrada los campos de la tabla, se debe tener en cuenta que al actualizar no debe enviar el código del país sino su nombre del país y validar la existencia del mismo con un mensaje de “País no registrado en la base”. Finalmente si todo es correcto enviar un mensaje de “PASAJERO ACTUALIZADO CON EXITO”



IF OBJECT_ID('ACTUALIZAPASAJERO') IS NOT NULL
BEGIN
       DROP PROCEDURE DBO.ACTUALIZAPASAJERO
END
GO

--2. 
CREATE PROCEDURE ACTUALIZAPASAJERO(
             @IDPAS CHAR(5),@NOM VARCHAR(40),@PAI VARCHAR(40),
             @TEL VARCHAR(15),@EMA VARCHAR(40)
             )
AS
       BEGIN TRY
       IF EXISTS(SELECT IDPAIS FROM PAIS WHERE PAIS=@PAI)
       BEGIN
             DECLARE @IDPAI CHAR(4)
             SELECT @IDPAI = IDPAIS FROM PAIS WHERE PAIS=@PAI
             UPDATE PASAJERO
                    SET NOMBRES=@NOM,
                           IDPAIS=@IDPAI,
                           TELEFONO=@TEL,
                           EMAIL=@EMA
                    WHERE  IDPASAJERO=@IDPAS
             PRINT 'PASAJERO ACTUALIZADO CON EXITO'
       END
       ELSE
             PRINT 'PAIS NO REGISTRADO EN LA BASE'
       END TRY
       BEGIN CATCH
             PRINT 'OCURRIO UN ERROR AL ACTUALIZAR'
       END CATCH     
GO

EXEC ACTUALIZAPASAJERO 'P0011','JANETH CRUZ','HAITI','999999999','JCRUZ@HOTMAIL.COM'
GO

En el punto dos se implementa el procedimiento almacenado ACTUALIZAPASAJERO que tendrá por misión actualizar todos los valores registrados en la tabla PASAJERO según el código del pasajero.

Primero, se verifica la existencia del país ingresado como parámetro en el procedimiento, luego se le asigna el IDPAIS a la variable @IDPAI para que se pueda registrar en la tabla PASAJERO.

BEGIN TRY
       IF EXISTS(SELECT IDPAIS FROM PAIS WHERE PAIS=@PAI)
       BEGIN
             DECLARE @IDPAI CHAR(4)
             SELECT @IDPAI = IDPAIS FROM PAIS WHERE PAIS=@PAI
             UPDATE PASAJERO
                    SET NOMBRES=@NOM,
                           IDPAIS=@IDPAI,
                           TELEFONO=@TEL,
                           EMAIL=@EMA
                    WHERE  IDPASAJERO=@IDPAS
             PRINT 'PASAJERO ACTUALIZADO CON EXITO'
       END
       ELSE
             PRINT 'PAIS NO REGISTRADO EN LA BASE'
       END TRY

Finalmente se muestra el mensaje siempre y cuando se encuentre un error en la actualización este mensaje será visualizado solo si se implementó BEGIN TRY y BEGIN CATCH respectivo.

Para ejecutar el procedimiento almacenado, se debe colocar el siguiente script

EXEC ACTUALIZAPASAJERO 'P0011','JANETH CRUZ','HAITI','999999999','JCRUZ@HOTMAIL.COM'
GO



Procedimientos almacenados con parámetros de entrada y salida


Ejercicio 5
Procedimiento almacenado que permita determinar el número de reservas y el monto acumulado de las reservas realizadas en un determinado país, este deberá ingresarse por su nombre. Use parámetros de salida.



IF OBJECT_ID('TPAGOSXPAIS') IS NOT NULL
BEGIN
       DROP PROCEDURE DBO.TPAGOSXPAIS
END
GO

CREATE PROCEDURE TPAGOSXPAIS
       @PAIS VARCHAR(40),
       @TOTAL INT OUTPUT,
       @ACUMULADO MONEY OUTPUT
AS
       SELECT @TOTAL= COUNT(*),
             @ACUMULADO=SUM(MONTO)
             FROM PAIS PAI
             JOIN PASAJERO PAS ON PAI.IDPAIS=PAS.IDPAIS
             JOIN PAGO PAG ON PAG.IDPASAJERO=PAS.IDPASAJERO
             WHERE PAI.PAIS=@PAIS
GO

Para ejecutar el procedimiento almacenado, deberá ejecutar el siguiente script:

DECLARE @T INT ,@M MONEY
DECLARE @PAIS VARCHAR(40)='PERU'
EXEC TPAGOSXPAIS @PAIS,@TOTAL=@T OUTPUT ,@ACUMULADO=@M OUTPUT
PRINT  'EL PAIS '+@PAIS+' TIENE '+CAST(@T AS VARCHAR(10))+' RESERVAS '+
             'Y ACUMULA UN MONTO DE: '+CAST(@M AS VARCHAR(10))

GO


Los parámetros de un procedimiento almacenado, pueden ser de entrada o salida sin límites de declaraciones, pero de acuerdo a la cantidad de parámetros, ya sea de entrada o salida, es el que se tiene que enviar cuando se intenta ejecutar el procedimiento, ahora en este caso en particular, se declara a @T y @M para recibir los valores devueltos desde el procedimiento y @PAIS tendrá asignado el nombre del país que será enviado al procedimiento y será tomado como parámetro de entrada.

En caso de que se omita algún parámetro de salida, el motor de base de datos emitirá el siguiente mensaje de error:



Si todo es correcto y se envían los parámetros necesarios del procedimiento, entonces se mostrará los resultados esperados como en la imagen siguiente:

EL PAIS PERU TIENE 5 RESERVAS Y ACUMULA UN MONTO DE: 6200.00

Ejemplo 6:
Procedimiento almacenado que muestre todos los procedimientos almacenados registrados en la base de datos AGENCIA

SELECT * FROM SYS.sysobjects P
       WHERE P.xtype='P'


La imagen siguiente muestra el resultado de la consulta:


Eliminar procedimientos almacenados

Ya vimos como crear procedimientos almacenados, sin parámetros, y con parámetros, ahora veremos como eliminarlos, solo hay que tener en cuenta, que una vez eliminados, no se podrán recuperar

Ejemplo 7:
Implemente un script que permita eliminar el procedimiento almacenado usando el criterio de la existencia del mismo. Para este caso tomaremos en referencia el procedimiento almacenado en TPAGOSXPAIS
IF OBJECT_ID('TPAGOSXPAIS') IS NOT NULL
BEGIN
       DROP PROCEDURE DBO.TPAGOSXPAIS
       PRINT 'PROCEDIMIENTO ELIMINADO CORRECTAMENTE'
END
ELSE
       PRINT 'PROCEDIMIENTO NO EXISTE'
GO

Visualizar la implementación de un procedimiento almacenado

En muchas ocasiones nos tocará procedimientos almacenados que ya se encuentran ejecutados, en la base de datos pero no se visualiz si implementación, para poder modificarlos, se tiene que obtener la fuente del procedimiento


Desde aquí se podrá visualizar su contenido y arrastarlo al editor de consultas y alterar su contenido.

Procedimientos almacenados y cursores

Cuando se hablo del tema de cursores, resultaba un poco incomodo ejecutarlos, ya que se tenía que seleccionar todo el bloque que encierra al cursor o ejecutarlo por bloques, los procedimientos almacenados reducen el tiempo de ejecución llamando al cursor por medio del nombre asignado al procedimiento almacenado que lo aloje.

Ejemplo 9:
Implemente un procedimiento almacenado que permita reportar los registros de la tabla PASAJERO por medio de un CURSOR, considere que solo se mostrarán los pasajeros de un determinado PAIS ingresado por el usuario. Debe adicionar el nombre del país en la consulta


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

--2.
CREATE PROCEDURE REPORTE_PASAJEROS (@PAIS VARCHAR(30))
AS
BEGIN
       DECLARE @IDPA CHAR(5),@NOM CHAR(30),@PAI CHAR(30),
                 @TEL CHAR(15),@EMA VARCHAR(40),@TOTAL INT=0

       DECLARE MICURSOR CURSOR
       FOR SELECT PAS.IDPASAJERO,PAS.NOMBRES,PAI.PAIS,PAS.TELEFONO,PAS.EMAIL
                    FROM PASAJERO PAS
                    JOIN PAIS PAI ON PAS.IDPAIS=PAI.IDPAIS
                    WHERE PAI.PAIS=@PAIS
      
       OPEN MICURSOR

       FETCH MICURSOR INTO @IDPA,@NOM,@PAI,@TEL,@EMA
       PRINT 'CODIGO    PASAJERO                     PAIS        TELEFONO         EMAIL'
       PRINT '------------------------------------------------------------------------'

       WHILE @@FETCH_STATUS=0
       BEGIN
             SET @TOTAL+=1
             PRINT @IDPA+SPACE(5)+@NOM+SPACE(5)+RTRIM(@PAI)+SPACE(5)+RTRIM(@TEL)+SPACE(5)+@EMA
             FETCH MICURSOR INTO @IDPA,@NOM,@PAI,@TEL,@EMA
       END

       PRINT 'EL TOTAL DE PASAJEROS ES: >> '+CAST(@TOTAL AS CHAR(6))
       CLOSE MICURSOR
       DEALLOCATE MICURSOR
END
GO

En el punto uno se verifica la existencia del procedimiento, en el punto dos se implementa el procedimiento almacenado  dentro de este se implementa el cursor. Les dejo el tema de CURSORES

Para probar el procedimiento almacenado deberá ejecutar el siguiente script

EXEC REPORTE_PASAJEROS 'PERU'
GO

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


Hasta aquí el tema de los procedimientos almacenados queridos lectores, como pudieron apreciar es un tema bastante grande, pero que es básico entender su uso y aplicación. En nuestro próximo post, les hablaré de otro tema sumamente interesante que son las transacciones, no se lo pierdan.

Espero comenten, compartan o le den clic a algún anuncio que les interese. Muchas gracias









Banderas de Visitantes

Flag Counter