WIXLIB

Unidad 5SQL Procedural5.1 Procedimientos y FuncionesAlmacenados.5.2 Disparadores (Triggers).Taller Bases de DatosISCIng. Felipe Alanís González -ITD-1

5.1 Procedimientos y FuncionesAlmacenados1. Se usará laMySQLBase deDatosllamadaLaConsentida.use LaConsentida2. Si es necesario, crear la tabla CtasBanc:create table CtasBanc(IdCuenta int auto increment primary key,tipo char(10),saldo numeric(10,2))Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-2

5.1 Procedimientos y FuncionesAlmacenados3. Añadir 2 tuplas:insert into CtasBancvalues (1, 'Chequera',10000)insert into CtasBancvalues (2, 'Inversión',0)4. Consulte el contenido de la tabla:select * from CtasBancTaller Bases de DatosISCIng. Felipe Alanís González -ITD-3

5.1 Procedimientos y FuncionesAlmacenados5. La tabla CtasBanc contiene la informaciónde las dos cuentas de la miscelánea “LaConsentida”, una de ellas es una chequeray otra es una inversión.6. Confrecuencia se requieren efectuartraspasos entre las cuentas, es decir, moverdinero de una cuenta hacia la otra.Escribiremos dos procedimientos paraefectuar esas tareasTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-4

5.1 Procedimientos y FuncionesAlmacenados7. Los conceptos de programación relativos alos procedimientos yaplicablesalosalmacenados.funciones sonprocedimientos8. Parapoder crear un procedimientoalmacenado en la Interfaz Workbench deMySQL o la Línea de Comandos, enprimer lugar hay que usar otro delimitador(en vez de punto y coma):delimiter //Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-5

5.1 Procedimientos y FuncionesAlmacenados“vImporte” es un parámetro dedelimiter //valor ( IN entrada ), la “v” laelegimos para indicar que es unavariable.Create procedureDepositoInversion(IN vImporte numeric(10,2))BEGINupdate CtasBanc set saldo saldo-vImportewhere IdCuenta 1;update CtasBanc set saldo saldo vImportewhere IdCuenta 2;Termina la instrucción de creación del procedimiento, si noEND;hubiéramos cambiado el delimitador, el primer “punto y//coma” se interpretaría como el final del create.Taller Bases de DatosISCIng. Felipe Alanís González -ITD-6

5.1 Procedimientos y FuncionesAlmacenadosdelimiter //Create procedureDepositoInversion(IN vImporte numeric(10,2))BEGINstart transaction;update CtasBanc set saldo saldo-vImportewhere IdCuenta 1;update CtasBanc set saldo saldo vImportewhere IdCuenta 2;commit;END; //Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-7

5.1 Procedimientos y FuncionesAlmacenadosdelimiter ;Instruye a MySQL para que elterminador sea de nuevo punto ycoma.select * from CtasBanc;call DepositoInversion(9000);select * from CtasBanc;Llamada a ejecucióndel procedimiento.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-8

5.1 Procedimientos y FuncionesAlmacenadosdelimiter //create procedureRetiroInversion(IN vImporte numeric(10,2))BEGINstart transactionupdate CtasBanc set saldo saldo-vImportewhere IdCuenta 2;update CtasBanc set saldo saldo vImportewhere IdCuenta 1;commit;END; //Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-9

5.1 Procedimientos y FuncionesAlmacenadosdelimiter ;select * from CtasBanc;call RetiroInversion(5000);select * from CtasBanc;call DepositoInversion(1000);select * from CtasBanc;Taller Bases de DatosISCIng. Felipe Alanís González -ITD-10

5.1 Procedimientos y FuncionesAlmacenadosSQL Server1. Se usará la Base de Datos llamadaLaConsentida.use LaConsentida2. En caso necesario crear la tabla CtasBanc:create table CtasBanc(IdCuenta int identity primary key,tipo char(10),saldo numeric(10,2))Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-11

5.1 Procedimientos y FuncionesAlmacenados3. Añadir 2 tuplas:insert into CtasBancvalues ('Chequera',10000)insert into CtasBancvalues ('Inversión',0)4. Consulte el contenido de la tabla:select * from CtasBancTaller Bases de DatosISCIng. Felipe Alanís González -ITD-12

5.1 Procedimientos y FuncionesAlmacenadoscreate procedureDepositoInversion @vImporte numeric(10,2) asBEGINbegin transactionupdate CtasBancset saldo saldo-@vImportewhere IdCuenta 1update CtasBancset saldo saldo @vImportewhere IdCuenta 2commitENDTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-13

5.1 Procedimientos y FuncionesAlmacenadosselect * from CtasBancexecute DepositoInversion 1000Llamada a ejecucióndel procedimiento.select * from CtasBancTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-14

5.1 Procedimientos y FuncionesAlmacenadoscreate procedureRetiroInversion @vImporte numeric(10,2) asBEGINbegin transactionupdate CtasBancset saldo saldo-@vImportewhere IdCuenta 2update CtasBancset saldo saldo @vImportewhere IdCuenta 1commitENDTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-15

5.1 Procedimientos y FuncionesAlmacenadosselect * from CtasBancexecute RetiroInversion 5000select * from CtasBancexecute DepositoInversion 1000select * from CtasBancTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-16

5.1 Procedimientos y FuncionesAlmacenadosMySQLdelimiter create function SaldoCuenta(vCuenta int)returns numeric(10,2)BEGINdeclare vSaldo numeric(10,2);select saldo from CtasBanc where IdCuenta vCuentainto vSaldo;return vSaldo;END delimiter ;Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-17

5.1 Procedimientos y FuncionesAlmacenadosselect * from CtasBanc;set @vSaldoChequera SaldoCuenta(1);select @vSaldoChequera;call DepositoInversion(3000);Llamada a ejecuciónde la función (tieneque incluirse en unselect o asignarse auna variableselect SaldoCuenta(1),SaldoCuenta(2);Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-18

5.1 Procedimientos y FuncionesAlmacenadosSQL SERVERcreate function SaldoCuenta(@vCuenta int)returns numeric(11,2)asBEGINdeclare @vSaldo numeric(11,2)select @vSaldo saldo from CtasBancwhere IdCuenta @vCuentareturn @vSaldoENDTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-19

5.1 Procedimientos y FuncionesAlmacenadosselect dbo.SaldoCuenta(1) as Chequeraselect dbo.SaldoCuenta(2) as Inversionupdate CtasBanc set saldo 1000where idCuenta 2update CtasBanc set saldo 9000where idCuenta 1dbo es el esquemapor default, SqlServer lo obliga enla sintáxisselect dbo.SaldoCuenta(1) as Chequeraselect dbo.SaldoCuenta(2) as InversionTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-20

5.1 Procedimientos y FuncionesAlmacenadosbegin transactionexecute DepositoInversion 1500if dbo.SaldoCuenta(1) 0rollbackelsecommitselect * from CtasBanc-- EJECUTE ESTA TRANSACCIÓN 7 o MÁS VECESTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-21

5.1 Procedimientos y FuncionesAlmacenadosbegin transactionexecute RetiroInversion 3500if dbo.SaldoCuenta(2) 0rollbackelsecommitselect * from CtasBanc-- EJECUTE ESTA TRANSACCIÓN 3 VECESTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-22

5.1 Procedimientos y FuncionesAlmacenadosEjercicio 1Escriba una aplicación en C# que haga uso roInversion y la función SaldoCuenta. La aplicaciónsolo debe llamar a ejecución los procedimientosalmacenados que vimos en clase pero hay que modificarlospara evitar se realicen traspaso con saldo insuficiente.La aplicación debe mostrar en un Grid los Saldos deambas cuentas antes de la operación, solicitar al usuario elimporte y el tipo de traspaso (depósito o retiro de lainversión) y finalmente, en otro Grid, mostrará los saldosdespués del traspaso.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-23

4.3 Niveles de Aislamiento-- Ejemplo con Parámetros Outputcreate procedureDepositoInversion @vImporte numeric(10,2),@vNuevoSaldo numeric(10,2) OUTPUT asBEGINbegin transactionupdate CtasBancset saldo saldo-@vImportewhere IdCuenta 1update CtasBancset saldo saldo @vImportewhere IdCuenta 2select @vNuevoSaldo saldo from CtasBancwhere IdCuenta 2commitENDTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-24

5.1 Procedimientos y FuncionesAlmacenadosselect * from CtasBancbegindeclare @vSaldoInversion numeric(10,2)execute DepositoInversion 1000,@vSaldoInversion OUTPUTselect @vSaldoInversionendselect * from CtasBancTaller Bases de Datos ISCIng. Felipe Alanís González -ITD-25

4.3 Niveles de AislamientoEjercicio 2 Escribaun procedimiento almacenado paraefectuar la transacción de los pagos a profesorespor el concepto de titulación (Unidad anterior). El procedimiento almacenado recibirá comoparámetros, Número de Control del alumno, RFCdel profesor, Nombre de la Opción de titulación yun parámetro de salida (OUTPUT) que contendráel nuevo total acumulado por el profesor (puedeusar este dato de la forma que desee). Haga los cambios a la aplicación para que solo sellame a ejecución el procedimiento almacenado, yde esa forma se simplifique el código C#.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-26

5.2 Disparadores (Triggers)Un trigger es un objeto de la B.D., que está asociadocon una tabla y que se activa cuando cierto eventoocurre.Los eventos que el DBMS monitorea para efectos delos triggers son: insert, update, delete.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-27

5.2 Disparadores (Triggers)Los triggers pueden configurarse para que seactiven antes o después del evento deseado, porejemplo:Un trigger que se active antes de que se borre una tupla. después de que se modifique una tupla.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-28

5.2 Disparadores (Triggers)MySQLCrear una Base de Datos llamada Unidad 5 y enella crear las siguientes tablas:create table Maestros (IdMaestro int auto increment primary key,Curp char(18) unique,Nombre varchar(40),Email varchar(40) unique)Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-29

5.2 Disparadores (Triggers)create tableInasistMaestros (IdInMaestro int auto increment primary key,IdMaestro int references Maestros,Fecha date,Hora time)Taller Bases de DatosISCIng. Felipe Alanís González -ITD-30

5.2 Disparadores (Triggers)create table Mensajes (IdMensaje int auto increment primary key,NombreDestinatario varchar(40),Email varchar(40),Texto text,FueEnviado boolean)Taller Bases de DatosISCIng. Felipe Alanís González -ITD-31

5.2 Disparadores (Triggers)En seguida se crea un trigger que se activarácada vez que se añada una tuplacorrespondiente a la Inasistencia de un profesoral aula.El trigger causará el insert de una tupla en latabla Mensajes, para que posteriormente seenvíe el mensaje de correo al profesorcomunicándole que deberá justificar lainasistencia o procederá un descuento en susalario.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-32

5.2 Disparadores (Triggers)create trigger tInasistMaestrosafter insert on InasistMaestros for each rowinsert into Mensajesselect 0,Nombre,Email,'Se le ruega justificar inasistencia o procederá descuento',0from Maestros where IdMaestro new.IdMaestro;Complemente elmensajeconcatenándolo conla fecha y hora de laInasistenciaTaller Bases de Datos ISCnew es una tabla temporal quecontiene la tupla que recién seinsertó en InasistMaestrosIng. Felipe Alanís González -ITD-33

5.2 Disparadores (Triggers)Para acceder a la tupla recién insertada enMySQL, se debe hacer referencia, como yase vio, a una tabla temporal llamada new.Cuando el trigger se activa a consecuenciade un update, las tablas temporales new yold contienen la tupla nueva y originalrespectivamente.Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-34

5.2 Disparadores (Triggers)Añada las siguientes tuplas:insert into Maestrosvalues ( 0, 'U1','Uno', 'uno@itd.edu.mx');insert into Maestrosvalues ( 0, 'D2','Dos', 'dos@itd.edu.mx');insert into Maestrosvalues ( 0, 'T3', 'Tres', 'tres@itd.edu.mx');Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-35

5.2 Disparadores (Triggers)Haga las siguientes consultas:select * from Maestros;select * from InasistMaestros;select * from Mensajes;Taller Bases de Datos ISCIng. Felipe Alanís González -ITD-36