RUTINAS/TAREAS ALMACENADAS (continuacin)

9.- CURSORES. Normalmente los procedimientos implican la manipulacin de BD pudindose utilizar cualquier instruccin SQL, es decir, pertenecientes al DDL, DML o DCL. Ejemplo DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S simple*sql$$ C#E+"E &#%CED'#E simple*sql, BE.!N DECL+#E i !N"/ SE" i 0 1/ D#%& "+BLE !( E)!S"S testta2le/ C#E+"E "+BLE testta2le , id !N" &#!M+#3 4E3, dato 5+#C6+#,78- -/ 96!LE i :0 18 D% !NSE#" !N"% testta2le 5+L'ES ,i, C%NC+" ,;re<istro ;, i- - / SE" i 0 i = 1/ END 96!LE/ SE" i 0 >/ '&D+"E testta2le SE" dato 0 C%NC+" ,;actualizado ;, i- 96E#E id 0 i/ DELE"E (#%M testta2le 96E#E id ? i/ END$$ ?C+LL simple*sql, -$$ SELEC" @ (#%M testta2le$$ &odemos usar la sentencia SELEC" para enAiar Aalores a Aaria2les ,usando !N"%-. Ejemplo DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S simple*sql1$$ C#E+"E &#%CED'#E simple*sql1,jo2 5+#C6+#,18-BE.!N DECL+#E emple !N",B-/ DECL+#E apell 5+#C6+#,78-/ SELEC" emp*no, apellido !N"% emple, apell (#%M empleados 96E#E oCicio 0 jo2/ D@ procesamos los datos o2tenidos, por ejemplo mostrEndolos @D SELEC" emple, apell, jo2/ END$$ ?C+LL simple*sql1,F&#ES!DEN"EG-$$ Si queremos recuperar mEs de una Cila para manipular sus datos, la sentencia anterior no sirAe H necesitamos usar los cursores. Conceptualmente los cursores se asocian a un conjunto de Cilas o una consulta so2re la BD. Nos permite recuperar los datos de una consulta select en un procedimiento almacenado de Corma similar a como se recuperan los datos de un arcIiAo con un len<uaje de pro<ramacin procedural. JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 1DJ8

?C+LL simple*sql1,F5ENDED%#G-$$

da ;E##%# 11MJ NN..O

Los cursores se utilizan para procesar indiAidualmente cada una de las Cilas que Iemos o2tenido como resultado de una consulta SELEC". En las Aersiones de MHSQL superior a >.P los cursores tienen las si<uientes propiedades L Solo lectura Los Aalores en el cursor no se pueden actualizar L No scrolla2le Solo se puede recorrer en una direccin H no se pueden dar saltos ni moAer Iacia delante H Iacia atrEs en el conjunto de Cilas. L !nsensi2le Se de2e eAitar Iacer actualizaciones en la ta2la asociada mientras el cursor estE a2ierto Ha que se pueden o2tener resultados inesperados. MHSQL Cacilita las si<uientes sentencias para tra2ajar con cursores Lo primero que IaH que Iacer es declarar el cursor usando la sentencia DECL+#E. DECLARE cursor_na ! CURSOR "OR SELECT_stat! !nt# Despus, se a2rirE el cursor con la sentencia %&EN. 6aH que a2rir el cursor para poder tra2ajar con las Cilas almacenadas en el cursor. O$EN cursor_na !# + continuacin, para ir recuperando Cilas <uardadas en el cursor e ir aAanzando a traAs de las diCerentes Cilas se usa la sentencia (E"C6. "ETC% cursor_na ! INTO &aria'(! (ist# &or Qltimo, IaH que cerrar el cursor para desactiAarlo H li2erar la memoria asi<nada al cursor. &ara cerrar el cursor se usa la sentencia CL%SE CLOSE cursor_na !# +l<unas Cunciones Qtiles para manejar los cursores ,tam2in disparadores H eAentos- son (%'ND*#%9S, limit. Ejemplo SELEC" @ (#%M departamentos/ SELEC" (%'ND*#%9S, -/ #%9*C%'N", deAuelAe el nQmero de Cilas aCectadas por la Qltima sentencia insert, update o delete, es decir, deAuelAe el nQmero de Cilas insertadas, actualizadas o 2orradas de una ta2la. Si deAuelAe el Aalor L1, indica que la Qltima sentencia Cue una select. Ejemplo SELEC" #%9*C%'N", -/ !NSE#" !N"% departamentos 5+L'ES ,M8, FM+#4E"!N.G, FSE5!LL+G-/ SELEC" #%9*C%'N", -/ 5eremos un ejemplo de un cursor que muestre todos los cdi<os H su descripcin de la ta2la productos ,usamos la Cuncin (%'ND*#%9S, - que nos dice el nQmero de Cilas seleccionadas por la Qltima instruccin selectJK +S!# L +dministracin de Sistemas .estores de Bases de Datos JDJ8 deAuelAe el nQmero de Cilas mostradas en la Qltima sentencia select sin la claQsula

DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S mis*productos$$ C#E+"E &#%CED'#E mis*productos, BE.!N DECL+#E codi<o !N",J-/ DECL+#E descri 5+#C6+#,>8-/ DECL+#E num*Cilas !N"/ DECL+#E cur*prod C'#S%# (%# SELEC" producto*no, descripcion (#%M productos/ D@ +2rimos el cursor H <uardamos su nQmero de Cilas @D %&EN cur*prod/ SELEC" (%'ND*#%9S, - !N"% num*Cilas/ SELEC" num*Cilas/ !( num*Cilas 0 8 "6EN SELEC" ;La ta2la de productos estE AacRa ;/ END !(/ 96!LE ,num*Cilas :? 8- D% (E"C6 cur*prod !N"% codi<o, descri/ SELEC" codi<o, descri/ D@ Mostramos los datos de cada Cila @D END 96!LE/ END$$ ?C+LL mis*productos, -$$ NN.. E##%# 17JS ,8J888- No data T zero roUs CetcIed, selected, or processed El procedimiento Cunciona pero al Cinal da un mensaje de error Ha que el 2ucle intenta se<uir leHendo mEs allE del Cinal del cursor. Lo ele<ante es eAitar que se produzca o usar un manejador de error Solucin 1 D@ Solo Aa a leer tantas Aeces como le indica el nQmero de Cilas @D

JK +S!# L +dministracin de Sistemas .estores de Bases de Datos

7DJ8

DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S mis*productos1$$ C#E+"E &#%CED'#E mis*productos1, BE.!N DECL+#E codi<o !N",J-/ DECL+#E descri 5+#C6+#,>8-/ DECL+#E num*Cilas !N"/ DECL+#E cur*prod C'#S%# (%# SELEC" producto*no, descripcion (#%M productos/ D@ +2rimos el cursor H <uardamos su nQmero de Cilas @D %&EN cur*prod/ SELEC" (%'ND*#%9S, - !N"% num*Cilas/ !( num*Cilas 0 8 "6EN SELEC" ;La ta2la de productos estE AacRa ;/ END !(/ 96!LE ,num*Cilas :? 8- D% (E"C6 cur*prod !N"% codi<o, descri/ SELEC" codi<o, descri/ D@ Mostramos los datos de cada Cila @D SE" num*Cilas 0 num*Cilas L1/ END 96!LE/ END$$ ?C+LL mis*productos1, -$$ Solucin J D@ 'tilizamos un manejador de tipo ;no encuentro mEsO @D DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S mis*productosJ$$ C#E+"E &#%CED'#E mis*productosJ, BE.!N DECL+#E no*mas !N" DE(+'L" 8/ DECL+#E codi<o !N",J-/ DECL+#E descri 5+#C6+#,>8-/ DECL+#E cur*prod C'#S%# (%# SELEC" producto*no, descripcion (#%M productos/ DECL+#E C%N"!N'E 6+NDLE# (%# N%" (%'ND SE" no*mas 0 1/ D@ +2rimos el cursor H <uardamos su nQmero de Cilas @D %&EN cur*prod/ +1 L%%& (E"C6 cur*prod !N"% codi<o, descri/ !( no*mas 0 1 "6EN LE+5E +1/ END !(/ SELEC" codi<o, descri/ D@ Mostramos los datos de cada Cila @D END L%%& +1/ END$$

JK +S!# L +dministracin de Sistemas .estores de Bases de Datos

BDJ8

DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S mis*productosJ$$ C#E+"E &#%CED'#E mis*productosJ, BE.!N DECL+#E no*mas !N" DE(+'L" 8/ DECL+#E codi<o !N",J-/ DECL+#E descri 5+#C6+#,>8-/ DECL+#E cur*prod C'#S%# (%# SELEC" producto*no, descripcion (#%M productos/ DECL+#E C%N"!N'E 6+NDLE# (%# N%" (%'ND SE" no*mas 0 1/ D@ +2rimos el cursor H <uardamos su nQmero de Cilas @D %&EN cur*prod/ 96!LE no*mas 0 8 D% (E"C6 cur*prod !N"% codi<o, descri/ SELEC" codi<o, descri/ D@ Mostramos los datos de cada Cila @D END 96!LE/ END$$ ?C+LL mis*productosJ, -$$ 9.).- CONDICIONES * MANE+ADORES DE ERROR (%ANDLERS) L L Cuando en un pro<rama se produce un error inesperado IaH J posi2ilidades de actuacin Dejar que el pro<rama termine inmediatamente para que el S.BD tome el control H lo resuelAa a su manera. Escri2ir cdi<o en el propio pro<rama para que <estione el error.

Se llama condicin o ePcepcin, al error que se produce en tiempo de ejecucin. Cada ePcepcin de2e tener su manejador correspondiente, que se encar<arE de procesar el error. Su declaracin es la si<uiente DECL+#E tipo*manejador 6+NDLE# (%# Aalor*condicin V, NW instruccin/ "ipo*manejador C%N"!N'E X E)!" X 'ND% 5alor*condicin SQLS"+"E Aalor X SQL9+#N!N. X N%" (%'ND X N !nstruccin puede ser Qnica o Aarias en un 2loque BE.!N N END C%N"!N'E, continQa la rutina actual que proAoc el error despus de ejecutar la instruccin de manejador. E)!", termina la ejecucin. 'ND%, desIace lo Ha IecIo, aunque de momento no estE implementado. SQLS"+"E Aalor, cdi<o de error de SQL. SQL9+#N!N., incluHe todos los cdi<os SQLS"+"E que empiezan por 81 ,Uarnin<sN%" (%'ND, los que empiezan por 8J ,no encontradosCuando se tra2aja con cursores lo mEs Ia2itual es usar un manejador ,Iandler- para eAitar que se produzca un error ;no data to CetcIO cuando se lle<a al Cinal del cursor H Ha no quedan mEs Cilas que leer, es decir DECL+#E C%N"!N'E 6+NDLE# (%# N%" (%'ND instruccin/

JK +S!# L +dministracin de Sistemas .estores de Bases de Datos

>DJ8

Ejemplo Cursor con manejador que recorre el resultado de una SELEC" H muestra el total de re<istros encontrados DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S simple*sqlJ$$ C#E+"E &#%CED'#E simple*sqlJ,jo2 5+#C6+#,18-BE.!N DECL+#E no*mas !N" DE(+'L" 8/ DECL+#E total !N" DE(+'L" 8/ DECL+#E re<i 5+#C6+#,J>>-/ DECL+#E cur*oC C'#S%# (%# SELEC" apellido (#%M empleados 96E#E oCicio 0 jo2/ DECL+#E C%N"!N'E 6+NDLE# (%# N%" (%'ND SE" no*mas 0 1/ %&EN cur*oC/ B1 L%%& (E"C6 cur*oC !N"% re<i/ !( no*mas 0 1 "6EN LE+5E B1/ END !(/ SE" total 0 total =1/ SELEC" re<i/ END L%%& B1/ CL%SE cur*oC/ SELEC" total/ END$$ ?C+LL simple*sqlJ,F5ENDED%#G-$$ Ejemplo En este procedimiento se deCine un cursor so2re la ta2la &#%D'C"%S H se realiza un 2ucle que comprue2a si la cantidad en stocY de cada uno de los productos es menor que J>. En caso de que el stocY sea menor que J>, se <uarda inCormacin de dicIo producto en una ta2la temporal. 'na Aez procesados todos los re<istros de la ta2la &#%D'C"%S se Aisualiza en pantalla la ta2la temporal H se elimina. !nicialmente se Ian declarado tanto el cursor como el manejador del error N%" (%'ND para que no se produzca un error cuando se lle<ue al Cinal DEL!M!"E# $$ D#%& &#%CED'#E !( E)!S"S Cursor&roc$$ C#E+"E &#%CED'#E Cursor&roc, BE.!N DECL+#E no*mas, cant*stocY !N" DE(+'L" 8/ DECL+#E prd*code 5+#C6+#,J>>-/ DECL+#E cur*prod C'#S%# (%# SELEC" producto*no (#%M productos/ DECL+#E C%N"!N'E 6+NDLE# (%# N%" (%'ND SE" no*mas 0 1/ D@ para <uardar inCormacin de lo< en FinColo<sG @D C#E+"E "+BLE inColo<s , id !N",11- N%" N'LL +'"%*!NC#EMEN", ms< 5+#C6+#,J>>- N%" N'LL, &#!M+#3 4E3 ,!d-/ %&EN cur*prod/ (E"C6 cur*prod !N"% prd*code/ #E&E+" JK +S!# L +dministracin de Sistemas .estores de Bases de Datos ZDJ8

SELEC" stocY !N"% cant*stocY (#%M productos 96E#E producto*no 0 prd*code/ !( cant*stocY : J> "6EN !NSE#" !N"% inColo<s,ms<- 5+L'ES ,prd*code-/ END !(/ (E"C6 cur*prod !N"% prd*code/ 'N"!L no*mas 0 1 END #E&E+"/ CL%SE cur*prod/ SELEC" @ (#%M inColo<s/ D#%& "+BLE inColo<s/ END$$ DEL!M!"E# / 9.,.- E+ERCICIOS 1. 6acer un procedimiento so2re la BD Aentas que muestre los datos de los empleados que no son Aendedores H el nom2re de su departamento. Delimiter // drop procedure if exists novendedores// create procedure novendedores() begin declare no_mas int default 0; declare apellido_c varchar(30); declare oficio_c varchar(10); declare fecha_alta_c date; declare dep_no_c int(2); declare dpto varchar(30); declare cursor_noven cursor for select apellido oficio dep_no from empleados !here oficio"#$vendedor$; Declare continue handler for not found set no_mas%1; open cursor_noven;

fecha_alta

fetch cursor_noven into apellido_c oficio_c fecha_alta_c dep_no_c; !hile no_mas%0 do select dnombre from departamentos !here dep_no%dep_no_c into dpto; select apellido_c oficio_c fecha_alta_c dpto; fetch cursor_noven into apellido_c oficio_c fecha_alta_c dep_no_c; end !hile; close cursor_noven; end// delimiter ;

drop procedure if exists novendedor&; JK +S!# L +dministracin de Sistemas .estores de Bases de Datos MDJ8

delimiter // create procedure novendedor&() begin declare no_mas int default 0; declare emp_no_c int; declare apellido_c varchar(30); declare oficio_c varchar(30); declare dep_no_c int; declare dnombre_c varchar(30); declare cursor'0 cursor for select empleados(emp_no empleados(apellido empleados(oficio empleados(dep_no departamentos(dnombre from empleados departamentos !here empleados(dep_no%departamentos(dep_no and oficio) %$vendedor$; declare continue handler for not found set no_mas%1; open cursor'0; fetch cursor'0 into emp_no_c apellido_c oficio_c dep_no_c dnombre_c; !hile no_mas%0 do select emp_no_c apellido_c oficio_c dep_no_c dnombre_c; fetch cursor'0 into emp_no_c apellido_c oficio_c dep_no_c dnombre_c; end !hile; close cursor'0; end // delimiter ; call novendedor&(); J. +l ejercicio anterior, a[adir que para los empleados cuHo departamento sea M+D#!D, le su2amos el sueldo un 18\. Delimiter // drop procedure if exists novendedorsal// create procedure novendedorsal() begin declare no_mas int default 0; declare apellido_c varchar(30); declare oficio_c varchar(10); declare fecha_alta_c date; declare dep_no_c int(2); declare dpto varchar(30); declare salario_c decimal(* 2); declare cursor_noven cursor for select apellido oficio fecha_alta dep_no from empleados !here oficio"#$vendedor$; Declare continue handler for not found set no_mas%1; open cursor_noven; salario

fetch cursor_noven into apellido_c oficio_c salario_c fecha_alta_c dep_no_c; !hile no_mas%0 do if dep_no_c%30 then JK +S!# L +dministracin de Sistemas .estores de Bases de Datos ]DJ8

update empleados !here apellido%apellido_-; select dnombre from into dpto; select salario_c fecha_alta_c dpto; select $.alario del

set

salario%salario+(salario,0(1)

departamentos !here dep_no%dep_no_c apellido_c /mpleado 0ctuali1ado($; oficio_c

end if; select dnombre from departamentos !here dep_no%dep_no_c into dpto; select apellido_c oficio_c fetch cursor_noven salario_c fecha_alta_c dep_no_c; end !hile; close cursor_noven; end// delimiter ; OTRA "ORMADelimiter // drop procedure if exists novendedorsal// create procedure novendedorsal() begin declare no_mas int default 0; declare apellido_c varchar(30); declare oficio_c varchar(10); declare fecha_alta_c date; declare dep_no_c int(2); declare dpto varchar(30); declare salario_c decimal(* 2); declare cursor_noven cursor for select apellido oficio salario fecha_alta dep_no from empleados !here oficio"#$vendedor$ and dep_no in(select dep_no from departamentos !here localidad%$madrid$); Declare continue handler for not found set no_mas%1; open cursor_noven; fetch cursor_noven into apellido_c oficio_c salario_c fecha_alta_c dep_no_c; !hile no_mas%0 do update empleados set salario%salario+(salario,0(1) !here apellido%apellido_-; select dnombre from departamentos !here dep_no%dep_no_c into dpto; select apellido_c oficio_c salario_c fecha_alta_c dpto; fetch cursor_noven into apellido_c oficio_c salario_c fecha_alta_c dep_no_c; end !hile; close cursor_noven; end// delimiter ; JK +S!# L +dministracin de Sistemas .estores de Bases de Datos SDJ8 salario_c fecha_alta_c dpto; into apellido_c oficio_c

7. E^E#C!C!%S DE B+^+D+ DE S'ELD%S Con motiAo de la crisis econmica se Aa a 2ajar el sueldo a todos los empleados un >\. !mplementar en MHSQL el proceso que realice la 2ajada de sueldos ,usando ;cursoresO-. Delimiter // drop procedure if exists ba2ada// create procedure ba2ada() begin declare dni char(3); declare salario decimal(4 2); declare no_mas int default 0; declare cursor_ba2a cursor for select nif sueldo from nominas; Declare continue handler for not found set no_mas%1; open cursor_ba2a; fetch cursor_ba2a into dni salario; !hile no_mas%0 do update nominas set sueldo%sueldo5(sueldo,0(06) !here nif%dni; select nif sueldo from nominas !here nif%dni; fetch cursor_ba2a into dni salario; end !hile; close cursor_ba2a; end// delimiter ; Los datos de los salarios de los empleados se <uardan en la ta2la nminas C#E+"E "+BLE nominas , niC C6+#,S- &#!M+#3 4E3, sueldo N'ME#!C ,],J- N%" N'LL-/ En la Iilera DB*Bajada*Sueldos.sql tenemos las si<uientes instrucciones SQL para la creacin de las ta2las de estos ejercicios D#%& D+"+B+SE !( E)!S"S practica*scripts/ C#E+"E D+"+B+SE practica*scripts/ 'SE practica*scripts/ D#%& "+BLE !( E)!S"S nominas/ C#E+"E "+BLE nominas , niC C6+#,S- &#!M+#3 4E3, sueldo N'ME#!C,],J- N%" N'LL-/ !NSE#" !N"% nominas 5+L'ES ,_1J7BZM]+_, 1>888.88-, ,_1J7BZM]B_, 1B888.88-, ,_1J7BZM]C_, J>888.88-, ,_1J7BZM]D_, J8888.88-, ,_1J7BZM]E_, 1>888.88-, ,_1J7BZM](_, 1J888.88-, ,_1J7BZM]._, 18888.88-, ,_1J7BZM]6_, S888.88-, ,_1J7BZM]!_, 11>88.88-, ,_1J7BZM]^_, 1>M>8.88-, ,_1J7BZM]4_, 1]888.88-/ C#E+"E "+BLE nominasJ , JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 18DJ8

niC C6+#,S- &#!M+#3 4E3, <rupo C6+#,1- N%" N'LL, sueldo N'ME#!C ,],J- N%" N'LL-/ !NSE#" !N"% nominasJ 5+L'ES ,_1J7BZM]+_, `B`, 1>888.88-, ,_1J7BZM]B_, `B`, 1B888.88-, ,_1J7BZM]C_, `+`, J>888.88-, ,_1J7BZM]D_, `+`, J8888.88-, ,_1J7BZM]E_, `B`, 1>888.88-, ,_1J7BZM](_, `B`, 1J888.88-, ,_1J7BZM]._, `C`, 18888.88-, ,_1J7BZM]6_, `D`, S888.88-, ,_1J7BZM]!_, `C`, 11>88.88-, ,_1J7BZM]^_, `B`, 1>M>8.88-, ,_1J7BZM]4_, `+`, 1]888.88-/ B. !mplementar una se<unda Aersin del procedimiento de 2ajada de sueldo. Consiste en 2ajar el sueldo a todos los empleados en 2ase a un determinado porcentaje en Cuncin a su escala salarial. &ara ello a- Crear una nueAa ta2la nominasJ a[adiendo a la ta2la nominas un campo que represente la escala salarial. El campo se llamarE <rupo H serE de tipo C6+#,1C#E+"E "+BLE nominasJ , NiC C6+#,S- &#!M+#3 4E3, .rupo C6+#,1- N%" N'LL, Sueldo N'ME#!C ,],J- N%" N'LL-/ 2- Crear una Cuncin llamada dame*porcentaje que acepte como parEmetro la escala salarial H deAuelAa el porcentaje de disminucin a aplicar. Los porcentajes de 2ajada que deAolAerE serEn 18\, ]\, >\ H 7\ para los <rupos +, B, C H D, respectiAamente. Delimiter // drop function if exists ba2adas// create function ba2adas(clase char(1)) returns 789 begin declare porcenta2e int default 0; if clase%$0$ then set porcenta2e%10; elseif clase%$:$ then set porcenta2e%4; elseif clase%$-$ then set porcenta2e%6; elseif clase%$D$ then set porcenta2e%3; else set porcenta2e%0; end if; return porcenta2e; end// delimiter ; c- Crear una nueAa Aersin del procedimiento 2aja*sueldos para que llame a la Cuncin dame*porcentaje para o2tener el porcentaje de disminucin de salario. >. ModiCicar la Cuncin dame*porcentaje para que ePtrai<a de una ta2la los porcentajes de 2ajada en Cuncin del <rupo. La ta2la de2erE tener dos campos, uno con el <rupo ,+, B, N- H otro campo con el porcentaje a descontar. Crear una tercera Aersin del procedimiento 2aja*sueldos para que use la nueAa Cuncin. JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 11DJ8

C#E+"E "+BLE porcentaje*<rupo , <rupo C6+#,1- &#!M+#3 4E3, porcentaje !N"E.E#,7- N%" N'LL-/ !NSE#" !N"% porcentaje*<rupo 5+L'ES ,F+G, 18-, ,FBG, M-, ,FCG, >-, ,FDG, 7-, ,FEG, 8-/ Z. ModiCica el procedimiento almacenado 2aja*sueldos para que ademEs <uarde en una ta2la auPiliar cantidad_reducida ,aniC*empleado, cantidad- la cantidad que se Aa a reducir el sueldo de cada uno de los empleados. Si Ha ePistieran datos so2re el empleado en la ta2la cantidad*reducida, el Aalor calculado sustituirE al Aalor anterior. C#E+"E "+BLE cantidad*reducida , NiC*empleado C6+#,S- &#!M+#3 4E3, cantidad N'ME#!C ,Z,J- N%" N'LL-/ )..- TRI//ERS o DIS$ARADORES. 'n tri<<er o disparador es un tipo especial de rutina almacenada que se actiAa o ejecuta cuando en una ta2la ocurre un eAento de tipo !NSE#", DELE"E o '&D+"E. Es decir implementan una Cuncionalidad asociada a cualquier cam2io en una ta2la. MucIas Aeces se conocen como re<las ECA ,EAento Condicin Accin- Ha que para deCinir un disparador es necasario especiCicar L E&!nto, que es la operacin que proAoca la actiAacin ,!NSE#", DELE"E o '&D+"E-. L Con0icin, es la condicin de de2e cumplirse para que se actiAe el disparador. L Accin, instrucciones que ejecuta el disparador cuando se actiAa. Su sintaPis <eneral es No 'r! E&!nto C#E+"E "#!..E# nom_disparador bBE(%#E X +("E# c b!NSE#" X DELE"E X '&D+"E V%( columnasWc %N tabla V#E(E#ENC!N. %LD V#%9W V+SW vieja_fila NE9 V#%9W V+SW nueva_fila %LD "+BLE V+SW vieja_tabla NE9 "+BLE V+SW nueva_tabla W Vb(%# E+C6 #%9 X (%# E+C6 S"+"EMEN" cW V96EN ,condicin-W b sentencia_sq;l X BE.!N sentencia_sql_1; sentencia_sql_n; END c

Con0icin Accin

'SE e2anca/ ,antes ejecutar el DB*e2anca.sqlEjemplo Crear un disparador que sume las cantidades insertadas en la Aaria2le de usuario dsum, cada Aez que se introduce un nueAo moAimiento C#E+"E "#!..E# insertar*moA BE(%#E !NSE#" %N moAimiento (%# E+C6 #%9 SE" dsum 0 dsum = NE9.cantidad/ &ara Aer su Cuncionamiento SE" dsum 0 18888/ SELEC" dsum/ !NSE#" !N"% moAimiento 5+L'ES ,`11111111`, `J811L11LJ8`, J888, >M, 1-/ JK +S!# L +dministracin de Sistemas .estores de Bases de Datos

1JDJ8

SELEC" dsum/ Las instrucciones para <estionar los disparadores son D#%& "#!..E#. La sintaPis mEs utilizada H sencilla es C#E+"E "#!..E# nom2re*disp momento*disp eAento*disp %N nom2re*ta2la (%# E+C6 #%9 sentencia*disp/ L momento*disp, puede ser BE(%#E ,antes- o +("E# ,despus- para indicar que se ejecute antes o despus de la sentencia que lo actiAa. L eAento*disp, indica la clase de sentencias que actiAa el disparador ,!NSE#", '&D+"E o DELE"E-. No puede Ia2er dos disparadores en una misma ta2la que correspondan al mismo momento H sentencia. L (%# E+C6 #%9, Iace reCerencia a las acciones a lleAar a ca2o so2re cada Cila de la ta2la indicada. L sentencia*disp, es la sentencia que se ejecuta cuando se actiAa el disparador. Si IaH mEs de una IaH que colocarlas entre BE.!N N. END. Las columnas de la ta2la asociada pueden reCerenciarse con L %LD.nom*columna ,Aalor de la columna antes de ser actualizada o 2orrada-. L NE9.nom*columna ,despus de ser actualizada o insertada-. En un disparador para !NSE#" solo se puede usar NE9, Ha que no IaH Aalor preAio. &ara DELE"E, solo %LD. Si es para '&D+"E podemos usar am2os. &ara o2tener inCormacin de los disparadores creados S6%9 "#!..E#S V b (#%M X !N c 2d*nom2reW V L!4E FpatrnG X 96E#E ePpresinW/ +l crear los disparadores se crea un nueAo re<istro en la ta2la INFOR !"ION_#$%& ! llamada INFOR !"ION_#$%& !'"RI((&R# que se puede Aer de la si<uiente manera SELEC" tri<<er*name, action*statement (#%M inCormation*scIema.tri<<ers/ &ara eliminar disparadores D#%& "#!..E# V !( E)!S"S W Vesquema*nom2re.Wnom2re*disp/ )..).- USO DE TRI//ERS o DIS$ARADORES )..).). Contro( 0! s!sion!sMucIas Aeces queremos almacenar ciertos Aalores en Aaria2les de sesin creadas por el usuario para Aer un resumen de lo realizado en la sesin. El caso del ejemplo anterior. SE" dsum 0 8/ !NSE#" !N"% moAimiento 5+L'ES ,`11111111`, `J811L11LJ8`, 1888, >], 1-, ,`11111111`, `J811L11LJ8`, L>88, >S, 1-, ,`11111111`, `J811L11LJ8`, M>8, Z8, 1-/ SELEC" dsum +S F"otal insertadoG/ )..).,. Contro( 0! &a(or!s 0! !ntra0a&ara controlar Aalores insertados o actualizados en ta2las JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 17DJ8 C#E+"E "#!..E#, S6%9 "#!..E# H

DEL!M!"E# $$ C#E+"E "#!..E# compro2ar*saldo BE(%#E '&D+"E %N moAimiento (%# E+C6 #%9 BE.!N !( NE9.cantidad : 8 "6EN SE" NE9.cantidad 0 8/ ELSE!( NE9.cantidad ? 188 "6EN SE" NE9.cantidad 0 188/ END !(/ END$$ '&D+"E moAimiento SE" cantidad 0 >88 UIere idmoA 0 >S$$ '&D+"E moAimiento SE" cantidad 0 L>8 UIere idmoA 0 Z8$$ SELEC" @ (#%M moAimiento$$ )..).1. Mant!ni i!nto 0! ca 2os 0!ri&a0os&ara actualizar campos que pueden calcularse a partir de otros, por ejemplo el campo saldo en la ta2la cuenta cada Aez que se Iace un in<reso DEL!M!"E# / C#E+"E "#!..E# actualizar*cta BE(%#E !NSE#" %N moAimiento (%# E+C6 #%9 '&D+"E cuenta SE" saldo 0 saldo = NE9.cantidad 96E#E cod*cuenta 0 NE9.cod*cuenta/ SELEC" @ (#%M cuenta UIere cod*cuenta 0 1/ !NSE#" !N"% moAimiento 5+L'ES ,`11111111`, `J811L11LJ8`, 1888, Z1, 1-/ SELEC" @ (#%M cuenta UIere cod*cuenta 0 1/ )..).3. Esta04sticas&odemos re<istrar estadRsticas de operaciones o Aalores de nuestras BD en tiempo real. &or ejemplo podemos re<istrar los in<resos que se Iacen cada mes en una ta2la aparte D@ Creamos la ta2la donde almacenar la cantidad total para cada mes @D C#E+"E "+BLE testadistica ,tcantidad dou2le, tmes cIar,J--/ D@ Creamos una Cuncin ePiste que deAuelAe 1 o 8 si ePiste o no el re<istro para cada mes @D DEL!M!"E# $$ D#%& ('NC"!%N !( E)!S"S ePiste$$ C#E+"E ('NC"!%N ePiste ,mes cIar,J-#E"'#NS int BE.!N DECL+#E cont !N"/ SELEC" count,@- !N"% cont (#%M testadistica 96E#E tmes 0 mes/ #E"'#N cont/ END$$ C#E+"E "#!..E# in<resos*dia +("E# !NSE#" %N moAimiento (%# E+C6 #%9 BE.!N !( ePiste,M%N"6,NE9.CecIaIora-- 0 8 "6EN !NSE#" !N"% testadistica,tcantidad, tmesM%N"6,NE9.CecIaIora- -/ JK +S!# L +dministracin de Sistemas .estores de Bases de Datos

5+L'ES

,NE9.cantidad, 1BDJ8

ELSE '&D+"E testadistica SE" tcantidad 0 NE9.cantidad = tcantidad 96E#E tmes 0 M%N"6,NE9.CecIaIora-/ END !(/ END$$ DEL!M!"E# / !NSE#" !N"% moAimiento 5+L'ES ,`11111111`, `J811L11LJ]`, 1888, ZJ, 1-, ,`11111111`, `J811L11LJ]`, LB88, Z7, 1-, ,`11111111`, `J811L18LJ8`, M>8, ZB, 1-/ SELEC" @ (#%M testadistica/ )..).5. R!6istro o au0itor4aCuando mucIos usuarios acceden a la BD puede que el re<istro lo< no sea suCiciente o que solo se necesite al<una inCormacin especRCica. &ara ello podemos crear un disparador que despus de un DELE"E o '&D+"E, <uarde determinados Aalores del re<istro o al<una inCormacin de utilidad en una ta2la tipo lo<. D@ Creamos una ta2la donde almacenar la inCormacin de actualizacin @D C#E+"E "+BLE auditoria*moA , !d*moA !N" N%" N'LL +'"%*!NC#EMEN" &#!M+#3 4E3, Cod*cta*ant !N",11-, (ecIa*ant D+"E, Cantidad*ant D%'BLE, Cod*cta*n !N",11-, (ecIa*n D+"E, Cantidad*n D%'BLE, 'suario 5+#C6+#,B8-, (ecIa*mod D+"E"!ME -/ D@ Cada Aez que se actualice la ta2la moAimientos, se crearE un re<istro en auditoria*moA @D DEL!M!"E# $$ C#E+"E "#!..E# auditoria*moA +("E# '&D+"E %N moAimiento (%# E+C6 #%9 BE.!N !NSE#" !N"% auditoria*moA ,Cod*cta*ant, (ecIa*ant, Cantidad*ant, Cod*cta*n, (ecIa*n, Cantidad*n, 'suario, (ecIa*mod5+L'ES,%LD.cod*cuenta, %LD.CecIaIora, %LD.cantidad, NE9.cod*cuenta, NE9.CecIaIora, NE9.cantidad, C'##EN"*'SE#,-, N%9,- -/ END$$ '&D+"E moAimiento SE" cod*cuenta 0 F17G, cantidad 0 >>8, CecIaIora 0 GJ811L18LJSG UIere idmoA 0 7M$$ '&D+"E moAimiento SE" cantidad 0 M>8 UIere idmoA 0 7]$$ SELEC" @ (#%M auditoria*moA$$ )..,.- E+ERCICIOS 1. En la BD 5entas, crea un disparador que ase<ure que el precio de cada producto sea un nQmero maHor de J88. delimiter // drop trigger if exists comp_precios// create trigger comp_precios after insert on productos for each ro! begin JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 1>DJ8

if ne!(precio " 200 then delete from productos !here precio " 200; end if; end// delimiter ; J. En la 2ase ebanca, Iaz un disparador que cree un re<istro en la ta2la nrojos con los campos cliente, cuenta, CecIa H saldo cada Aez que al<Qn cliente se quede en nQmeros rojos en al<una de sus cuentas. drop trigger if exists registro; delimiter // create trigger registro after update on cuenta for each ro! begin if ne!(saldo " 0 then insert into nro2os values ne!(fecha_creacion ne!(cod_cliente ne!(cod_cuenta ne!(saldo); end if; end // delimiter ; update cuenta set saldo%5600 !here cod_cliente%2; select , from nro2os;

(null

7. 6az lo necesario para que cada Aez que un cliente de ebanca in<rese mEs de 1888e se le 2oniCique con 188e, solo para clientes con cuentas que superen tres a[os de anti<fedad H para moAimientos realizados entre el 1 de enero de J811 H el 71 de marzo de J811. drop trigger if exists bono; delimiter // create trigger bono after insert on movimiento for each ro! if ne!(cantidad # 1000 then update cuenta set saldo%saldo+ne!(cantidad+100 !here fecha_creacion"$2011502513$ and cod_cuenta in(select cod_cuenta from movimiento !here cuenta(cod_cuenta%movimiento(cod_cuenta and fechahora bet!een $2011501501$ and $2011503531$); end if; end // delimiter ; )).- E7ENTOS. Los eAentos son tareas que se ejecutan de acuerdo a un Iorario. &or eso, tam2in se les llama eAentos pro<ramados. Es un concepto similar al &ro<ramador de "areas ,comando +"- de 9indoUs. 'n eAento se identiCica por su nom2re H el esquema o la BD al que se le asi<na. LleAa a ca2o una accin especRCica ,una o Aarias instrucciones SQL dentro de un 2loque BE.!NDEND- de acuerdo a un Iorario. &ueden distin<uirse J tipos de eAentos los que se pro<raman para una Qnica ocasin H los que ocurren peridicamente cada cierto tiempo. JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 1ZDJ8

La Aaria2le <lo2al event_sc)eduler determina si el pro<ramador de eAentos estE Ia2ilitado H en ejecucin en el serAidor. &uede estar a %N ,actiAado-, %(( ,desactiAado- H D!S+BLED ,si queremos que no se pueda actiAar en tiempo de ejecucin-. Con el si<uiente comando podemos compro2ar si el pro<ramador de eAentos estE actiAo Ha que se ejecuta como un Iilo mEs del serAidor S6%9 &#%CESSL!S" g. Si *lobal_event_sc)eduler no estE como D!S+BLED podemos actiAar el pro<ramador con SE" .L%B+L eAent*scIeduler 0 %N/ )).).- /ESTI8N DE E7ENTOS &odemos usar C#E+"E E5EN", +L"E# E5EN", S6%9 E5EN" H D#%& E5EN". )).).). Cr!acin 0! !&!ntosC#E+"E V DE(!NE# 0 b user X C'##EN"*'SE# c W E5EN" V !( N%" E)!S"S W nom2re*eAento %N SC6ED'LE# scIedule V %N C%M&LE"!%N V N%" W &#ESE#5E W V EN+BLE X D!S+BLE X D!S+BLE %N SL+5E W V C%MMEN" FcomentarioG W D% cuerpo*eAento/ scIedule +" timestamp V = !N"E#5+L interAaloW N X E5E#3 interAalo W V S"+#"S timestamp V = !N"E#5+L interAaloW N W V ENDS timestamp V = !N"E#5+L interAaloW N W interAalo quantitH b 3E+# X Q'+#"E# X M%N"6 X D+3 X 6%'# X M!N'"E X 9EE4 X SEC%ND X 3E+#*M%N"6 X D+3*6%'# X D+3*M!N'"E X D+3*SEC%ND X 6%'#*M!N'"E X 6%'#*SEC%ND X M!N'"E*SEC%ND c L La clEusula %N SC6ED'LE permite esta2lecer como H cuando se ejecutarE el eAento una sola Aez, durante un interAalo, cada cierto tiempo o en una CecIaLIora de inicio H Cin determinadas. L DE(!NE# especiCica el usuario cuHos permisos se tendrEn en cuenta en la ejecucin del eAento. L cuerpo*eAento es el contenido del eAento que se Aa a ejecutar. L C%M&LE"!%N permite mantener el eAento aunque IaHa ePpirado mientras que D!S+BLE permite crear el eAento en estado inactiAo. L D!S+BLE %N SL+5E sirAe para indicar que el eAento se cre en el master de una replicacin H que, por tanto, no se ejecutarE en el esclaAo. Ejemplo 1 5emos si estE actiAo el pro<ramador de eAentos S6%9 &#%CESSL!S" g. Si no lo estE, lo actiAamos SE" .L%B+L eAent*scIeduler 0 %N/ C#E+"E "+BLE cron ,id !N",11- &#!M+#3 4E3 +'"%*!NC#EMEN", data D+"E-/ C#E+"E E5EN" primer*eAento %N SC6ED'LE E5E#3 > SEC%ND JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 1MDJ8

%N C%M&LE"!%N &#ESE#5E EN+BLE D% !NSE#" !N"% cron,data- 5+L'ES ,N%9,--/ SELEC" @ (#%M cron/ Si queremos eliminarlo D#%& E5EN" primer*eAento/ Ejemplo J D@ en e2anca, se 2oniCica con 188e a las cuentas dadas de alta en el interAalo de 1 mes @D C#E+"E E5EN" 2oniCicacion %N SC6ED'LE +" C'##EN"*"!MES"+M& = !N"E#5+L 1 M%N"6 D% '&D+"E cuenta SE" saldo 0 saldo = 188 96E#E CecIa*creacion BE"9EEN noU,- +ND D+"E*+DD,N%9,-, interAal L1 montI-/ )).).,. Mo0i9icacin 0! !&!ntos+L"E# V DE(!NE# 0 b user X C'##EN"*'SE# c W E5EN" nom2re*eAento V %N SC6ED'LE# scIedule W V %N C%M&LE"!%N V N%" W &#ESE#5E W V #EN+ME "% nueAo*nom2re*eAento W V EN+BLE X D!S+BLE X D!S+BLE %N SL+5E W V C%MMEN" FcomentarioG W V D% cuerpo*eAento W/ )).).1. Consu(ta 0! !&!ntos&ara mostrar la inCormacin de eAentos asociados a un esquema o BD H Ciltrados se<Qn el patrn que determinamos o una clEusula 96E#E. S6%9 E5EN"S V b (#%M X !N c nom2re*esquema W V L!4E FpatrnG X 96E#E ePpresin W "am2in podemos consultar la ta2la !N(%#M+"!%N*SC6EM+.E5EN"S del diccionario de datos. )).,.- E+ERCICIOS 1. Crea un eAento que car<ue una comisin del J\ so2re las cuentas en nQmeros rojos cada primero de mes comenzando el B de (EB#E#% de J81B ,actualice el saldo ne<atiAo a[adiendo el J\-.
drop event if exists comision; -;/09/ /</89 comision =8 .->/D?@/ /</;A 1 B=89> .90;9. C201'50250'C D= ?DD09/ cuenta ./9 .0@D=%.0@D=+(.0@D=,0(02) E>/;/ .0@D="0;

J. &ro<rama un anElisis ,!N!+,-& "!.+&- de las ta2las de la 2ase e2anca para el 1 de Ce2rero de J81J. D;=D /</89 7F /G7.9. 080@7.7.; -;/09/ /</89 080@7.7. =8 .->/D?@/ 09 current_timestamp D= 080@AH/ 90:@/ -@7/89/ -?/890 B=<7B7/89= 8;=I=.; 7. Crea un eAento que re<istre diariamente los moAimientos superiores a 1888e en una ta2la temponal tmp_mov. Cralo desIa2ilitado. JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 1]DJ8

D;=D table tmp_mov; -;/09/ 90:@/ tmp_mov (movi int primarJ KeJ cuenta int cantidad int fecha date); D;=D /</89 7F /G7.9. ;/L7.9;=_B=<7B7/89=.; -;/09/ /</89 ;/L7.9;=_B=<7B7/89=. =8 .->/D?@/ everJ 1 daJ disable D= insert into tmp_mov select idmov cod_cuenta cantidad fechahora from movimiento !here cantidad#1000; ),.- RESUMEN DE O:+ETOS $ARA AUTOMATI;ACI8N DE TAREAS. $ROCEDIMIENTOS "UNCIONES 7ISTAS DIS$ARADORES E7ENTOS SCRI$TS Co an0os 2ara 2ro6ra acin 0! tar!as &eque[os pro<ramas que Iacen tareas sencillas H 2ien deCinidas. Se llaman con el comando ?C+LL. Similares a los procedimientos salAo que deAuelAen un Aalor H todos sus parEmetros son de tipo !N. &artes determinadas de la BD en Corma de ta2las procedentes de consultas so2re ta2las 2ase. De tipo BE(%#E H +("E# permiten desencadenar acciones ante modiCicaciones de la BD. #ealizan conjuntos de acciones en ciertos momentos temporales. &eque[os pro<ramas escritos en perl, pIp, pHtIon o C que permiten ampliar la Cuncionalidad de nuestro serAidor. &ro<ramas propios del S%. C#%N para LinuP, +" para 9indoUs.

)1.- E+ERCICIOS $RO$UESTOS (O$CIONAL). 1. 6az lo necesario para poder sa2er el mes H a[o con mejor saldo total en la 2ase ebanca. Dado que no tenemos modo de sa2er el saldo total mensual tenemos que crear un eAento que re<istre el saldo de cada mes en la ta2la saldo_mes J. Crea un procedimiento que encripte una cadena de caracteres cam2iando cada letra por la si<uiente, por ejemplo la a serRa la b H asR sucesiAamente ,usa las Cunciones !#$II H $%!R-. 7. 6az lo necesario so2re la 2ase de datos ebanca para alimentar la ta2la num_rojos con datos de las cuentas que Ian tenido nQmeros rojos en al<Qn momento del a[o. B. Crea una Cuncin que deAuelAa 1 8 si una Crase es o no palRndroma ,pala2ra a pala2ra-. >. Crea una Cuncin que deAuelAa 1 8 si una Crase es o no palRndroma ,letra a letra H sin considerar los espacios en 2lanco-. Z. Crea una Aista para la cuenta limitada que permita Aer el nQmero de moAimientos por dRa. +si<na los permisos necesarios a dicIa cuenta. hEs actualiza2le la Aistai M. Crea un eAento que recoja cada 7 Ioras el estado del serAidor en cuanto al nQmero de consultas realizadas H conePiones creadas. Los Aalores de2en almacenarse en la ta2la estado_servidor,idestado, numero_consultas, numero_cone/iones]. En la 2ase e2anca Iaz un procedimiento que pon<a en ne<rita el nom2re del cliente en la ta2la cliente. Los parEmetros son 8 para aplicarlo a todos los clientes o el dni del cliente. JK +S!# L +dministracin de Sistemas .estores de Bases de Datos 1SDJ8

S. En la BD 5entas, crea un procedimiento almacenado ,cursor- que deAuelAa el Aendedor con maHor nQmero de clientes. 18. En la BD 5entas, crea una ta2la Cantidad*&edir. Esta ta2la contendrE inCormacin de las cantidades de diCerentes productos que de2emos pedir a nuestros proAeedores para alcanzar un niAel de stocY en nuestro almacen cantidad*&edir ,id*producto, cantidad+demEs id*producto es claAe ajena de &#%D'C"%S

C#E+"E "+BLE cantidad*&edir , id*producto !N",J- &#!M+#3 4E3, cantidad !N" N%" N'LL, (%#E!.N 4E3 ,id*producto- #E(E#ENCES &#%D'C"%S ,producto*no- -/ Crea un procedimiento almacenado que analice si el stocY actual de cada uno de los productos es maHor de una ciCra que se le pasarE como parEmetro. Si no es asR, en la ta2la Cantidad*&edir se a[adir una Cila con el identiCicador del producto H la cantidad que se de2e pedir para alcanzar dicIo Aalor de stocY. 11. En la BD 5entas, crea una ta2la Comisiones. Esta ta2la contendrE inCormacin so2re la comisin que de2e reci2ir cada uno de los Aendedores Comisiones ,id*Aendedor, comision- +demEs id*Aendedor es claAe ajena de EM&LE+D%S C#E+"E "+BLE comisiones , id*Aendedor !N",B- &#!M+#3 4E3, comision N'ME#!C ,Z,J- N%" N'LL, (%#E!.N 4E3 ,id*Aendedor- #E(E#ENCES EM&LE+D%S ,emp*no- -/ Crea un procedimiento almacenado que actualice las comisiones de los empleados en la ta2la. El procedimiento almacenado calcularE, dado un cdi<o de Aendedor, una CecIa H el porcentaje de comisin, la comisin que le corresponde al empleado por los artRculos Aendidos desde la CecIa dada. En caso de que en la ta2la Comisiones no IaHa inCormacin de las comisiones del empleado se introducirE la inCormacin en la ta2la. En caso contrario, es decir, si Ha ePiste inCormacin so2re las comisiones del empleado, se acumularE el Aalor de la comisin al Ha ePistente. 1J.L En la BD 5entas, pro<rama un disparador que cada Aez que se inserte un empleado nueAo en la ta2la EM&LE+D%S, comprue2e si la comisin es N'LL H el oCicio 5ENDED%#, H si es asR lo <ra2e con comisin cero. Comprue2a que Cunciona 2ien.

JK +S!# L +dministracin de Sistemas .estores de Bases de Datos

J8DJ8