2º Administración de Sistemas Informáticos en Red
Tema 4 O NSTRUCCIÓN DE GUIONES DE ADMINISTRACIÓN AUTOMATIZACIÓN DE TAREAS : C ONSTRUCCIÓN
·· Tareas ··
1·
Programación de Procedimientos y Funciones Crea un procedimiento que visualice todas las películas cuyo costo de reemplazo sea superior a un valor que se pasará como parámetro de entrada. ¿Cuántas películas tienen un costo de reemplazo superior a 20€?
# Ejercicio 1 DELIMITER $$ DROP PROCEDURE IF EXISTS costes$$ CREATE PROCEDURE sakila.costes(IN coste INT) BEGIN SELECT * FROM film WHERE replacement_cost > coste; END$$ CALL sakila.costes(20);
2·
Crea un procedimiento que visualice todas las películas cuyo costo de reemplazo esté comprendido entre dos cantidades que se pasarán como parámetros de entrada. ¿Cuántas películas tienen un costo de reemplazo superior a 20€ y 21,99 €?
# Ejemplo 2 DELIMITER $$ DROP PROCEDURE IF EXISTS costes$$ CREATE PROCEDURE sakila.costes(IN coste1 INT, IN coste2 DECIMAL(5,2)) BEGIN SELECT * FROM film WHERE replacement_cost BETWEEN coste1 AND coste2; END$$ CALL sakila.costes(20, 21.99);
3·
Crea un procedimiento que reciba como parámetros de entrada el continente y la lengua y obtenga todos los países de ese continente que hablen ha blen esa lengua. ¿qué países de Asia tienen como lengua entre otras el inglés? Nota, a pesar de que el campo continente es de tipo enum, podemos pasar el continente como tipo varchar porque es compatible. # Ejemplo 3 DELIMITER $$ DROP PROCEDURE IF EXISTS continente_lengua$$ CREATE PROCEDURE continente_lengua (IN continente VARCHAR(30),IN lengua VARCHAR(30)) BEGIN SELECT name FROM country WHERE continent=continente continent=continente and code in (select countrycode from countrylanguage where language=lengua); END$$ CALL continente_lengua('Asi continente_lengua('Asia','English'); a','English');
4·
Escribe un procedimiento que reciba una palabra y la devuelva escrita del revés. Utiliza para ello un único parámetro de entrada y salida. # Ejercicio 4 DELIMITER $$ DROP PROCEDURE IF EXISTS reves$$ CREATE PROCEDURE reves (INOUT palabra VARCHAR(25)) BEGIN SELECT REVERSE(@entrada) 'Palabra del revés'; END$$ SET @entrada='educa';$$ CALL reves(@entrada);$$
5·
Crear un procedimiento almacenado que cambie el mail de un cliente, tabla costumer, por otro que se pasará como parámetro, el procedimiento almacenado recibirá dos parámetros, el identificador del cliente y el nuevo mail. Ejecutar el procedimiento almacenado. # Ejemplo 5 DELIMITER $$ DROP PROCEDURE IF EXISTS cambio_mail$$ CREATE PROCEDURE s akila.cambio_mail(id SMALLINT, correo VARCHAR(80)) BEGIN UPDATE customer SET email=correo WHERE customer_id=id; END$$ CALL sakila.cambio_mail(22, '
[email protected]'); SELECT customer_id,email FROM customer WHERE customer_id=22;
6·
Crear un procedimiento almacenado que tenga como parámetros el nombre, apellidos y el nuevo mail de un cliente de la tabla costumer (NOTA: debes utilizar el procedimiento anterior para cambiar el mail.) # Ejemplo 6 DELIMITER $$ DROP PROCEDURE IF EXISTS cambio_correo$$ CREATE PROCEDURE sakila.cambio_correo(nombre VARCHAR(45), apellido VARCHAR(45), correo VARCHAR(50)) BEGIN DECLARE identi SMALLINT; SELECT customer_id INTO identi FROM customer WHERE first_name=nombre AND last_name=apellido; CALL sakila.cambio_mail(identi,correo); END$$ CALL sakila.cambio_correo('MARY','SMITH','
[email protected]'); SELECT first_name,last_name,email FROM sakila.customer WHERE first_name='MARY';
7·
Crea un procedimiento que pase dos parámetros de entrada, identificador de categoría e identificador de actor y obtenga los datos de las películas sobre esa categoría en las que ha trabajado ese actor. Prueba el ejemplo con el actor 182 y la categoría 2. # Ejemplo 7 DELIMITER $$ DROP PROCEDURE IF EXISTS categoria_actor$$ CREATE PROCEDURE sakila.categoria_actor(IN actor SMALLINT(5), IN categoria TINYINT(3)) BEGIN SELECT title,description,length,rating,special_features FROM film WHERE film_id IN ( SELECT film_id FROM film_actor WHERE actor_id=actor) AND film_id IN (SELECT film_id FROM film_category WHERE category_id=categoria); END$$ CALL sakila.categoria_actor('182','2');
8·
Crea un procedimiento que tenga un parámetro de entrada que será el nombre de la categoría y un parámetro de salida que contendrá el número de películas para esa categoría. # Ejemplo 8 DELIMITER $$ DROP PROCEDURE IF EXISTS categoria_numpeliculas$$ CREATE PROCEDURE categoria_numpeliculas(IN categoria varchar(45), OUT contar INT) BEGIN SELECT COUNT(*) INTO contar FROM film WHERE film_id IN (SELECT film_id FROM film_category WHERE category_id IN (SELECT category_id FROM category WHERE name= categoria)); SELECT @contar 'Películas de animación'; END$$ CALL categoria_numpeliculas('Animation', @contar);
9·
Desarrollar una función que devuelva el número de años completos que hay entre dos fechas que se pasan como parámetros. Utiliza la función DATEDIFF. Para visualizar el formato de la fecha con la que trabaja mysql en la sesión que estás utilizando visualiza la fecha actual utilizando la función current_date() # Ejemplo 9
10 ·
Escribir una función que, haciendo uso de la función anterior, devuelva los trienios que hay entre dos fechas # Ejemplo 10
11 ·
Codificar un procedimiento que reciba una lista de hasta tres números y visualice su suma (ten en cuenta que pueden ser NULL) # Ejemplo 11 DELIMITER $$ DROP PROCEDURE IF EXISTS suma_numeros$$ CREATE PROCEDURE suma_numeros(num1 INT, num2 INT, num3 INT) BEGIN SELECT SUM(IFNULL(num1,0)+IFNULL(num2,0)+IFNULL(num3,0)) 'Suma'; END$$ CALL suma_numeros(1,2,3);
12 ·
Escribir un procedimiento que permita borrar un actor cuyo identificador se pasará como parámetro. Si el actor cuyo número se ha pasado como parámetro no existe, aparecerá un mensaje diciendo “Ese actor no existe”. Comprueba el funcionamiento del procedimiento. ¿Qué
ocurre cuando tratas de borrar un actor que ya existe? ¿Por qué? *En el caso de intentar eliminar un actor existente no podríamos, ya que la tabla se encuentra unida a otras a través de claves foráneas. # Ejemplo 12 DELIMITER $$ DROP PROCEDURE IF EXISTS borrar_actor$$ CREATE PROCEDURE borrar_actor(num INT) BEGIN DECLARE numero INT; SELECT actor_id INTO numero FROM actor WHERE actor_id=num; IF numero IS NULL THEN SELECT CONCAT('El actor ', num,' no e xiste') 'Error de borrado'; ELSE DELETE FROM actor WHERE actor_id=num; END IF; END$$
CALL borrar_actor(222);
