Use sakila2;
-- Crear un SP que permita incrementar en 2€ el coste de penalización
(film.replacement_cost) de las peliculas usando cursores
-- (Modificacion por qu edecia que era chorra)
-- Queremos visualizar por pantalla el título, el precio original y el precio final del cambio.
DELIMITER $$ -- este no me funciona
CREATE PROCEDURE incremento_penal()
BEGIN
DECLARE fin_Cursor BOOLEAN DEFAULT FALSE;
DECLARE idPeli SMALLINT UNSIGNED;
DECLARE tituloPeli VARCHAR(128);
DECLARE costePeli DECIMAL(5,2); -- como maximo 5 digitos con dos detras de la
coma Ej: (123.45)
DECLARE cursorPeli CURSOR FOR SELECT film_id, title, replacement_cost
FROM film WHERE replacement_cost > 29;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET fin_Cursor = TRUE;
END;
OPEN cursorPeli;
WHILE (finCursor = FALSE)
DO
FETCH cursorPeli INTO idPeli, tituloPeli,costePeli;
IF(finCursor = FALSE) THEN
-- CON CADA REGISTRO LLEVARÉ A CABO LAS ACCIONES QUE
CORRESPONDAN
UPDATE film SET replacement_cost = costePeli + 2 WHERE film_id = idPeli;
SELECT tituloPeli AS 'Titulo', costePeli AS 'coste original', replacement_cost AS
'coste actualizado' FROM film;
END IF;
END WHILE;
END$$
DELIMITER ;
DROP PROCEDURE incremento_penal;
call incremento_penal;
USE sakila2;
DELIMITER $$ -- si funciona
CREATE PROCEDURE incremento_penal()
BEGIN
DECLARE fin_Cursor BOOLEAN DEFAULT FALSE;
DECLARE idPeli SMALLINT UNSIGNED;
DECLARE tituloPeli VARCHAR(128);
DECLARE costePeli DECIMAL(5,2);
DECLARE cursorPeli CURSOR FOR SELECT film_id, title, replacement_cost FROM film
WHERE replacement_cost > 29;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET fin_Cursor = TRUE;
END;
OPEN cursorPeli;
FETCH cursorPeli INTO idPeli, tituloPeli, costePeli; -- Se necesita un FETCH inicial para
iniciar el bucle WHILE
WHILE (fin_Cursor = FALSE) DO
-- CON CADA REGISTRO LLEVARÉ A CABO LAS ACCIONES QUE
CORRESPONDAN
UPDATE film SET replacement_cost = costePeli + 2 WHERE film_id = idPeli;
SELECT tituloPeli AS 'Titulo', costePeli AS 'coste original', replacement_cost AS 'coste
actualizado' FROM film WHERE film_id = idPeli; -- Solo mostrar el registro modificado
FETCH cursorPeli INTO idPeli, tituloPeli, costePeli; -- Fetch dentro del bucle para
obtener el siguiente registro
END WHILE;
END$$
DELIMITER ;
CALL incremento_penal;
/*
Queremos añadir correo electrónico a los médicos del hospital. Para ello ejecutaremos la
sentencia DDL
alter table doctor ADD COLUMN email VARCHAR(250);
Nos piden a continuación crear un procedimiento almacenado que reciba como parámetro el
dominio del email que debe añadirse a cada médico (sakila2.com)
que añada mediante CURSORES a cada doctor, su email con el siguiente formato:
a) El nombre sin espacios
b) El carácter @.
c) El dominio pasado como parámetro.
d) debe estar todo en minúsculas
*/
-- use hospital_mini;
-- alter table doctor ADD COLUMN email VARCHAR(250);
USE hospital_mini;
-- , LOWER(dominio)
DELIMITER $$
CREATE PROCEDURE añadir_email(IN dominio VARCHAR(100))
BEGIN
DECLARE fin_Cursor BOOLEAN DEFAULT FALSE;
DECLARE docId CHAR(9);
DECLARE docNombre CHAR(25);
DECLARE emailNuevo VARCHAR(250);
DECLARE cursorDoctores CURSOR FOR SELECT doctorId, doctorName FROM doctor;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET fin_Cursor = TRUE;
END;
OPEN cursorDoctores;
WHILE (fin_Cursor = FALSE)
DO
FETCH cursorDoctores INTO docId, docNombre;
IF ( fin_Cursor = FALSE)THEN
SET emailNuevo = CONCAT(docNombre, '@', LOWER(dominio));
UPDATE doctor SET email = CONCAT(LOWER(REPLACE(doctorName, ' ',
'')),'@',dominio) WHERE doctorId = docId;
END IF;
END WHILE;
CLOSE cursorDoctores;
END$$
DELIMITER ;
Drop procedure añadir_email;
CALL añadir_email('hospital.com');
select * from doctor;
-- OTRA FORMA:
USE hospital_mini;
DELIMITER $$
CREATE PROCEDURE añadir_email(IN dominio VARCHAR(100))
BEGIN
DECLARE fin_Cursor BOOLEAN DEFAULT FALSE;
DECLARE docId CHAR(9);
DECLARE docNombre CHAR(25);
DECLARE cursorDoctores CURSOR FOR SELECT doctorId, doctorName FROM doctor;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET fin_Cursor = TRUE;
END;
OPEN cursorDoctores;
WHILE (fin_Cursor = FALSE)
DO
FETCH cursorDoctores INTO docId, docNombre;
IF ( fin_Cursor = FALSE)THEN
UPDATE doctor SET email = CONCAT(LOWER(REPLACE(docNombre, ' ', '')), '@',
dominio) WHERE doctorId = docId;
END IF;
END WHILE;
CLOSE cursorDoctores;
END$$
DELIMITER ;
-- OTRO EJERCICIO:
/*
Escribe un SP llamado crear_lista_emails_doctores que devuelva la lista de emails de los
doctores
separados por un punto y coma. Ejemplo:
[email protected];[email protected];...
*/
USE hospital_mini;
USE hospital_mini;
DELIMITER $$
CREATE PROCEDURE crear_lista_emails_doctores()
BEGIN
DECLARE lista_emails TEXT DEFAULT '';
DECLARE fin_cursor BOOLEAN DEFAULT FALSE;
DECLARE docEmail VARCHAR(250);
-- Declara un cursor para obtener los correos electrónicos de los doctores
DECLARE cursorDoctores CURSOR FOR SELECT email FROM doctor;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_cursor = TRUE;
OPEN cursorDoctores;
-- Itera sobre los resultados del cursor
FETCH cursorDoctores INTO docEmail;
WHILE NOT fin_cursor DO
-- Concatena los correos electrónicos con punto y coma
SET lista_emails = CONCAT(lista_emails, docEmail, ';');
FETCH cursorDoctores INTO docEmail;
END WHILE;
CLOSE cursorDoctores;
-- Elimina el último punto y coma si existe, este no hace falta
IF LENGTH(lista_emails) > 0 THEN
SET lista_emails = LEFT(lista_emails, LENGTH(lista_emails) - 1);
END IF;
SELECT lista_emails AS lista_emails_doctores;
END$$
DELIMITER ;
-- sofia
DELIMITER $$
CREATE PROCEDURE crear_lista_emails_doctores(INOUT listaEmails VARCHAR(5000))
BEGIN
DECLARE emailDoctor VARCHAR(250) DEFAULT "";
DECLARE finCursor BOOLEAN DEFAULT FALSE;
DECLARE cursorEmailDoctor CURSOR FOR SELECT email FROM doctor;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET finCursor = TRUE;
OPEN cursorEmailDoctor;
SET listaEmails = "";
WHILE(finCursor = FALSE)
DO
FETCH cursorEmailDoctor INTO emailDoctor;
IF(finCursor = FALSE)THEN
SET listaEmails = CONCAT(emailDoctor,";",listaEmails);
END IF;
END WHILE;
CLOSE cursorEmailDoctor;
SELECT listaEmails;
END $$
DELIMITER ;
SET @listaEmails = ''; -- Definimos una variable para almacenar la lista de correos
electrónicos
-- Llamamos al procedimiento y pasamos la variable como parámetro
CALL crear_lista_emails_doctores(@listaEmails);
-- Mostramos el resultado
SELECT @listaEmails AS lista_de_emails;
