MySQL archivos - Página 2 de 5 - el blog de giltesa

1×26: Triggers para auditar

3 May 2012 giltesa Deja un comentario

/*
	Ejercicio 26:
	Realizar tres triggers "ejercicio26a", "ejercicio26b", y "ejercicio26c"
	que realicen una auditoria similar a la que realiza el trigger2 del
	apartado 9.3 de los materiales, pero para la tabla centros: 

		El trigger ejercicio26a auditará las operaciones de inserción.
		El trigger ejercicio26b auditará las operaciones de modificación.
		El trigger ejercicio26c auditará las operaciones de borrado.
	
	Antes de realizar el ejercicio necesitarás tener creada la tabla: 
		create table audita
		(
			mensaje varchar(200)
		)
		engine = innodb;
*/


-- Trigger A: Audita las operaciones de inserción.
delimiter $$
create trigger t_aud_insercion after insert on centros for each row
	begin
		insert into audita values
		(
			concat
			(
				'Inserccion realizada por ', user(), ' el dia ', now(),
				'. Valores insertados: "', new.numce, '", "', new.nomce, '" y "', new.seas, '"'
			)
		);
	end $$
delimiter ;

	
-- Trigger B: Audita las operaciones de modificación.
delimiter $$
create trigger t_aud_modificacion after update on centros for each row
	begin
		insert into audita values
		(
			concat
			(
				'Actualizacion realizada por ', user(), ' el dia ', now(),
				'. Anteriores valores: "', old.numce, '", "', old.nomce, '" y "', old.seas, '"',
				'. Nuevos valores: "', new.numce, '", "', new.nomce, '" y "', new.seas, '"'
			)
		);
	end $$
delimiter ;


-- Trigger C: Audita las operaciones de borrado.
delimiter $$
create trigger t_aud_borrado after delete on centros for each row
	begin
		insert into audita values
		(
			concat
			(
				'Fila borrada por ', user(), ' el dia ', now(),
				'. Valores borrados: "', old.numce, '", "', old.nomce, '" y "', old.seas, '"'
			)
		);
	end $$
delimiter ;

Ejercicio 26:

Realizar tres triggers "ejercicio26a", "ejercicio26b", y "ejercicio26c"

que realicen una auditoria similar a la que realiza el trigger2 del

apartado 9.3 de los materiales, pero para la tabla centros:

El trigger ejercicio26a auditará las operaciones de inserción.

El trigger ejercicio26b auditará las operaciones de modificación.

El trigger ejercicio26c auditará las operaciones de borrado.

Antes de realizar el ejercicio necesitarás tener creada la tabla:

create table audita

(

mensaje varchar(200)

)

engine = innodb;

-- Trigger A: Audita las operaciones de inserción.

delimiter $$

create trigger t_aud_insercion after insert on centros for each row

begin

insert into audita values

(

concat

(

'Inserccion realizada por ', user(), ' el dia ', now(),

'. Valores insertados: "', new.numce, '", "', new.nomce, '" y "', new.seas, '"'

)

);

end $$

delimiter ;

-- Trigger B: Audita las operaciones de modificación.

delimiter $$

create trigger t_aud_modificacion after update on centros for each row

begin

insert into audita values

(

concat

(

'Actualizacion realizada por ', user(), ' el dia ', now(),

'. Anteriores valores: "', old.numce, '", "', old.nomce, '" y "', old.seas, '"',

'. Nuevos valores: "', new.numce, '", "', new.nomce, '" y "', new.seas, '"'

)

);

end $$

delimiter ;

-- Trigger C: Audita las operaciones de borrado.

delimiter $$

create trigger t_aud_borrado after delete on centros for each row

begin

insert into audita values

(

concat

(

'Fila borrada por ', user(), ' el dia ', now(),

'. Valores borrados: "', old.numce, '", "', old.nomce, '" y "', old.seas, '"'

)

);

end $$

delimiter ;

1×25: Uso de Triggers con insert, update y delete

3 May 2012 giltesa Deja un comentario

/*
	Ejercicio 25:

	Realizar dos triggers que completen la funcionalidad del trigger1
	del apartado 9.3 de los materiales, mantener sincronizada la tabla
	alumnos_replica, pero para las operaciones de modificación
	(trigger ejercicio25a) y borrado (trigger ejercicio25b).
*/
/*
	Un trigger es un programa almacenado que no se ejecuta por voluntad del usuario, si no que se ejecuta solo de forma automática cuando se realizan modificaciones con las sentencias: insert, update y delete.
	Podemos elegir cuando se ha de ejecutar el trigger, si antes (before) de realizar la acción o después (after). El trigger solo funciona bajo la tabla que especifiquemos y cada tabla como máximo podrá tener 6 trigger, 2 por cada sentencia.

	Notas de interes:
		Muestra los triggers:	show triggers;
		Borra un trigger:		drop trigger t_copia;	
*/


-- Primero se copia una tabla para hacer las pruebas:
create table alumnos_replica (select * from alumnos);


-- Triger 1: Se encarga de sincronizar las nuevas filas insertadas:
delimiter $$
create trigger t_copia before insert on alumnos for each row
	begin
		-- Disponemos de "new" para obtener el nuevo valor en insert y update
		-- y de "old" para obtener el valor anterior en update y delete.
		insert into alumnos_replica values (new.id, new.alumno);
	end $$
delimiter;


-- Triger A: Mantiene las filas actualizadas:
delimiter $$
create trigger t_modifica before update on alumnos for each row
	begin
		update alumnos_replica set id = new.id, alumno = new.alumno where id = old.id;
	end $$
delimiter ;


-- Triger B: Borra las filas
delimiter $$
create trigger t_borra before delete on alumnos for each row
	begin
		delete from alumnos_replica where id = old.id;
	end $$
delimiter ;

Ejercicio 25:

Realizar dos triggers que completen la funcionalidad del trigger1

del apartado 9.3 de los materiales, mantener sincronizada la tabla

alumnos_replica, pero para las operaciones de modificación

(trigger ejercicio25a) y borrado (trigger ejercicio25b).

Un trigger es un programa almacenado que no se ejecuta por voluntad del usuario, si no que se ejecuta solo de forma automática cuando se realizan modificaciones con las sentencias: insert, update y delete.

Podemos elegir cuando se ha de ejecutar el trigger, si antes (before) de realizar la acción o después (after). El trigger solo funciona bajo la tabla que especifiquemos y cada tabla como máximo podrá tener 6 trigger, 2 por cada sentencia.

Notas de interes:

Muestra los triggers: show triggers;

Borra un trigger: drop trigger t_copia;

-- Primero se copia una tabla para hacer las pruebas:

create table alumnos_replica (select * from alumnos);

-- Triger 1: Se encarga de sincronizar las nuevas filas insertadas:

delimiter $$

create trigger t_copia before insert on alumnos for each row

begin

-- Disponemos de "new" para obtener el nuevo valor en insert y update

-- y de "old" para obtener el valor anterior en update y delete.

insert into alumnos_replica values (new.id, new.alumno);

end $$

delimiter;

-- Triger A: Mantiene las filas actualizadas:

delimiter $$

create trigger t_modifica before update on alumnos for each row

begin

update alumnos_replica set id = new.id, alumno = new.alumno where id = old.id;

end $$

delimiter ;

-- Triger B: Borra las filas

delimiter $$

create trigger t_borra before delete on alumnos for each row

begin

delete from alumnos_replica where id = old.id;

end $$

delimiter ;

1×23-24: Funciones

3 May 2012 giltesa Deja un comentario

/*
	Ejercicio 23:
	Desarrollar una función que devuelva un 1 si el número de departamento
	que se le pasa como argumento existe y devuelva 0 en caso contrario.	
*/

delimiter $$
drop function if exists 1x23 $$
create function 1x23(dep int) returns boolean
	begin
		if (select numde from departamentos where numde = dep) = dep then
			return true;
		else
			return false;
		end if;
	end $$
delimiter ;

-- select 1x23(112) as 'Resultado';

Ejercicio 23:

Desarrollar una función que devuelva un 1 si el número de departamento

que se le pasa como argumento existe y devuelva 0 en caso contrario.

delimiter $$

drop function if exists 1x23 $$

create function 1x23(dep int) returns boolean

begin

if (select numde from departamentos where numde = dep) = dep then

return true;

else

return false;

end if;

end $$

delimiter ;

-- select 1x23(112) as 'Resultado';

/*
	Ejercicio 24:
	Desarrollar una función que devuelva la suma total de salarios de los
	empleados del número de departamento que se le pasa como argumento.
	Si el Departamento no existe devolverá -1. Utiliza una llamada a la
	función del ejercicio anterior dentro del código.
*/

delimiter $$
drop function if exists 1x24 $$
create function 1x24(dep int) returns int
	begin
		if (select 1x23(dep)) then
			return (select sum(salario) from empleados where numde = dep);
		else
			return -1;
		end if;
	end $$
delimiter ;

-- select 1x24(112) as 'Total';

Ejercicio 24:

Desarrollar una función que devuelva la suma total de salarios de los

empleados del número de departamento que se le pasa como argumento.

Si el Departamento no existe devolverá -1. Utiliza una llamada a la

función del ejercicio anterior dentro del código.

delimiter $$

drop function if exists 1x24 $$

create function 1x24(dep int) returns int

begin

if (select 1x23(dep)) then

return (select sum(salario) from empleados where numde = dep);

else

return -1;

end if;

end $$

delimiter ;

-- select 1x24(112) as 'Total';

1×22: Manejador de errores y claves ajenas

27 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Tablas Centros/Departamentos/Empleados (script primeros_pasos.sql)

	Ejercicio 22:
	Realiza un procedimiento que reciba como argumentos los datos de un
	empleado (los 9 parámetros correspondientes a las 9 columnas) y los
	almacene en la tabla empleados. Además de los 4 manejadores de error
	contemplados en el ejercicio 21 anterior se deberá añadir otro que
	trate el error de que el departamento al que se le asigna al empleado
	(numde) debe existir previamente o ser nulo (integridad referencial).
*/

delimiter $$
drop procedure if exists 1x22 $$
create procedure 1x22( p_numde int, p_extel int, p_fecna date, p_fecin date, p_salario int, p_comision int, p_numhi int, p_nomem VARCHAR(18), out p_errorNum int, out p_errorText varchar(100))
modifies sql data
	begin
		-- DECLARACIONES:
		declare clave_repetida_error	condition for 1062;
		declare clave_nula_error		condition for 1048;
		declare tabla_inexistente_error	condition for 1146;
		declare clave_ajena_error		condition for 1452;

		declare continue handler for clave_repetida_error
			begin
				set p_errorNum  = 1062;
				set p_errorText = 'Clave duplicada';
			end;

		declare continue handler for clave_nula_error
			begin
				set p_errorNum  = 1048;
				set p_errorText = 'Clave nula';
			end;

		declare continue handler for tabla_inexistente_error
			begin
				set p_errorNum  = 1146;
				set p_errorText = 'Tabla inexistente';
			end;

		declare continue handler for clave_ajena_error
			begin
				set p_errorNum  = 1452;
				set p_errorText = 'El departamento no existe ni es nulo';
			end;

		declare continue handler for sqlexception
			begin
				set p_errorNum  = -1;
				set p_errorText = 'Ocurrió un error';
			end;

		-- INICIO OPERACIONES
		set p_errorNum = 0;
		select max(numem) into @max from empleados;

		insert into empleados values( @max+1, p_numde, p_extel, p_fecna, p_fecin, p_salario, p_comision, p_numhi, p_nomem );
		set @max := null;

		if p_errorNum = 0 then
		  set p_errorText = 'Alta de alumno realizada';
		end if;

	end $$
delimiter ;

-- call 1x22( 110, 22, '1988-04-21', '2012-04-26', 1300, 50, 3, 'Lacasa, Perez', @errorId, @errorText);
-- select @errorId as 'ID Error', @errorText as 'Descripcion';

Tablas Centros/Departamentos/Empleados (script primeros_pasos.sql)

Ejercicio 22:

Realiza un procedimiento que reciba como argumentos los datos de un

empleado (los 9 parámetros correspondientes a las 9 columnas) y los

almacene en la tabla empleados. Además de los 4 manejadores de error

contemplados en el ejercicio 21 anterior se deberá añadir otro que

trate el error de que el departamento al que se le asigna al empleado

(numde) debe existir previamente o ser nulo (integridad referencial).

delimiter $$

drop procedure if exists 1x22 $$

create procedure 1x22( p_numde int, p_extel int, p_fecna date, p_fecin date, p_salario int, p_comision int, p_numhi int, p_nomem VARCHAR(18), out p_errorNum int, out p_errorText varchar(100))

modifies sql data

begin

-- DECLARACIONES:

declare clave_repetida_error condition for 1062;

declare clave_nula_error condition for 1048;

declare tabla_inexistente_error condition for 1146;

declare clave_ajena_error condition for 1452;

declare continue handler for clave_repetida_error

begin

set p_errorNum = 1062;

set p_errorText = 'Clave duplicada';

end;

declare continue handler for clave_nula_error

begin

set p_errorNum = 1048;

set p_errorText = 'Clave nula';

end;

declare continue handler for tabla_inexistente_error

begin

set p_errorNum = 1146;

set p_errorText = 'Tabla inexistente';

end;

declare continue handler for clave_ajena_error

begin

set p_errorNum = 1452;

set p_errorText = 'El departamento no existe ni es nulo';

end;

declare continue handler for sqlexception

begin

set p_errorNum = -1;

set p_errorText = 'Ocurrió un error';

end;

-- INICIO OPERACIONES

set p_errorNum = 0;

select max(numem) into @max from empleados;

insert into empleados values( @max+1, p_numde, p_extel, p_fecna, p_fecin, p_salario, p_comision, p_numhi, p_nomem );

set @max := null;

if p_errorNum = 0 then

set p_errorText = 'Alta de alumno realizada';

end if;

end $$

delimiter ;

-- call 1x22( 110, 22, '1988-04-21', '2012-04-26', 1300, 50, 3, 'Lacasa, Perez', @errorId, @errorText);

-- select @errorId as 'ID Error', @errorText as 'Descripcion';

1×21: Manejador de errores

26 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	use dam1;
	tablas centros/departamentos/empleados (script primeros_pasos.sql)

	Ejercicio 21:
	Realiza un procedimiento a partir del procedimiento error8 del apartado 7.6
	de los materiales. deberás completarlo incluyendo un manejador de error con
	condición de error con nombre definido por el usuario de la misma manera que
	se ha hecho con los otros dos manejadores de ese tipo (clave nula y clave
	repetida) para tratar el error de tabla inexistente. (1146)
*/

delimiter $$
drop procedure if exists 1x21 $$
create procedure 1x21( id int, nom varchar(30), out errorNum int, out errorText varchar(100))
modifies sql data
	begin
		-- DECLARACIONES:
		-- Se declaran tres 'constantes' cada una con el error a tratar:
		declare clave_repetida_error condition for		1062;
		declare clave_nula_error condition for			1048;
		declare tabla_inexistente_error condition for	1146;

		-- Se declara que hacer cuando suceda alguno de los siguientes errores:
		declare continue handler for clave_repetida_error
			begin
				set errorNum	= 1062;
				set errorText	= 'Clave duplicada';
			end;

		declare continue handler for clave_nula_error
			begin
				set errorNum	= 1048;
				set errorText	= 'Clave nula';
			end;

		declare continue handler for tabla_inexistente_error
			begin
				set errorNum	= 1146;
				set errorText	= 'Tabla inexistente';
			end;

		declare continue handler for sqlexception
			begin
				set errorNum	= -1;
				set errorText	= 'Ocurrió un error';
			end;

		-- INICIO OPERACIONES
		-- Se establece error a 0 'false', si al realizar la insercion sucede alguno de los errores controlados
		-- el valor de errorNum y errorText cambiaran por el que indique el error...
		set errorNum = 0;
		insert into alumnos values(id,nom);

		-- ... en caso contrario se le da valor a errorText como Operacion correcta.
		if errorNum = 0 then
			set errorText= 'Alta de alumno realizada';
		end if;

	end $$
delimiter ;

-- call 1x21(6,'Pedro', @errorId, @errorText);
-- select @errorId as 'ID Error', @errorText as 'Descripcion';

use dam1;

tablas centros/departamentos/empleados (script primeros_pasos.sql)

Ejercicio 21:

Realiza un procedimiento a partir del procedimiento error8 del apartado 7.6

de los materiales. deberás completarlo incluyendo un manejador de error con

condición de error con nombre definido por el usuario de la misma manera que

se ha hecho con los otros dos manejadores de ese tipo (clave nula y clave

repetida) para tratar el error de tabla inexistente. (1146)

delimiter $$

drop procedure if exists 1x21 $$

create procedure 1x21( id int, nom varchar(30), out errorNum int, out errorText varchar(100))

modifies sql data

begin

-- DECLARACIONES:

-- Se declaran tres 'constantes' cada una con el error a tratar:

declare clave_repetida_error condition for 1062;

declare clave_nula_error condition for 1048;

declare tabla_inexistente_error condition for 1146;

-- Se declara que hacer cuando suceda alguno de los siguientes errores:

declare continue handler for clave_repetida_error

begin

set errorNum = 1062;

set errorText = 'Clave duplicada';

end;

declare continue handler for clave_nula_error

begin

set errorNum = 1048;

set errorText = 'Clave nula';

end;

declare continue handler for tabla_inexistente_error

begin

set errorNum = 1146;

set errorText = 'Tabla inexistente';

end;

declare continue handler for sqlexception

begin

set errorNum = -1;

set errorText = 'Ocurrió un error';

end;

-- INICIO OPERACIONES

-- Se establece error a 0 'false', si al realizar la insercion sucede alguno de los errores controlados

-- el valor de errorNum y errorText cambiaran por el que indique el error...

set errorNum = 0;

insert into alumnos values(id,nom);

-- ... en caso contrario se le da valor a errorText como Operacion correcta.

if errorNum = 0 then

set errorText= 'Alta de alumno realizada';

end if;

end $$

delimiter ;

-- call 1x21(6,'Pedro', @errorId, @errorText);

-- select @errorId as 'ID Error', @errorText as 'Descripcion';

1×20: Uso de transacciones

21 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	TABLAS empleados y departamentos (script cargatablas1.sql)
	
	Ejercicio 20:
	Escribir un procedimiento que suba el sueldo de todos los empleados
	que ganen menos que el salario medio de su oficio. La subida será
	del 50% de la diferencia entre el salario del empleado y la media de
	su oficio. La transacción no deberá quedarse a medias.
*/

delimiter $$
drop procedure if exists 1x20 $$
create procedure 1x20()
	begin
		declare id int;
		declare ofi1, ofi2 tinytext; 
		declare sal, med, aumento double;
		declare finCursor     boolean default false;
		declare transaccionOk boolean default true;

		-- Se guarda en un cursor el oficio y la media de sueldos de los empleados.
		-- Y en otro el id del empleado y su oficio:
		declare c_oficios cursor for
			select oficio, avg(salario) as media from empleados group by oficio order by oficio;

		declare c_empleados cursor for
			select emp_no, oficio, salario from empleados order by oficio;

		-- Si hay un error en la lectura el testigo se podra a true:
 		declare continue handler for not found set finCursor = true;


		-- Se comienza una transacción:
		start transaction;

		-- Se abre y se hace la primera lectura de los Cursores:
		open  c_oficios;
		open  c_empleados;
		fetch c_oficios   into ofi1, med;
		fetch c_empleados into id, ofi2, sal;

		
		while finCursor = false do 

			-- Como ambos Cursores tienen ordenados sus campos por oficio, se leeran tantos usuarios iguales al oficio actual
			-- cuando dejen de coincidir terminara el while2 y se leera un nuevo oficio de c_oficios para tratar los siguientes empleados con ese oficio.

			while ofi1 like ofi2  &&  finCursor = false do
				set aumento = if( sal < med, (med-sal)/2+sal, sal );
				update empleados set salario = aumento where emp_no = id;

				if truncate((select salario from empleados where emp_no = id),0) != truncate(aumento,0) then
					set transaccionOk = false;
				end if;

				fetch c_empleados into id, ofi2, sal; -- Se recoge un nuevo empleado.
			end while;
			

			fetch c_oficios into ofi1, med; -- Se recoge un nuevo oficio.
		end while;

		close c_oficios;
		close c_empleados;


		if transaccionOk = true then
			commit;
			select 'Salarios actualizados correctamente';
		else
			rollback;
			select 'Error en la actualizacion de los salarios';
		end if;

	end $$
delimiter ;

-- call 1x20;

TABLAS empleados y departamentos (script cargatablas1.sql)

Ejercicio 20:

Escribir un procedimiento que suba el sueldo de todos los empleados

que ganen menos que el salario medio de su oficio. La subida será

del 50% de la diferencia entre el salario del empleado y la media de

su oficio. La transacción no deberá quedarse a medias.

delimiter $$

drop procedure if exists 1x20 $$

create procedure 1x20()

begin

declare id int;

declare ofi1, ofi2 tinytext;

declare sal, med, aumento double;

declare finCursor boolean default false;

declare transaccionOk boolean default true;

-- Se guarda en un cursor el oficio y la media de sueldos de los empleados.

-- Y en otro el id del empleado y su oficio:

declare c_oficios cursor for

select oficio, avg(salario) as media from empleados group by oficio order by oficio;

declare c_empleados cursor for

select emp_no, oficio, salario from empleados order by oficio;

-- Si hay un error en la lectura el testigo se podra a true:

declare continue handler for not found set finCursor = true;

-- Se comienza una transacción:

start transaction;

-- Se abre y se hace la primera lectura de los Cursores:

open c_oficios;

open c_empleados;

fetch c_oficios into ofi1, med;

fetch c_empleados into id, ofi2, sal;

while finCursor = false do

-- Como ambos Cursores tienen ordenados sus campos por oficio, se leeran tantos usuarios iguales al oficio actual

-- cuando dejen de coincidir terminara el while2 y se leera un nuevo oficio de c_oficios para tratar los siguientes empleados con ese oficio.

while ofi1 like ofi2 && finCursor = false do

set aumento = if( sal < med, (med-sal)/2+sal, sal );

update empleados set salario = aumento where emp_no = id;

if truncate((select salario from empleados where emp_no = id),0) != truncate(aumento,0) then

set transaccionOk = false;

end if;

fetch c_empleados into id, ofi2, sal; -- Se recoge un nuevo empleado.

end while;

fetch c_oficios into ofi1, med; -- Se recoge un nuevo oficio.

end while;

close c_oficios;

close c_empleados;

if transaccionOk = true then

commit;

select 'Salarios actualizados correctamente';

else

rollback;

select 'Error en la actualizacion de los salarios';

end if;

end $$

delimiter ;

-- call 1x20;

1×19: Uso de Cursores

20 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Tablas Centros/Departamentos/Empleados (script primeros_pasos.sql)

	Ejercicio 19:
	Realiza un procedimiento que liste cada departamento junto con sus
	empleados de forma similar a cómo se ha hecho en el ejercicio
	cursor5 del apartado 6.2 del tema (en este último caso para centros
	junto con sus departamentos). Se listarán los datos de aquellos
	Departamentos cuyo número esté comprendido entre los dos números de
	departamento que se pasen como argumento al procedimiento.
	El formato y un ejemplo de ejecución (se han suprimido parte de la
	salida como los mensajes de filas devueltas):
*/
/*
	mysql> CALL ejercicio2_5(110,111);
	+-----------------------------------------+
	| DATOS DEPARTAMENTO                      |
	+-----------------------------------------+
	| Departamento: 110 - DIRECCION COMERCIAL |
	+-----------------------------------------+
	+----------------------------------------------------------------------+
	| DATOS EMPLEADO                                                       |
	+----------------------------------------------------------------------+
	| Empleado: PEREZ, MARCOS-Salario: 480-Comision: 50 -Total sueldo: 530 |
	+----------------------------------------------------------------------+
	+----------------------------------------------------------------------+
	| DATOS EMPLEADO                                                       |
	+----------------------------------------------------------------------+
	| Empleado: MORAN, CARMEN-Salario: 215-Comision: No -Total sueldo: 215 |
	+----------------------------------------------------------------------+
	+-----------------------------------------------------------------------+
	| DATOS EMPLEADO                                                        |
	+-----------------------------------------------------------------------+
	| Empleado: CAMPOS, ROMULO-Salario: 200-Comision: No -Total sueldo: 200 |
	+-----------------------------------------------------------------------+
	+----------------------------------+
	| TOTALES DEPARTAMENTO             |
	+----------------------------------+
	| EMPLEADOS: 3 - TOTAL SUELDO: 945 |
	+----------------------------------+

	+---------------------------------------+
	| DATOS DEPARTAMENTO                    |
	+---------------------------------------+
	| Departamento: 111 - SECTOR INDUSTRIAL |
	+---------------------------------------+
	+------------------------------------------------------------------------+
	| DATOS EMPLEADO                                                         |
	+------------------------------------------------------------------------+
	| Empleado: AGUIRRE, AUREO-Salario: 310-Comision: 110 -Total sueldo: 420 |
	+------------------------------------------------------------------------+
	............
	............
	+-----------------------------------+
	| TOTALES DEPARTAMENTO              |
	+-----------------------------------+
	| EMPLEADOS: 8 - TOTAL SUELDO: 2475 |
	+-----------------------------------+
*/

delimiter $$
drop procedure if exists 1x19 $$
create procedure 1x19( in inicio int, in fin int )
	begin

		declare finCursor boolean default false;
		declare cueEmp int default 0;
		declare totSue double default 0;
		declare id, dep int;
		declare nom, ape tinytext;
		declare sal, com, tot double;

		-- Se crean dos Cursores, cada uno de ellos guarda la informacion que se necesitara despues:
		declare c_departamentos cursor for
			select dep_no, dnombre from departamentos where dep_no >= inicio && dep_no <= fin order by dep_no;		

		declare c_empleados cursor for
			select dep_no, apellido, salario, if(comision is null, 0, comision) as comision, salario + if(comision is null, 0, comision) as total
			from empleados where dep_no >= inicio && dep_no <= fin order by dep_no;	

		-- Automaticamente si ocurre un error de lectura en el Cursor, finCursor pasara a valer true.
		declare continue handler for not found set finCursor = true;

		-- Se abren los cursores:
		open c_departamentos;
		open c_empleados;
			
		-- Se lee el primer departamento y empleado:
		fetch c_departamentos into id, nom;
		fetch c_empleados into dep, ape, sal, com, tot;

		-- Recorre los departamentos:
		while finCursor = false do

			-- Se imprime el departamento.
			select concat('Departamento: ', id, ' - ', nom) as 'DATOS DEPARTAMENTO';
			
			-- Si el departamento del empleado es el mismo que el del empleado se muestra y se lee un nuevo empleado:
			while id = dep  &&  finCursor = false do
				set totSue = totSue + tot; -- Suma sueldos.
				select concat( 'Empleado: ', ape, ', Salario: ', sal, ', Comision: ', com, ', Total sueldo: ', tot ) as 'DATOS EMPLEADO';
				fetch c_empleados into dep, ape, sal, com, tot;
			end while;
			
			fetch c_departamentos into id, nom; -- Se le un nuevo departamento.
			set cueEmp = cueEmp + 1; --Cuenta empleados.

		end while;

		-- Se cierran los cursores:
		close c_empleados;
		close c_departamentos;

		-- Se muestran los contadores:
		select concat( 'EMPLEADOS: ', cueEmp, ' - TOTAL SUELDO: ', truncate(totSue, 3) ) as 'TOTALES DEPARTAMENTO ';

	end $$
delimiter ;

-- call 1x19( 20, 40 );

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

Tablas Centros/Departamentos/Empleados (script primeros_pasos.sql)

Ejercicio 19:

Realiza un procedimiento que liste cada departamento junto con sus

empleados de forma similar a cómo se ha hecho en el ejercicio

cursor5 del apartado 6.2 del tema (en este último caso para centros

junto con sus departamentos). Se listarán los datos de aquellos

Departamentos cuyo número esté comprendido entre los dos números de

departamento que se pasen como argumento al procedimiento.

El formato y un ejemplo de ejecución (se han suprimido parte de la

salida como los mensajes de filas devueltas):

mysql> CALL ejercicio2_5(110,111);

+-----------------------------------------+

| DATOS DEPARTAMENTO |

+-----------------------------------------+

| Departamento: 110 - DIRECCION COMERCIAL |

+-----------------------------------------+

+----------------------------------------------------------------------+

| DATOS EMPLEADO |

+----------------------------------------------------------------------+

| Empleado: PEREZ, MARCOS-Salario: 480-Comision: 50 -Total sueldo: 530 |

+----------------------------------------------------------------------+

| DATOS EMPLEADO |

+----------------------------------------------------------------------+

| Empleado: MORAN, CARMEN-Salario: 215-Comision: No -Total sueldo: 215 |

+----------------------------------------------------------------------+

+-----------------------------------------------------------------------+

| DATOS EMPLEADO |

+-----------------------------------------------------------------------+

| Empleado: CAMPOS, ROMULO-Salario: 200-Comision: No -Total sueldo: 200 |

+-----------------------------------------------------------------------+

+----------------------------------+

| TOTALES DEPARTAMENTO |

+----------------------------------+

| EMPLEADOS: 3 - TOTAL SUELDO: 945 |

+----------------------------------+

+---------------------------------------+

| DATOS DEPARTAMENTO |

+---------------------------------------+

| Departamento: 111 - SECTOR INDUSTRIAL |

+---------------------------------------+

+------------------------------------------------------------------------+

| DATOS EMPLEADO |

+------------------------------------------------------------------------+

| Empleado: AGUIRRE, AUREO-Salario: 310-Comision: 110 -Total sueldo: 420 |

+------------------------------------------------------------------------+

............

+-----------------------------------+

| TOTALES DEPARTAMENTO |

+-----------------------------------+

| EMPLEADOS: 8 - TOTAL SUELDO: 2475 |

+-----------------------------------+

delimiter $$

drop procedure if exists 1x19 $$

create procedure 1x19( in inicio int, in fin int )

begin

declare finCursor boolean default false;

declare cueEmp int default 0;

declare totSue double default 0;

declare id, dep int;

declare nom, ape tinytext;

declare sal, com, tot double;

-- Se crean dos Cursores, cada uno de ellos guarda la informacion que se necesitara despues:

declare c_departamentos cursor for

select dep_no, dnombre from departamentos where dep_no >= inicio && dep_no <= fin order by dep_no;

declare c_empleados cursor for

select dep_no, apellido, salario, if(comision is null, 0, comision) as comision, salario + if(comision is null, 0, comision) as total

from empleados where dep_no >= inicio && dep_no <= fin order by dep_no;

-- Automaticamente si ocurre un error de lectura en el Cursor, finCursor pasara a valer true.

declare continue handler for not found set finCursor = true;

-- Se abren los cursores:

open c_departamentos;

open c_empleados;

-- Se lee el primer departamento y empleado:

fetch c_departamentos into id, nom;

fetch c_empleados into dep, ape, sal, com, tot;

-- Recorre los departamentos:

while finCursor = false do

-- Se imprime el departamento.

select concat('Departamento: ', id, ' - ', nom) as 'DATOS DEPARTAMENTO';

-- Si el departamento del empleado es el mismo que el del empleado se muestra y se lee un nuevo empleado:

while id = dep && finCursor = false do

set totSue = totSue + tot; -- Suma sueldos.

select concat( 'Empleado: ', ape, ', Salario: ', sal, ', Comision: ', com, ', Total sueldo: ', tot ) as 'DATOS EMPLEADO';

fetch c_empleados into dep, ape, sal, com, tot;

end while;

fetch c_departamentos into id, nom; -- Se le un nuevo departamento.

set cueEmp = cueEmp + 1; --Cuenta empleados.

end while;

-- Se cierran los cursores:

close c_empleados;

close c_departamentos;

-- Se muestran los contadores:

select concat( 'EMPLEADOS: ', cueEmp, ' - TOTAL SUELDO: ', truncate(totSue, 3) ) as 'TOTALES DEPARTAMENTO ';

end $$

delimiter ;

-- call 1x19( 20, 40 );

1×18: Gestión de errores

19 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 18:
	Escribir un procedimiento que reciba todos los datos de un nuevo
	empleado y procese la transacción de alta gestionando posibles errores.
*/

delimiter $$
drop procedure if exists 1x18 $$
create procedure 1x18( ape tinytext, ofi tinytext, dir int, sal double, com int, dep int )
	begin

		declare correcto boolean default true;
		declare error varchar(100) default 'Errores en: ';


		-- El apellido, en este caso..., no puede estar repetido, no puede ser nulo y tampoco vacio:
		if (select apellido from empleados where apellido like ape) = ape  ||  ape is null  ||  ape like '' then
			set error = concat(error,'apellido, ');
			set correcto = false;
		end if;

		-- El oficio no puede ser nulo ni vacio:
		if ofi is null  ||  ofi like '' then
			set error = concat(error,'oficio, ');
			set correcto = false;
		end if;

		-- Se debe de tener un director o no nulo:
		if dir not in( select director from empleados where director = dir ) then
			set error = concat(error,'director, ');
			set correcto = false;
		end if;

		-- El salario no puede ser negativo ni cero ni nulo:
		if sal <= 0 || sal is null then
			set error = concat(error,'salario, ');
			set correcto = false;
		end if;

		-- Si la comision es negativo:
		if com < 0 then
			set error = concat(error,'complemento, ');
			set correcto = false;
		end if;

		-- El departamento debe de existir:
		if dep not in (select dep_no from departamentos) then
			set error = concat(error,'departamento.');
			set correcto = false;
		end if;

		-- Si todo es correcto se inserta el empleado, si no se muestra el resultado:
		if correcto = true then
			select max(emp_no)+1 into @max from empleados;
			insert into empleados values( @max, ape, ofi, dir, sysdate(), sal, com, dep );
			set @max = null;
		else
			select error as 'Resultado';
		end if;

	end $$
delimiter ;

-- call 1x18( 'ZARZA', 'VENDEDOR', 7839, 1400, 75, 30 );

Ejercicio 18:

Escribir un procedimiento que reciba todos los datos de un nuevo

empleado y procese la transacción de alta gestionando posibles errores.

delimiter $$

drop procedure if exists 1x18 $$

create procedure 1x18( ape tinytext, ofi tinytext, dir int, sal double, com int, dep int )

begin

declare correcto boolean default true;

declare error varchar(100) default 'Errores en: ';

-- El apellido, en este caso..., no puede estar repetido, no puede ser nulo y tampoco vacio:

if (select apellido from empleados where apellido like ape) = ape || ape is null || ape like '' then

set error = concat(error,'apellido, ');

set correcto = false;

end if;

-- El oficio no puede ser nulo ni vacio:

if ofi is null || ofi like '' then

set error = concat(error,'oficio, ');

set correcto = false;

end if;

-- Se debe de tener un director o no nulo:

if dir not in( select director from empleados where director = dir ) then

set error = concat(error,'director, ');

set correcto = false;

end if;

-- El salario no puede ser negativo ni cero ni nulo:

if sal <= 0 || sal is null then

set error = concat(error,'salario, ');

set correcto = false;

end if;

-- Si la comision es negativo:

if com < 0 then

set error = concat(error,'complemento, ');

set correcto = false;

end if;

-- El departamento debe de existir:

if dep not in (select dep_no from departamentos) then

set error = concat(error,'departamento.');

set correcto = false;

end if;

-- Si todo es correcto se inserta el empleado, si no se muestra el resultado:

if correcto = true then

select max(emp_no)+1 into @max from empleados;

insert into empleados values( @max, ape, ofi, dir, sysdate(), sal, com, dep );

set @max = null;

else

select error as 'Resultado';

end if;

end $$

delimiter ;

-- call 1x18( 'ZARZA', 'VENDEDOR', 7839, 1400, 75, 30 );