Estructuras de control
1.- Procedimiento que recibe 2 números y muestra un mensaje indicando el de mayor valor o son
iguales.
CREATE DATABASE rutinas; -- base de datos para los ejemplos ilustrativos
USE rutinas;
DELIMITER //
CREATE PROCEDURE numero_mayor(IN num1 INTEGER, in num2 INTEGER)
BEGIN
IF num1 >num2 THEN
SELECT 'El mayor es: ', num1;
ELSEIF num2>num1 THEN
SELECT 'El mayor es: ', num2;
ELSE
SELECT 'Los dos números son iguales ';
END IF;
END//
DELIMITER ;
-- comprobación
CALL numero_mayor(4,5);
CALL numero_mayor(5,5);
2.- Realizar una función que reciba una fecha y devuelva el nombre del día de la semana que le
corresponde.
DROP FUNCTION IF EXISTS diasemana;
DELIMITER |
CREATE FUNCTION DiaSemana(d DATE)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE n INT;
SET n=DAYOFWEEK(d);
IF n=1 THEN RETURN 'Domingo';
ELSEIF n=2 THEN RETURN 'Lunes';
ELSEIF n=4 THEN RETURN 'Miercoles';
ELSEIF n=5 THEN RETURN 'Jueves';
ELSEIF n=6 THEN RETURN 'Viernes';
ELSE RETURN 'Sabado';
END IF;
END |
DELIMITER ;
-- comprobación
set @dia = DiaSemana('2011/11/16');
select @dia;
Estructuras de control
3.- Realizar una función que devuelve la calificación (texto) correspondiente a una calificación numérica
con decimales. Deben tratarse errores.
USE rutinas;
DROP FUNCTION IF EXISTS Calificacion;
DELIMITER |
CREATE FUNCTION Calificacion (c FLOAT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE Ctexto VARCHAR(20);
CASE
WHEN c>=0 AND c<5 THEN SET Ctexto='Suspenso';
WHEN c>=5 AND c<6 THEN SET Ctexto='Suficiente';
WHEN c>=6 AND c<7 THEN SET Ctexto='Bien';
WHEN c>=7 AND c<9 THEN SET Ctexto='Notable';
WHEN c>=9 AND c<=10 THEN SET Ctexto='Sobresaliente';
ELSE SET Ctexto='Calificación errónea';
END CASE;
RETURN Ctexto;
END|
DELIMITER ;
-- comprobación
SELECT Calificacion(7.5);
SELECT Calificacion(11);
4.- En la base de datos rutinas creamos un procedimiento que recibe un número entero y muestra los
números anteriores hasta llegar al uno. (El número debe ser mayor que cero).
USE rutinas;
DELIMITER //
CREATE PROCEDURE pa_numeros_menores_repeat(IN num INT)
BEGIN
IF num <= 0 THEN
SELECT 'El valor introducido debe ser positivo';
ELSE
REPEAT -- REPITE las siguientes sentencias
SELECT num, ' ';
SET num = num-1;
UNTIL num<1 -- hasta que la condición es cierta
END REPEAT;
END IF;
END //
DELIMITER ;
-- llamada
CALL pa_numeros_menores_repeat(0);
CALL pa_numeros_menores_repeat(10);
Estructuras de control
5.- Realizar una función que recibe una cadena de texto, cadeA, y una letra para devolver una cadena
que sustituye con caracteres subrayados todos los de cadeA excepto los que son iguales a la letra
pasada.
USE rutinas;
DELIMITER //
CREATE FUNCTION palabraOculta(cadeA VARCHAR(20), letra CHAR(1))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE oculta VARCHAR(20);
DECLARE c VARCHAR(20);
DECLARE pos INT DEFAULT 1;
SET oculta='';
REPEAT
SET c=substring(cadeA, pos, 1);
IF c= letra THEN
SET oculta=concat(oculta,letra);
ELSE
SET oculta=concat(oculta, '_');
END IF;
SET pos=pos+1;
UNTIL pos > char_length(cadeA)
END REPEAT;
RETURN oculta;
END//
DELIMITER ;
-- comprobación
SELECT palabraOculta('Anaconda', 'a');
6.- En la base de datos rutinascreamos un procedimiento que recibe un número entero y muestra los
números anteriores hasta llegar al uno. (El número debe ser mayor que cero).
USE rutinas;
DELIMITER //
CREATE PROCEDURE pa_numeros_menores_while(IN num INT)
BEGIN
IF num <= 0 THEN
SELECT 'El valor introducido debe ser positivo';
ELSE -- n > 0
WHILE num >= 1 DO -- Mientras la condición es cierta HAZ las
siguientes sentencias
SELECT num, ' ';
SET num = num-1;
END WHILE;
END IF;
END //
DELIMITER ;
-- llamada
CALL pa_numeros_menores_while(0);
CALL pa_numeros_menores_while(10);
Estructuras de control
7. Realizar un procedimiento que carga en una tabla, FECHAS, con una sola columna F de tipo DATE,
las fechas en la que no se han realizado reparaciones desde la fecha pasada hasta la fecha actual.
- En primer lugar creamos la tabla Fechas en la base de datos talleresfaber:
USE talleresfaber;
CREATE TABLE Fechas (Fecha1 DATE);
-Creamos el procedimiento:
DELIMITER //
CREATE PROCEDURE malasFECHAS( IN dia DATE)
BEGIN
DECLARE fi DATE;
DECLARE n INT;
IF dia>curdate() THEN
SELECT 'Fecha indicada es incorrecta';
ELSE
SET fi=dia;
WHILE fi<curdate() DO
SELECT count(*) INTO n
FROM REPARACIONES
WHERE FechaEntrada=fi;
IF n=0 THEN
INSERT INTO fechas values(fi);
END IF;
SET fi= adddate(fi, INTERVAL 1 DAY);
END WHILE;
END IF;
END//
DELIMITER ;
-- llamada
CALL malasFECHAS('2019-03-16');
SELECT * FROM Fechas;
8. Crear un procedimiento que añada una línea en la tabla Incluyen para añadir un nuevo recambio en
una reparación, y que posteriormente actualice el stock de recambios disponible. Comprobar antes de
añadir la fila que haya unidades suficientes en stock de ese recambio.
Comprobar el procedimiento con los siguientes datos: IdRecambio= 'BJ_111_666', IdReparacion=2,
Unidades=1
DELIMITER //
CREATE PROCEDURE InsertarIncluyen (IN IdRec char(10), IN IdRepar INT , IN Unid
SMALLINT)
BEGIN
DECLARE Num INT;
SELECT Stock INTO Num
FROM RECAMBIOS
WHERE IdRecambio = IdRec;
IF Num >= Unid THEN
INSERT INTO Incluyen VALUES (IdRec, IdRepar, Unid);
UPDATE RECAMBIOS
SET Stock=Stock-Unid
WHERE IdRecambio = IdRec;
END IF;
END//
DELIMITER ;
-- llamada
CALL InsertarIncluyen('BJ_111_666',2,1);
Estructuras de control
9. Procedimiento en la base de datos rutinas que recibe un número entero y muestra los números
anteriores hasta llegar al 1. (Controla que el número debe ser mayor que cero).
USE rutinas;
DELIMITER //
CREATE PROCEDURE pa_numeros_menores_loop(IN num INT)
BEGIN
IF num <= 0 THEN
SELECT 'El valor introducido debe ser positivo';
ELSE
b1: LOOP -- REPITE las siguientes sentencias
SELECT num, ' ';
SET num = num-1;
IF num < 1 THEN -- si la condición es cierta
LEAVE b1; -- salir del bucle etiquetado b1
ELSE
ITERATE b1; -- comenzar el bucle
END IF;
END LOOP b1; -- punto final del bucle
END IF;
END //
DELIMITER ;
-- llamada y comprobación
CALL pa_numeros_menores_loop(0);
CALL pa_numeros_menores_loop(10);
10.- Ejemplo de utilización de las sentencias LEAVE e ITERATE dentro de un bucle LOOP.
DELIMITER //
CREATE PROCEDURE BucleLoop (P1 INT, OUT P2 INT, OUT P3 INT)
BEGIN
SET P2=0;
Etiqueta1:LOOP
SET P1=P1+1;
SET P2=P2+1;
IF P1<10 THEN
ITERATE Etiqueta1;
END IF;
LEAVE Etiqueta1;
END LOOP Etiqueta1;
SET P3=P1;
END//
DELIMITER ;
-- comprobación
CALL BucleLoop(7,@n, @m);
SELECT @n, @m; -- los valores mostrados son 3 y 10
Estructuras de control
Manipuladores de error:
Debemos tener activada la opción:
11.- En la base de datos talleresfaber, supongamos que tenemos el siguiente procedimiento:
DELIMITER |
CREATE PROCEDURE Manipuladores()
BEGIN
BEGIN
INSERT INTO VEHICULOS (Matricula, Marca, Modelo) VALUES
('1234BMY','audi','A6');
SELECT * FROM CLIENTES;
SELECT COUNT(*) FROM CLIENTES;
END;
SELECT * FROM REPARACIONES;
END |
DELIMITER ;
Suponiendo que hacemos la llamada al procedimiento y la matrícula a insertar ya existe en la tabla, no
se ejecutará ninguna de las sentencias que siguen a la sentencia INSERT que da error.
CALL Manipuladores()
ERROR 1062 (23000): Duplicate entry '1234BMY' for key 1
Sabiendo que un error de inserción por clave duplicada da el valor de estado '23000' y el código de error
1062, modificaríamos el procedimiento anterior para que controlase ese error de una forma similar a la
siguiente:
CREATE DEFINER=`java`@`%` PROCEDURE `Manipuladores`()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'Se ha producido un
error';
BEGIN
INSERT INTO VEHICULOS (Matricula,Marca, Modelo)
VALUES ('2233 ABC','Ford','Mondeo');
SELECT * FROM CLIENTES;
SELECT COUNT(*) FROM CLIENTES;
END;
SELECT * FROM REPARACIONES;
END
Ahora, al ejecutar el procedimiento, se ejecuta la sentencia asociada al manipulador cuando se produce
el error, es decir, se escribe el mensaje se ha producido un error y se continua (tipo CONTINUE) con la
sentencia que hay a continuación de la sentencia INSERT que produjo error. Si el manipulador fuese
tipo EXIT se ejecutaría la sentencia asociada al manipulador, pero no las siguientes y se iría al final del
procedimiento.
La declaración del manipulador podríamos haberla hecho usando el código de error y no el valor de
estado de la sentencia. En su lugar podríamos haber puesto:
DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Se ha producido un error';
Estructuras de control
12.- Partiendo del ejercicio 7 procedimientos (ejercicios de clase), añade un Handler que evite que las
consultas se ejecuten cuando la matrícula que se reciba no exista en TalleresFaber:
CREATE PROCEDURE `NumReparaciones2`(IN Matri varchar(8), OUT NumRep INT)
BEGIN
DECLARE m VARCHAR(8);
DECLARE EXIT HANDLER FOR SQLSTATE '02000'
SELECT 'La matricula no existe';
SELECT MATRICULA into m FROM VEHICULOS
WHERE Matricula=Matri;
BEGIN
SELECT *
FROM VEHICULOS
WHERE Matricula=Matri;
SELECT COUNT(Matricula) INTO NumRep
FROM REPARACIONES
WHERE Matricula=Matri;
END ;
END
-- comprobación con matrícula que no existe
Call NumReparaciones('2020 DEF', @NumRep);
SELECT @NumRep;
