1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
/* 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'; |
Categoría: Programación
1×21: Manejador de errores
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
/* 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'; |
9×01: Torres de Hanoi
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
/** * Proyecto: 9x01-Torres de Hanoi * Fichero: Torre.java * Fecha: 25/04/2012 */ package torresDeHanoi; public class Torre { // PROPIEDADES private int ultPosLibre; // Indica la primera posicion libre de la tabla que almacena los Discos en la Torre: private Disco tablaDiscos[]; // Tabla de Discos que almacena N Discos. // CONSTRUCTOR /** * <p>El constructor establece el numero de Discos de la Torre.<br> * -- Si el numero de discos recibido es igual a la capacidad que tiene la Torre, se instanciaran los Discos para esa torre.<br> * -- En caso contrario se dejan a null</p> * * @param capDiscos <i>Indica el numero de Discos que debera de poder guardar la Torre.</i> * @param numDiscos <i>Indica el numero de Discos inicial que tendra la torre. El valor debera de ser de 0 o N Discos.</i> */ public Torre( int capDiscos, int numDiscos ) { tablaDiscos = new Disco[capDiscos]; ultPosLibre = numDiscos; if( numDiscos == capDiscos ) { int tamDisco = 1; for( int i = numDiscos - 1; i >= 0; i-- ) { tablaDiscos[i] = new Disco(tamDisco); tamDisco += 2; } } }; // METODOS /** * <p>Inserta un Disco en la posicion libre que indica la Torre.</p> * * @param d <i>El Disco a insertar en al Torre.</i> */ public void ponerDisco( Disco d ) { tablaDiscos[ultPosLibre] = d; ultPosLibre++; }; /** * <p>Extrae el Disco que indica la posicion libre -1 de la Torre.<br> * -- Despues de extraerlo se borra poniendolo a null</p> * * @return <i>Devuelve el Disco mas arriba de la Torre.</i> */ public Disco quitarDisco() { ultPosLibre--; Disco temp = tablaDiscos[ultPosLibre]; tablaDiscos[ultPosLibre] = null; return temp; }; /** * <p>Devuelve el tamaño del disco indicado.</p> * * @param com <i>El numero de Disco del cual se quiere su tamaño.</i> * @return <i>Se devuelve 0 si el Disco no existe en la posicion indicada, o el tamaño que tanga el disco si si existiese.</i> */ public int getTamanioDisco( int com ) { if( tablaDiscos[com] == null ) return 0; else return tablaDiscos[com].getTamanio(); } }; |
1×20: Uso de transacciones
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
/* 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
/* 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
/* 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* 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); |