Upload
lenga
View
215
Download
0
Embed Size (px)
Citation preview
-ACTIVIDAD PUNTUABLE 3 (CORRESPONDIENTE AL TEMA 4)
1. TÍTULO DE LA ACTIVIDAD: Automatización de tareas mediante guiones de administración
2. DESCRIPCIÓN DE LA ACTIVIDAD:
OBJETIVOS- Con esta actividad conocerás las principales herramientas de MySQL para la automatización de tareas de administración. Programarás rutinas almacenadas (funciones y procedimientos), crearás disparadores, vistas y eventos.
METODOLOGÍA- La mejor manera de alcanzar los objetivos sobre rutinas es hacer ejemplos y ejecutar el código para familiarizarse con la sintaxis de las rutinas. Esto mismo haremos con los disparadores, vistas y eventos programados. Es muy práctico ir leyendo cada apartado y hacer cada ejemplo, antes de pasar al siguiente, ya que los ejemplos del manual están diseñados en orden de dificultad creciente. Al final de cada uno de los bloques (rutinas, disparadores, vistas y eventos) podremos hacer los ejercicios de ese bloque concreto.
-Para cualquier problema o duda sobre la sintaxis de un ejemplo o cómo utilizar cualquiera de las herramientas que se ven en el capítulo se recomienda consultar la documentación oficial (http://www.mysql.com), además del manual de la asignatura, antes que buscar en cualquier foro o blog no oficial.
ACTIVIDAD
RUTINAS (procedimientos y funciones almacenados)
Los siguientes ejercicios (del i al iv) los puedes hacer sobre la base de datos “test” instalada por
defecto en los servidores MySQL.
i. Crea un procedimiento almacenado que denominarás “hoy_es()” que muestre el día actual
de la semana, con el Texto “Hoy es…” en la cabecera de la tabla de salida como en la siguiente
imagen:
DELIMITER //
DROP PROCEDURE IF EXISTS HOY_ES//
CREATE PROCEDURE HOY_ES()
BEGIN
DECLARE HOY_ES CHAR(15);
SET HOY_ES=dayname(current_date());
SELECT HOY_ES;
END//
Call HOY_ES();
ii. Crea un procedimiento almacenado denominado “mayúsculas(cadena)” que tendrá como
entrada una cadena de 100 caracteres como máximo. El resultado de llamar a este
procedimiento será el mismo texto de entrada en mayúsculas. Por ejemplo, si hacemos la
llamada call mayúsculas(‘Texto Prueba’) obtendremos ‘texto prueba’.
DELIMITER &&
DROP PROCEDURE IF EXISTS MAYUSCULAS&&
CREATE PROCEDURE MAYUSCULAS(IN TEXTO CHAR(100))
BEGIN
DECLARE TEXT_MAYU CHAR(100);
SET TEXT_MAYU=UPPER(TEXTO);
SELECT TEXT_MAYU;
END&&
call MAYUSCULAS('miguel madroño morcillo estudia ASIR');
iii. Crea una función “fun_mayusculas(cadena)” que haga lo mismo que el procedimiento
anterior. Así cuando usemos la función con select fun_mayusculas(‘Texto Prueba’)
obtendremos como resultado de la consulta el texto ‘texto prueba’.
Lo primero que he tenido que hacer es lo modificar una variable global, porque me daba este
error:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ
L DATA in its declaration and binary logging is enabled (you *might* want to use
the less safe log_bin_trust_function_creators variable)
Y he modificado la variable asi:
SET GLOBAL log_bin_trust_function_creators = 1;
Luego he intoducido el codigo de la function y todo OK:
DELIMITER &&
DROP FUNCTION IF EXISTS FUN_MAYUSCULAS; &&
CREATE FUNCTION FUN_MAYUSCULAS(TEXT VARCHAR(100)) RETURNS VARCHAR(100)
BEGIN
DECLARE MAYU VARCHAR(100);
SET MAYU=UPPER(TEXTO);
RETURN MAYU;
END; &&
O bien se puede crear la función de la siguiente manera con la sentencia DETERMINISTIC:
DELIMITER &&
DROP FUNCTION IF EXISTS FUN_MAYUSCULAS; &&
CREATE FUNCTION FUN_MAYUSCULAS(TEXT VARCHAR(100)) RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
DECLARE MAYU VARCHAR(100);
SET MAYU=UPPER(TEXT);
RETURN MAYU;
END; &&
Como se ve en la siguiente imagen:
Resultado de la función:
select fun_mayusculas('miguel madroño estudia asir en cepal');
iv. Crea una función llamada “ese_dia_era(fecha)” que devuelva el día de la semana de una
fecha que daremos de entrada (en formato date). La salida será en español con el formato
“lunes”, “martes”, … “domingo”.
Por ejemplo si ponemos ese_dia_era(‘2013-09-07’) nos devolverá ‘sábado’
DELIMITER &&
DROP FUNCTION IF EXISTS ESE_DIA_ERA; &&
CREATE FUNCTION ESE_DIA_ERA(fecha DATE) RETURNS VARCHAR(200)
BEGIN
DECLARE hoy int;
DECLARE dia VARCHAR(20);
SET hoy=dayofweek(fecha);
IF hoy=1 THEN set dia="DOMINGO";
ELSEIF hoy=2 THEN set dia="LUNES";
ELSEIF hoy=3 THEN set dia="MARTES";
ELSEIF hoy=4 THEN set dia="MIERCOLES";
ELSEIF hoy=5 THEN set dia="JUEVES";
ELSEIF hoy=6 THEN set dia="VIERNES";
ELSEIF hoy=7 THEN set dia="SABADO";
END IF;
RETURN dia;
END; &&
O
DELIMITER &&
DROP FUNCTION IF EXISTS ESE_DIA_ERA; &&
CREATE FUNCTION ESE_DIA_ERA(fecha DATE) RETURNS VARCHAR(200)DETERMINISTICBEGIN
DECLARE hoy int;
DECLARE dia VARCHAR(20);
SET hoy=dayofweek(fecha);
IF hoy=1 THEN set dia="DOMINGO";
ELSEIF hoy=2 THEN set dia="LUNES";
ELSEIF hoy=3 THEN set dia="MARTES";
ELSEIF hoy=4 THEN set dia="MIERCOLES";
ELSEIF hoy=5 THEN set dia="JUEVES";
ELSEIF hoy=6 THEN set dia="VIERNES";
ELSEIF hoy=7 THEN set dia="SABADO";
END IF;
RETURN dia;
END; &&
select ESE_DIA_ERA('2013-09-07');
RESULTADO:
v. Sobre la base de datos “liga” (suministrada en el script bases_libro.sql) realiza un
procedimiento almacenado, denominado “puntos_equipo(eq)”, que nos devuelva la suma de
los puntos totales en la tabla “jugador” para un equipo concreto que daremos en el parámetro
“eq”. En este ejercicio utilizarás la función sum de MySQL.
Por ejemplo si llamamos al procedimiento con call puntos_equipo(‘mad’) nos devolverá la
suma de puntos totales del equipo ‘mad’.
DELIMITER //
DROP PROCEDURE IF EXISTS PUNTOS_EQUIPO//
CREATE PROCEDURE PUNTOS_EQUIPO(team char(10))
BEGIN
select concat('LOS PUNTOS DEL EQUIPO: ',team,' ES : '), sum(puntos_total) as suma from
liga.jugador WHERE team=jugador.equipo;
END//
call puntos_equipo('mad');
vi. Sobre la base de datos “liga” haz un nuevo procedimiento almacenado
“puntos_equipo_2(eq)” que haga lo mismo que la del apartado anterior, pero utilizando un
cursor para recorrer la tabla “jugador”, y una variable suma_puntos, donde vayas sumando los
puntos del equipo pasado en el parámetro “eq”.
DELIMITER //
DROP PROCEDURE IF EXISTS PUNTOS_EQUIPO_2//
CREATE PROCEDURE PUNTOS_EQUIPO_2(team char(10))
BEGIN
declare suma_puntos int(10);
declare cursor1 cursor for
select sum(puntos_total) from liga.jugador WHERE team=jugador.equipo;
OPEN cursor1;
FETCH cursor1 INTO suma_puntos;
CLOSE cursor1;
SELECT concat('LOS PUNTOS DEL EQUIPO ',team,' SON : '), suma_puntos;
END//
call puntos_equipo_2('mad');
O BIEN TAMBIEN LO HE REALIZADO DE LA SIGIENTE MANERA SEGÚN EL VIDEO TUTORIAL
SOBRE CURSORES SUBIDO EL DIA 03/12/2013 POR EL PROFESORE JUAN IGNACIO:
DELIMITER //
DROP PROCEDURE IF EXISTS PUNTOS_EQUIPO_2_P//
CREATE PROCEDURE PUNTOS_EQUIPO_2_P(team char(10))
BEGIN
DECLARE fin_bucle bool;
DECLARE suma_total int(9);
DECLARE puntos_equipo int(10);
DECLARE cursor1 cursor for
SELECT puntos_total FROM liga.jugador WHERE team=jugador.equipo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_bucle=1;
SET suma_total=0;
SET puntos_equipo=0;
SET fin_bucle=0;
OPEN cursor1;
bucle_cursor: LOOP
FETCH cursor1 INTO puntos_equipo;
IF fin_bucle=1 THEN
LEAVE bucle_cursor;
END IF;
SET suma_total=suma_total+puntos_equipo;
END LOOP bucle_cursor;
CLOSE cursor1;
SELECT concat('LOS PUNTOS DEL EQUIPO ',team,' SON : '), suma_total;
END//
call puntos_equipo_2_P('mad');
DISPARADORES
vii. En la base de datos ebanca, crea una tabla sospechosos (id int auto_increment primary key,
cod_cuenta varchar(100), fecha datetime, cantidad_nueva int, usuario varchar(40)).
En esta tabla “sospechosos” escribirá un una entrada automáticamente un trigger (nombrado
como trigger_sospechoso) con los datos de cod_cuenta, fecha, cantidad y usuario actual, cada
vez que alguien haga un movimiento de ingreso de más de 3000 o de cargo de más de 1000
(tabla “ingresos”). Escribe este trigger.
Creamos la tabla sospechosos:
CREATE TABLE sospechosos (id int auto_increment primary key, cod_cuenta varchar(100),
fecha datetime, cantidad_nueva int, usuario varchar(40));
Comprobamos las tabla movimiento que es la tabla donde aplicaremos el TRIGGER y se ve que
todavía no tiene registros:
Comprobamos también la tabla clientes:
Y también comprobamos la tabla cliente:
Insertamos valores en la tabla cuenta, ya que la vamos a necesitar:
INSERT INTO cuenta VALUES ('C','2012-01-01','P',0);
INSERT INTO cuenta VALUES ('A','2010-01-01','P',1);
INSERT INTO cuenta VALUES ('C','2010-02-01','P',2);
INSERT INTO cuenta VALUES ('C','2009-02-13','P',3);
INSERT INTO cuenta VALUES ('C','2012-01-01','P',4);
INSERT INTO cuenta VALUES ('A','2010-01-01','P',5);
INSERT INTO cuenta VALUES ('C','2010-02-01','P',6);
INSERT INTO cuenta VALUES ('C','2009-02-13','P',7);
Creamos el TRIGGER controlando los ingresos mayores a 3000 y tambien controlando los
cargos realizados mayores a 1000:
DELIMITER &&
DROP TRIGGER IF EXISTS ebanca.triger_sospechosos&&
CREATE TRIGGER triger_sospechosos AFTER INSERT ON movimiento
FOR EACH ROW BEGIN
DECLARE nombre_cliente CHAR(20);
IF (new.cantidad > 3000 or new.cantidad < -1000) THEN
BEGIN
SELECT cliente.nombre INTO nombre_cliente FROM cliente WHERE new.dni=cliente.dni;
INSERT INTO sospechosos(cod_cuenta,fecha,cantidad_nueva,usuario)
VALUES (new.cod_cuenta,new.fecha,new.cantidad,nombre_cliente);
END;
END IF;
END &&
Pero primero insertamos valores de en la tabla cliente, con los valores ya existentes en la tabla
clientes, que primero me exportado todos los datos de la tabla clientes en un CSV y he usado la
función concatenar para dejarlo con el formato de insertar en la tabla cliente:
Los comandos de inserccion quedarían asi:
INSERT INTO cliente VALUES (1,11111111,"jcarlos","baldo","GARCIA","d1");
INSERT INTO cliente VALUES (2,22222222,"pilar","rodri","GARCIA","d2");
INSERT INTO cliente VALUES (3,33333333,"pablo","babes","GARCIA","d3");
INSERT INTO cliente VALUES (4,44444444,"javi","sonas","GARCIA","d4");
INSERT INTO cliente VALUES (5,55555555,"angel","alva","GARCIA","d5");
INSERT INTO cliente VALUES (6,66666666,"pilar","bueso","perez","d6");
INSERT INTO cliente VALUES (7,77777777,"alberto","perez","dominguez","d7");
INSERT INTO cliente VALUES (8,88888888,"antonio","martinez","barea","d8");
INSERT INTO cliente VALUES (9,99999999,"maria jesus","longares","hernandez","d9");
INSERT INTO cliente VALUES (10,10101010,"ana","guillen","lopera","d10");
INSERT INTO cliente VALUES (11,12121212,"fernando","montero","iglesias","d11");
INSERT INTO cliente VALUES (12,13131313,"silvia","lopez","escartin","d12");
INSERT INTO cliente VALUES (13,14141414,"raquel","beltran","acebes","d13");
INSERT INTO cliente VALUES (14,15151515,"mario","cabaña","encinas","d14");
INSERT INTO cliente VALUES (15,16161616,"manuel","falceto","carrera","d15");
INSERT INTO cliente VALUES (16,17171717,"jordi","eulalia","perales","d16");
INSERT INTO cliente VALUES (17,18181818,"marc","garriga","higuera","d17");
INSERT INTO cliente VALUES (18,19191919,"rafael","hueso","ibañez","d18");
INSERT INTO cliente VALUES (19,20202020,"fran","granada","mariategui","d19");
INSERT INTO cliente VALUES (20,20202020,"porfirio","gambo","perez","d20");
Y ahora se ve que ya la tabla cliente tiene valores, ya que es necesario ya que la tabla
movimiento depende con clave FOREING de la tabla cliente y de la tabla cuenta, que se ve a
continuación:
Y ahora vemos sus valores:
Y ahora comprobamos si funciona insertando una cuantas columnas o movimientos en la tabla
movimiento para ver si salta el disipador:
INSERT INTO movimiento VALUES ('2013/12/03',3500,11111111,1);
INSERT INTO movimiento VALUES ('2013/12/03',4250,22222222,6);
INSERT INTO movimiento VALUES ('2013/12/03',500,55555555,3);
INSERT INTO movimiento VALUES ('2013/12/03',6000,33333333,0);
INSERT INTO movimiento VALUES ('2013/12/03',600,44444444,2);
INSERT INTO movimiento VALUES ('2013/12/13',6000,44444444,2);
Movimientos de cargo:
INSERT INTO movimiento VALUES ('2013/12/14',-1125,55555555,7);
INSERT INTO movimiento VALUES ('2013/12/15',-125,55555555,7);
Miramos los registros de la tabla movimiento:
Y vemos los registros de la tabla sospechosos que según los regsistros que he metido en la
tabla movimiento debe haber 5 bueno 6 por que he realizado anterior mente una prueba con
el cargo mayor que 1000 registros:
VISTAS
viii. Crea una vista, sobre la base de datos ebanca, que muestre los datos dni, nombre,
apellido1, cod_cuenta y saldo. Utiliza en la creación de la vista con la opción de seguridad para
el que la invoca.
Después intenta acceder con otro usuario distinto. ¿Qué sucede? ¿Qué habría que hacer para
que este segundo usuario también tuviese permisos?
CREATE OR REPLACE DEFINER=CURRENT_USER SQL security INVOKER VIEW datos_clientes AS
SELECT clientes.dni, clientes.nombre, clientes.apellido1, cuentas.cod_cuenta, cuentas.saldo
FROM ebanca.clientes, ebanca.cuentas WHERE clientes.codigo_cliente=cuentas.cod_cliente
GROUP BY cuentas.cod_cuenta;
También he utilizado y probado este código, que proporciona mas datos, y lo agrupa
combinado el código de cuenta y el código de cliente (pero los pantallazos son todos del
código anterior):
CREATE OR REPLACE DEFINER=CURRENT_USER SQL security INVOKER VIEW datos_clientes_2
AS
SELECT cuentas.cod_cuenta, cuentas.saldo, cuentas.cod_cliente, clientes.dni, clientes.nombre,
clientes.apellido1
FROM ebanca.clientes, ebanca.cuentas
WHERE clientes.codigo_cliente=cuentas.cod_cliente ORDER BY cuentas.cod_cuenta AND
clientes.codigo_cliente;
Invocamos la VISTA:
SELECT * FROM DATOS_CLIENTES;
En estos pantallazos te muestro la diferecias entre los dos códigos:
Primer código:
Segundo código:
Ahora nos conectamos con el usuario user:
Este usuario no puede convocar la vista:
NO tiene permisos para invocar la vista, se deberían de establecer desde la conexión de
administrador y establecerle los permisos de SELECT sobre dicha vista o BBDD.
De la siguiente manera:
Y como se ve a continuación ya si puede ejecutar la vista, en el pantallazo también se ve los
errores de permisos de este usuario (user) sobre la vista datos_clientes creada.
EVENTOS
ix. Crea un evento denominado “ranking” que establezca cada primero de mes, desde las
00:00:00 del 1 de enero de 2013, una inserción en una tabla “ranking_autores” con los datos
de autor_id y el número de noticias publicadas por ese autor (utiliza la tabla “noticias” para
esta consulta, y crea previamente una tabla “ranking_autores” con los campos autor_id y
num_noticias).
Creamos la tabla:
CREATE TABLE `nmotor`.`ranking_autores` (
`autor_id` INT NOT NULL,
`num_noticias` INT NULL,
PRIMARY KEY (`autor_id`));
Comprobamos la variable EVENT_SCHEDULER para ver si esta activa debe tener el valor ON.
Modificamos su valor con el comando SET GLOBAL event_scheduler=ON;
Comprobamos que ya esta activo con el comando SHOW PROCESSLIST;
Creamos el EVENTO:
DELIMITER //
DROP EVENT IF EXISTS ranking//
CREATE EVENT ranking ON SCHEDULE EVERY 1 MONTH STARTS '2013-01-01 00:00:00' ENDS
'2013-12-31 00:00:00' ENABLE
DO
BEGIN
DELETE from nmotor.ranking_autores where autor_id>=0;
INSERT INTO nmotor.ranking_autores SELECT autor_id, count(autor_id) as num_noticias FROM
nmotor.noticias WHERE fecha_<= date_sub(current_date(), interval 30 day) GROUP BY
autor_id;
END;//
Este al cambiarle la fecha a una fecha actual al dia 26/12/2013 si funciona inmediatamente:
DELIMITER //
DROP EVENT IF EXISTS ranking//
CREATE EVENT ranking ON SCHEDULE EVERY 1 minute STARTS '2013-12-26 01:10:00' ENDS
'2013-12-31 00:00:00' ENABLE
DO
BEGIN
DELETE from nmotor.ranking_autores where autor_id>=0;
INSERT INTO nmotor.ranking_autores SELECT autor_id, count(autor_id) as num_noticias FROM
nmotor.noticias WHERE fecha_pub <= date_sub(current_date(), interval 30 day) GROUP BY
autor_id;
END;//
Comprovamos que el EVENTO SE HA CREADO con el comando SHOW EVENTS:
Y ahora como vemos el evento se ha ejecutado, insertando los datos de esta manera en la
tabla ranking_autores:
3. EXTENSIÓN MÁXIMA DE LA ACTIVIDAD: 10 hojas con los códigos sql de los script necesarios para resolver los 8 ejercicios y contestar a las cuestiones.