DAM archivos - Página 5 de 18 - el blog de giltesa

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 );

1×17: Insertar tuplas incrementalmente

18 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 17:
	Desarrollar un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones:
		- Se pasará al procedimiento el nombre del departamento y la localidad.
		- El procedimiento insertará la fila nueva asignando como número de
		  departamento la decena siguiente al número mayor de la tabla. 

*/

delimiter $$
drop procedure if exists 1x17 $$
create procedure 1x17( in nom tinytext, in loc tinytext )
	begin

		select max(dep_no)+10 into @max from departamentos;
		insert into departamentos values ( @max, nom, loc );
		set @max = null;

	end $$
delimiter ;


-- call 1x17( 'MARKETING', 'PAMPLONA' );

Ejercicio 17:

Desarrollar un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones:

- Se pasará al procedimiento el nombre del departamento y la localidad.

- El procedimiento insertará la fila nueva asignando como número de

departamento la decena siguiente al número mayor de la tabla.

delimiter $$

drop procedure if exists 1x17 $$

create procedure 1x17( in nom tinytext, in loc tinytext )

begin

select max(dep_no)+10 into @max from departamentos;

insert into departamentos values ( @max, nom, loc );

set @max = null;

end $$

delimiter ;

-- call 1x17( 'MARKETING', 'PAMPLONA' );

1×16: Muestra un determinado número de tuplas

18 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 16:
	Escribir un programa que visualice el apellido y el salario de los
	cinco empleados que tienen el salario más alto.
*/

delimiter $$
drop procedure if exists 1x16 $$
create procedure 1x16(in num int)
	begin

		select apellido, salario
		from empleados 
    	order by salario desc limit 0, num;

	end $$
delimiter ;

-- call 1x16(5);

Ejercicio 16:

Escribir un programa que visualice el apellido y el salario de los

cinco empleados que tienen el salario más alto.

delimiter $$

drop procedure if exists 1x16 $$

create procedure 1x16(in num int)

begin

select apellido, salario

from empleados

order by salario desc limit 0, num;

end $$

delimiter ;

-- call 1x16(5);

1×15: Visualizar búsqueda y total de coincidencias

18 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 15:
	Escribir un procedimiento que reciba una cadena y visualice el apellido
	y el número de empleado de todos los empleados cuyo apellido contenga la
	cadena especificada. Al finalizar visualizar el número de empleados
	mostrados.
*/

delimiter $$
drop procedure if exists 1x15 $$
create procedure 1x15(in cad tinytext)
	begin
		/*
		-- Mostrando las coincidencias enumerando las filas:
		set @col = 0;
		set @cadena = concat( 'select (@col := @col +1) as "Fila", e.emp_no as "Num. empleado", e.apellido as "Apellido" from empleados e where e.apellido like "%', cad ,'%"' );

		prepare consulta from @cadena;
		execute consulta;

		deallocate prepare consulta;
		set @cadena = null;
		set @col = null;
		*/

		-- Mostrando las coincidencias y el total en dos tablas (y sin necesidad de preparar la sentencia):
		select e.emp_no as "Num. empleado", e.apellido as "Apellido" from empleados e where e.apellido like concat("%", cad ,"%");
		select count(*) as 'Numero de empleados' from empleados e where e.apellido like concat("%", cad ,"%");

	end $$
delimiter ;

-- call 1x15('MA');

Ejercicio 15:

Escribir un procedimiento que reciba una cadena y visualice el apellido

y el número de empleado de todos los empleados cuyo apellido contenga la

cadena especificada. Al finalizar visualizar el número de empleados

mostrados.

delimiter $$

drop procedure if exists 1x15 $$

create procedure 1x15(in cad tinytext)

begin

-- Mostrando las coincidencias enumerando las filas:

set @col = 0;

set @cadena = concat( 'select (@col := @col +1) as "Fila", e.emp_no as "Num. empleado", e.apellido as "Apellido" from empleados e where e.apellido like "%', cad ,'%"' );

prepare consulta from @cadena;

execute consulta;

deallocate prepare consulta;

set @cadena = null;

set @col = null;

-- Mostrando las coincidencias y el total en dos tablas (y sin necesidad de preparar la sentencia):

select e.emp_no as "Num. empleado", e.apellido as "Apellido" from empleados e where e.apellido like concat("%", cad ,"%");

select count(*) as 'Numero de empleados' from empleados e where e.apellido like concat("%", cad ,"%");

end $$

delimiter ;

-- call 1x15('MA');

1×14: Consultas a varias tablas con Group by

18 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 14:
	Codificar un procedimiento que muestre el nombre de cada departamento
	y el número de empleados que tiene.
*/

delimiter $$
drop procedure if exists 1x14 $$
create procedure 1x14()
	begin

		select d.dnombre as 'Nombre departamento', count(e.dep_no) as 'Numero de empleados'
		from departamentos d, empleados e
		where d.dep_no = e.dep_no
		group by d.dep_no;

	end $$
delimiter ;


-- call 1x14();

Ejercicio 14:

Codificar un procedimiento que muestre el nombre de cada departamento

y el número de empleados que tiene.

delimiter $$

drop procedure if exists 1x14 $$

create procedure 1x14()

begin

select d.dnombre as 'Nombre departamento', count(e.dep_no) as 'Numero de empleados'

from departamentos d, empleados e

where d.dep_no = e.dep_no

group by d.dep_no;

end $$

delimiter ;

-- call 1x14();

1×13: Mostrar información por orden descendente

17 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 13:
	TABLAS empleados y departamentos (script cargatablas1.sql)
	
	Desarrollar un procedimiento que visualice el apellido y la fecha
	de alta de todos los empleados ordenados por apellido.
*/

delimiter $$
drop procedure if exists 1x13 $$
create procedure 1x13()
	begin
		select apellido as 'Apellido', fecha_alta as 'Fecha de alta' from empleados order by apellido asc;
	end $$
delimiter ;

-- call 1x13();

Ejercicio 13:

TABLAS empleados y departamentos (script cargatablas1.sql)

Desarrollar un procedimiento que visualice el apellido y la fecha

de alta de todos los empleados ordenados por apellido.

delimiter $$

drop procedure if exists 1x13 $$

create procedure 1x13()

begin

select apellido as 'Apellido', fecha_alta as 'Fecha de alta' from empleados order by apellido asc;

end $$

delimiter ;

-- call 1x13();

1×12: Sentencias preparadas, borrar columna de una tabla

17 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 12:
	Realizar un procedimiento que acepte dos cadenas: El nombre de una tabla
	y el de una columna y elimine la columna de la tabla.  No es necesario que
	el procedimiento controle antes si existe la tabla o columna.
*/

delimiter $$
drop procedure if exists 1x12 $$
create procedure 1x12( in tabla tinytext, in columna tinytext )
	begin

		-- Se concatenan las diferentes partes de la consulta:
		set @cadena = concat('alter table ', tabla, ' drop ', columna );

		prepare consulta from @cadena;	-- Se prepara la consulta.
		execute consulta;               -- Se ejecuta.
		deallocate prepare consulta;    -- Se borra la consulta preparada.
		set @cadena = null;             -- Se borra la cadena.

	end $$
delimiter ;

-- call 1x12('empleados','nomem');

Ejercicio 12:

Realizar un procedimiento que acepte dos cadenas: El nombre de una tabla

y el de una columna y elimine la columna de la tabla. No es necesario que

el procedimiento controle antes si existe la tabla o columna.

delimiter $$

drop procedure if exists 1x12 $$

create procedure 1x12( in tabla tinytext, in columna tinytext )

begin

-- Se concatenan las diferentes partes de la consulta:

set @cadena = concat('alter table ', tabla, ' drop ', columna );

prepare consulta from @cadena; -- Se prepara la consulta.

execute consulta; -- Se ejecuta.

deallocate prepare consulta; -- Se borra la consulta preparada.

set @cadena = null; -- Se borra la cadena.

end $$

delimiter ;

-- call 1x12('empleados','nomem');

1×11: Devuelve parámetros por valor

17 Abr 20121 mayo 2012 giltesa Deja un comentario

/*
	Ejercicio 11:
	Realizar un procedimiento que acepte un departamento y devuelva mediante dos
	parámetros de tipo OUT su  número de director (director) y presupuesto.
*/

delimiter $$
drop procedure if exists 1x11 $$
create procedure 1x11( in elDepartamento tinytext, out numDirector int, out presupuesto int )
	begin
		-- Dos formas de guardar la consulta, la primera con into y la segunda con set:
		select direc into numDirector from departamentos where nomde like elDepartamento;
		set presupuesto = ( select presu from departamentos where nomde like elDepartamento );
	end $$
delimiter ;


-- call 1x11( 'FINANZAS', @numDirector, @presupuesto );
-- select @numDirector as 'ID Director', @presupuesto as 'Presupuesto';

Ejercicio 11:

Realizar un procedimiento que acepte un departamento y devuelva mediante dos

parámetros de tipo OUT su número de director (director) y presupuesto.

delimiter $$

drop procedure if exists 1x11 $$

create procedure 1x11( in elDepartamento tinytext, out numDirector int, out presupuesto int )

begin

-- Dos formas de guardar la consulta, la primera con into y la segunda con set:

select direc into numDirector from departamentos where nomde like elDepartamento;

set presupuesto = ( select presu from departamentos where nomde like elDepartamento );

end $$

delimiter ;

-- call 1x11( 'FINANZAS', @numDirector, @presupuesto );

-- select @numDirector as 'ID Director', @presupuesto as 'Presupuesto';