- Procedimientos Almacenados Parte I
- Procedimientos Almacenados Parte II. Definidos por el Usuario
- Procedimientos Almacenados Parte III. Con parámetros
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)=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))
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:
La imagen siguiente muestra el resultado de la consulta:
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'
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
No hay comentarios.:
Publicar un comentario