Taller de Procedimientos Almacenados 3

CENTRO DE GESTION DE MERCADOS LOGISTICA Y TECNOLOGIAS DE LA INFORMACION ANALISIS Y DESARROLLO DE SISTEMAS DE INFORMACION

Fecha: Noviembre/2010

Taller de procedimientos almacenados 3

PROCEDIMIENTOS CON VARIABLES DE SALIDA

Para determinar una variable como parámetro de salida, agregue en la declaración de la variable la palabra clave OUTPUT, también debe declararla para la ejecución del procedimiento. Por ejemplo: En la base de datos Terminal, vamos a crear un procedimiento almacenado que muestre la placa del bus que mas viajes ha hecho use Terminal go create procedure DevPlaca @placa nchar nchar( (6) output /*declaramos la variable de salida con la plabra clave output*/ as set @placa = (select top 1 bu_placa from buses b inner join Viajes v on b.bu_placa= bu_placa =v.vi_placa group by bu_placa order by COUNT COUNT( (vi_placa) vi_placa )desc desc) ) go /*Hasta aqui queda la estructura para la creacion del procedimiento*/ /*Ahora vamos a ejecutar el procedimiento*/ declare @placa nchar nchar( (6) /*declaramos la variable de salida*/ execute DevPlaca @placa output /*ejecutamos el procedimiento y le pasamos la variable de salida junto con la palabra clave output*/ --select 'El bus que mas viajes ha hecho es el: ' as Mensaje, Mensaje , @placa as PlacaBus --print (@placa)+( @placa)+(': ': Es el el bus bus que que mas mas viajes viajes ha hecho hecho' ' ) /*creamos la linea que nos permitira ver el resultado, pero solo colocamos una de las dos formas: SELECT ó PRINT*/ /*Ahora ejecutamos las tres lineas de codigo anteriores y obtendremos el resultado de la sentencia*/

Elaborado por: Instructora Sandra Yanneth Rueda Guevara SENA - Área de Teleinformática Referencia de material de de apoyo diseñado por Ing Yaqueline Chavarro



CON LA BASE DE DATOS RENTACAR CONSTRUYA LOS SIGUIENTES PROCEDIMIENTOS:

procedimiento que permita permita consultar los vehículos vehículos disponibles disponibles en 1. Redactar un procedimiento una fecha en particular. use PrestacCar go alter procedure bdd1 @fecha datetime as select Ve_placa ,Co_estilo from dbo.Vehículo v inner join dbo.Contrato c on v.Ve_placa = c.Co_placa where @fecha not in (Co_fechaentrada ,Co_fechasalida) go use PrestacCar Execute bdd1 '2007-08-02'

2. Crear un procedimiento que reciba la identificación de un usuario y muestre los vehículos (placa, categoría, #días que alquilo el vehículo). create procedure HistorialUsuario (@identificacion varchar varchar( (15)) 15)) as select C.Co_placa Co_placa, , datediff datediff( (DAY DAY, ,C.Co_fechasalida Co_fechasalida, ,C.Co_fechaentrada Co_fechaentrada) )as DiasAlquiler, DiasAlquiler ,V.Ve_codicate as categoria from (Usuario U inner join Contrato C on U.Us_identusua = C.Co_idenusua Co_idenusua) ) inner join Vehículo V on V.Ve_placa = C.Co_placa where U.Us_identusua = @identificacion group by C.Co_placa Co_placa, ,Co_fechasalida Co_fechasalida, ,Co_fechaentrada Co_fechaentrada, ,V.Ve_codicate go exec HistorialUsuario '1023456879'

3. Crear un procedimiento para registrar los abonos a los contratos, debe recibir todos los datos requeridos y retornar el saldo. alter procedure Pago (@NumeCont int , @IdentUsua varchar( varchar (15), 15),@CodFormPago @CodFormPago int int, ,@valorPago money ) as




insert into Pagos (Pa_numecont Pa_numecont, ,Pa_identusua Pa_identusua, ,Pa_codiformpago Pa_codiformpago, ,Pa_valorpago Pa_valorpago, ,Pa_fechapago Pa_fechapago) ) Values (@NumeCont @NumeCont, ,@IdentUsua @IdentUsua, ,@CodFormPago @CodFormPago, ,@valorPago @valorPago, ,Getdate Getdate()) ()) declare @Pagado money declare @saldo money set @Pagado =( =(select select SUM SUM( (Pa_valorpago Pa_valorpago) )from Pagos where Pa_identusua= Pa_identusua =@IdentUsua and Pa_numecont Pa_numecont= =@NumeCont @NumeCont) ) set @saldo = (select Co_valorliquidación from Contrato where Co_numecont= Co_numecont =@NumeCont @NumeCont))-@Pagado @Pagado print 'saldo= '+ ' + str str( (@saldo @saldo) ) go declare @NumeCont int , @IdentUsua varchar varchar( (15), 15),@CodFormPago @CodFormPago int, int ,@valorPago money set @NumeCont = 10040 set @IdentUsua = 45768678 set @CodFormPago @CodFormPago= =1 set @valorPago @valorPago= =100000 exec Pago @NumeCont @NumeCont, , @IdentUsua @IdentUsua, , @CodFormPago @CodFormPago, ,@valorPago go

4. Cree los procedimientos necesarios para insertar datos en cada una de las demás tabla de la base de datos. create procedure Categorias Categorias( (@Co_codicate int int, , @Co_descate varchar varchar( (50), 50), @Co_valorkilo money money, , @Co_valordia money money, , @Co_valormes money money) ) as insert into dbo dbo. .Categoría (Co_descate Co_descate, ,Co_valorkilo Co_valorkilo, ,Co_valordia Co_valordia, ,Co_valormes Co_valormes) ) values (@Co_descate ,@Co_valorkilo ,@Co_valordia ,@Co_valormes ) go create procedure Contratos Contratos( (@Co_placa char char( (6), ),@Co_idenusua @Co_idenusua char(15), char( 15),@Co_fechasalida @Co_fechasalida datetime datetime, ,@Co_fechaentrada datetime, datetime ,@Co_kilometrajes float float, ,@Co_cantgasolinasaliente int, int ,@Co_cantgasolinaentrada int int, ,@Co_valorliquidación money money, ,@Co_estilo bit) bit ) as insert into dbo. dbo .Contrato Contrato( (Co_placa Co_placa, ,Co_idenusua Co_idenusua, ,Co_fechasalida Co_fechasalida, ,Co_fechaentrada Co_fechaentrada, ,Co_kilom etrajes, etrajes ,Co_cantgasolinasaliente ,Co_cantgasolinaentrada ,Co_valorliquidació n,Co_estilo Co_estilo) ) values (@Co_placa @Co_placa, ,@Co_idenusua @Co_idenusua, ,@Co_fechasalida @Co_fechasalida, ,@Co_fechaentrada ,@Co_kilometrajes ,@Co_cantgasolinasaliente ,@Co_cantgasolinaentrada ,@Co_valorliquidación ,@C




o_estilo ) o_estilo) go create procedure Formapagos (@Fp_desformpago nvarchar nvarchar( (30)) 30)) as insert into dbo dbo. .Formapago (Fp_desformpago Fp_desformpago) ) values (@Fp_desformpago @Fp_desformpago) ) go create procedure Tanque Tanque( (@Tn_coditanq char char( (1), ),@Tn_lleno @Tn_lleno money money, ,@Tn_3_4 money,@Tn_medio money money, money, ,@Tn_1_4 money money) ) as insert into dbo dbo. .Tanques Tanques( (Tn_coditanq Tn_coditanq, ,Tn_lleno Tn_lleno, ,Tn_3_4 Tn_3_4, ,Tn_medio Tn_medio, ,Tn_1_4 Tn_1_4) ) values (@Tn_coditanq @Tn_coditanq, ,@Tn_lleno @Tn_lleno, ,@Tn_3_4 @Tn_3_4, ,@Tn_medio @Tn_medio, ,@Tn_1_4 @Tn_1_4) ) go create procedure Usuarios (@Us_identusua char char( (15), 15),@Us_nombusua @Us_nombusua nvarchar (25), 25),@Us_apellusua @Us_apellusua nvarchar (25), 25),@Us_direusua @Us_direusua nvarchar nvarchar( (25), 25),@Us_teleusua @Us_teleusua nvarchar (25)) 25)) as insert into dbo. dbo .Usuario Usuario( (Us_identusua Us_identusua, ,Us_nombusua Us_nombusua, ,Us_apellusua Us_apellusua, ,Us_direusua Us_direusua, ,Us_teleusua ) values (@Us_identusua @Us_identusua, ,@Us_nombusua @Us_nombusua, ,@Us_apellusua @Us_apellusua, ,@Us_direusua @Us_direusua, ,@Us_teleusua @Us_teleusua) ) go create procedure Vehiculos (@Ve_placa char char( (6), ),@Ve_marca @Ve_marca nvarchar( nvarchar (20), 20),@Ve_modelo @Ve_modelo int int, ,@Ve_codicate char char( (1), ),@Ve_coditanq @Ve_coditanq char( char (1), ),@Ve_estado @Ve_estado bit bit) ) as insert into dbo dbo. .Vehículo (Ve_placa Ve_placa, ,Ve_marca Ve_marca, ,Ve_modelo Ve_modelo, ,Ve_codicate Ve_codicate, ,Ve_coditanq Ve_coditanq, ,Ve_estado Ve_estado) ) values (@Ve_placa @Ve_placa, ,@Ve_marca @Ve_marca, ,@Ve_modelo @Ve_modelo, ,@Ve_codicate @Ve_codicate, ,@Ve_coditanq @Ve_coditanq, ,@Ve_estado @Ve_estado) ) go

Realizar un procedim procedimient iento o almacen almacenado ado que permita permita mostrar mostrar el número número de 5. Realizar contrato, la identificación del usuario, el monto total pagado por el contrato, el saldo del contrato, y el porcentaje que el saldo representa frente al total que nos adeudan entre todos. Mostrar únicamente los que adeuden un porcentaje mayor al que el usuario ingrese como parámetro. create procedure saldosPnt5 (@porcentaje int int) ) as declare @PagadoTotal money declare @deudas money set @PagadoTotal = (select SUM SUM( (Pa_valorpago Pa_valorpago) )from Pagos Pagos) ) set @deudas = (select sum sum( (Co_valorliquidación ) from Contrato Contrato) ) select Co_numecont Co_numecont, ,Co_idenusua Co_idenusua,( ,(select select SUM SUM( (Pa_valorpago Pa_valorpago) )from Pagos




where Co_idenusua = Pa_identusua and Pa_numecont = Co_numecont) Co_numecont )as Pagado Pagado, , (Co_valorliquidación -( -(select select SUM SUM( (Pa_valorpago Pa_valorpago) )from Pagos where Co_idenusua = Pa_identusua and Pa_numecont = Co_numecont)) Co_numecont )) as saldo saldo, , ((( (((Co_valorliquidación Co_valorliquidación -( -(select select SUM( SUM (Pa_valorpago Pa_valorpago) )from Pagos where Co_idenusua = Pa_identusua and Pa_numecont = Co_numecont))* Co_numecont ))*100 100)/( )/(@deudas @deudas-@PagadoTotal @PagadoTotal)) )) as porcentaje from Contrato where ((( (((Co_valorliquidación Co_valorliquidación -( -(select select SUM SUM( (Pa_valorpago Pa_valorpago) )from Pagos where Co_idenusua = Pa_identusua and Pa_numecont = Co_numecont))* Co_numecont ))*100 100)/( )/(@deudas @deudas-@PagadoTotal @PagadoTotal))> ))>@porcentaje @porcentaje go exec saldosPnt5 20

6. Es probable que los clientes requieran alquilar por más tiempo un vehículo, así que usted tiene la misión de crear el procedimiento que permita cambiar la fech fecha a de entr entrad ada a regi regist stra rada da en el cont contra rato to y por por lo tant tanto; o; en el mism mismo o procedimiento, se deberá modificar el valor de la liquidación, este debe ser incrementado, de acuerdo con el valor actual dividido en el número de días que inicialmente se alquiló y este valor se multiplica por el número de días total de contrato.

El usuario debe ingresar el número del contrato y la nueva fecha de entrada; el procedimiento debe mostrar el número del contrato, fecha de salida, fecha de entrada y valor de la liquidación. Debe tener en cuenta que la fecha de entrada debe ser siempre posterior o igual a la fecha de salida, así que haga que estos datos se validen antes de llevar a cabo las modificaciones. ¿Usted cree que este procedimiento también permitiría acortar el tiempo de contrato, teniendo en cuenta que el valor del contrato se liquida por días de préstamo? Justifique su respuesta.


Taller de Procedimientos Almacenados 3

Recommend Documents