Diferencia entre revisiones de «BD UD7 Funciones»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 23: Línea 23:
  
 
* En ejemplo de función:
 
* En ejemplo de función:
::<syntaxhighlight lang="java" line enclose="div" highlight="3,5" >
+
::<syntaxhighlight lang="SQL" line enclose="div" highlight="3,5" >
 
DELIMITER //
 
DELIMITER //
  
Línea 37: Línea 37:
  
 
: '''Ejemplos de llamada:'''
 
: '''Ejemplos de llamada:'''
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+
::<syntaxhighlight lang="SQL" line enclose="div" highlight="" >
 
SELECT ejemploFuncion();
 
SELECT ejemploFuncion();
  
Línea 48: Línea 48:
  
 
: Por ejemplo, si tengo definida una función que devuelve el nombre de un empleado en base a su id, de la forma: employees_getName(id Integer), puedo llamarla de la forma:
 
: Por ejemplo, si tengo definida una función que devuelve el nombre de un empleado en base a su id, de la forma: employees_getName(id Integer), puedo llamarla de la forma:
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+
::<syntaxhighlight lang="SQL" line enclose="div" highlight="" >
 
SELECT employees_getName(emp_no)
 
SELECT employees_getName(emp_no)
 
FROM salaries;
 
FROM salaries;

Revisión del 09:11 4 may 2020

Funciones

Nota: Todos los ejemplos están basados en esta base de datos.


Mod BD Prog Proc 15.jpg
Imagen obtenida de https://dev.mysql.com
El conjunto de instrucciones van en el 'routine_body'. Si lo forman varias (lo normal) deben ir entre las etiquetas BEGIN y END.
Fijarse que el END debe llevar punto y coma si a continuación le siguen nuevas instrucciones.
  • Una función lo conforman un bloque de instrucciones que se ejecutan y que 'devuelven' un dato el cual es 'recogido' por el que realiza la llamada a la función.
Podríamos ver una función como un procedimiento con un parámetro de tipo OUT.
  • Con respecto a los procedimientos, la función se diferencia (a mayores del hecho de devolver un valor) en que se pueden usar dentro de instrucciones SQL, como por ejemplo un SELECT, de la forma SELECT funcion(),col1 FROM Tabla o SELECT * FROM Tabla WHERE col = funcion()...
En estos casos, la función se va a llamar por cada fila de la tabla que cumpla los criterios de selección indiciados en la consulta SELECT.
Los tipos de datos que va a poder devolver una función son: STRING, INTEGER, REAL o DECIMAL.
Nota: Indicar que en el caso de que la función devuelva un número de tipo DECIMAL, es como si devolviera un String.


  • En ejemplo de función:
1 DELIMITER //
2 
3 CREATE FUCNTION ejemploFuncion() RETURNS VARCHAR(20)
4 BEGIN
5     RETURN 'Ejemplo';
6 END
7 //
Línea 3: Como vemos, en la definición de la función indicamos el tipo de dato que va a devolver (que si consultamos la sintaxis puede ser uno de estos tipos:STRING|INTEGER|REAL|DECIMAL).
  • Línea 5: En cualquier parte de la función hacemos uso de la instrucción return pasando un dato que debe ser del mismo tipo que el indicado en la definición.


Ejemplos de llamada:
1 SELECT ejemploFuncion();
2 
3 SET @valorDevuelto = ejemploFuncio();
Mostrará la cadena 'Ejemplo'.


  • Una de las ventajas que tienen las funciones con respecto a los procedimientos es la posibilidad de emplearlas dentro de consultas SQL y emplear columnas de la consulta como parámetros de entrada para la función.
Por ejemplo, si tengo definida una función que devuelve el nombre de un empleado en base a su id, de la forma: employees_getName(id Integer), puedo llamarla de la forma:
1 SELECT employees_getName(emp_no)
2 FROM salaries;
Como vemos, la función tiene como parámetro un id el cual se obtiene de cada fila de la consulta select. Es decir, la sentencia SELECT devolverá un conjunto de filas, por cada fila de dicha consulta, se ejecutará la función enviando como parámetro el id de la fila.



IMPORTANTE:

  • ACLARACIÓN 1:
A la hora de crear una función, si tenemos activado el registro binario de log, debemos indicar que dicha función es determinista. Una función se denomina determinista si siempre va a devolver el mismo resultado al aplicar la misma entrada. Si no fuera así y realizamos un procedimiento de restauración de la base de datos empleado el fichero de log binario, podría darse el caso de que los datos 'recuperados' no fueran los mismos que antes de la recuperación.
Para hacerlo, en la definición de la función debemos poner:
CREATE FUNCTION nombreFuncion() RETURNS tipoDato DETERMINISTIC
Por defecto, tanto los procedimientos como las funciones, son consideradas como NO DETERMINISTAS.
Otros modificadores que podemos añadir a la función y que van después del tipo de dato son:
  • CONTAINS SQL: La función o procedimiento no contiene sentencias SQL que lean (como SELECT) o escriban datos (como INSERT).
  • NO SQL: La función o procedimiento no contiene sentencias SQL.
  • READS SQL DATA: La función o procedimiento contiene sentencias que leen datos (como SELECT) pero no sentencias que modifiquen datos.
  • MODIFIES SQL DATA: La función o procedimiento contiene sentencias que modifican datos (como INSERT, DELETE, UPDATE).


Importante:
Para comprobar si el registro binario de log está activado podemos ejecutar la orden: SELECT @@log_bin;
En caso de estarlo (devuelve el valor 1 la llamada anterior), debemos de realizar alguna de las siguientes acciones:
  • Añadir a la definición de la función al menos uno de los modificadores siguientes: DETERMINISTIC, NO SQL o READS SQL DATA.
  • Desactivar el registro binario de log.
  • Modificar la variable del sistema log_bin_trust_function_creators con la orden: SET GLOBAL log_bin_trust_function_creators = 1;



  • ACLARACIÓN 2:
A la hora de realizar comparaciones de cadena empleando funciones (por ejemplo en la cláusula WHERE al comparar una expresión con un valor concreto), Mysql puede dar un error de tipo Error code 1267. Illegal mix of collations ....
Por ejemplo: SELECT * FROM Tabla WHERE funcion(col1) = 'VALOR'
Esto es debido a que la expresión que devuelve Mysql al emplear la función tendrá el mismo Collate que el definido en la tabla mientras que a la cadena con la que estamos comparando Mysql no le da el mismo collate.
Para eliminar este error, tendremos que hacer uso de la orden COLLATE de la forma:
SELECT * FROM Tabla WHERE funcion(col1) = 'VALOR' COLLATE collate_de_la_tabla
Para saber que COLLATE tiene una tabla, podemos ejecutar la orden 'SHOW CREATE TABLE nombre_tabla;' o ir desde MysqlWorkBench a la información de la tabla.



Ejercicios propuestos Funciones

Nota: Después de crear la función llama a la misma de diferentes formas (dentro de una orden SQL o directamente).


  • Ejercicio 1: Crea una función de nombre utilidades_getMesEnLetra a la que se le pase un número y devuelva el nombre del mes. En caso de que el número no se corresponda con ningún mes, debe devolver null.
Fijarse que esta función es determinista.
  • Llama a la función directamente y guarda el resultado en una variable de sesión.
  • Llama a la función para que muestre los meses en letra en los que se celebró la atracción 'El gran felino'.
  • Llama a la función para que muestre las atracciones que se celebraron en ABRIL (busca por la cadena ABRIL) (recuerda hacer uso de COLLATE).
Nota: Indicar que Mysql ya dispone de dicha función, a la que se le pasa una fecha y devuelve el mes en forma de cadena: monthname().


  • Ejercicio 2: Modifica un ejercicio anterior en el que creamos el procedimiento de nombre 'atracciones_getNumPorMes' y crea una función de nombre utilidades_getMesEnNumero a la que se le pase el nombre de un mes y devuelva el número que se corresponde con ese nombre. Si el mes no existe debe devolver -1. Modifica el procedimiento para que haga uso de la función.


  • Ejercicio 3: Crea una función de nombre animales_getEstadoPorAnhos que devuelva la cadena:
  • Si tipo = León
  • anhos < 2: 'JOVEN'
  • anhos >=2 y <=5: 'MADURO'
  • anhos > 5: 'VIEJO'
  • Cualquier otro tipo:
  • anhos < 1: 'JOVEN'
  • anhos >=1 y <=3: 'MADURO'
  • anhos > 3: 'VIEJO'
Llama a la función para mostrar el estado por años de cada uno de los animales del CIRCO.


  • Ejercicio 4: Crea una función de nombre pistas_getDiferenciaAforo al que se le pase el nuevo aforo de una pista y devuelva la diferencia entre el aforo nuevo y el aforo anterior.
Si la diferencia < 100 debe devolver la cadena 'PEQUEÑA menor que 100'
Si la diferencia está entre 100 y 500 debe devolver la cadena 'REGULAR entre 100 y 500'
Si la diferencia > 500 debe devolver la cadena 'ABISMAL mayor que 500'
Por ejemplo: PISTA1, 150 => Si la pista tiene actualmente un aforo de 100, debe devolver 150-100 = 50 => PEQUEÑA menor que 100
Si la pista no existe debe devolver null.
Acordaos de añadir los modificadores adecuados a la creación de la función.
Muestra los datos de todas las pistas junto la diferencia del aforo empleando la función anterior y enviando un aforo de 600.
Muestra las pistas que tengan una diferencia ABISMAL (busca la cadena con la función INSTR con un aforo propuesto de 1000.



  • Ejercicios propuesto sin solución:
  • Ejercicio 5: Crea una función de nombre pistas_getNumAtracciones que dada una pista devuelva el número de veces que se celebró.
Llama a dicha función por cada una de las pistas.


  • Ejercicio 6: Crea una función de nombre atracciones_getListEntreFechas que muestre las atracciones que se celebraron entre dos fechas dadas, y devuelva el número de atracciones que se celebraron.
Se debe comprobar que las fechas tienen el formato correcto y que primero se envía la fecha menor. En caso contrario debe mostrar un mensaje explicando el error y debe devolver el valor -1.


  • Ejercicio 7: Crea una función de nombre artistas_getNumAnimales al que se le pase el nif de un artista y devuelva a cuantos animales cuida.
En caso de que no exista el artista debe devolver -1.
Llama a la función y haz que muestre a cuantos animales cuidan cada uno de los artistas.
Guarda en una variable de sesión a cuantos animales cuida el artista con nif 22222222B y muestra su valor.
Muestra los artistas que cuidan a 2 o más animales empleando la función.


  • Ejercicio 8: Crea una función de nombre utilidades_getEstacionPorMes que en función del mes que se le envíe como dato, devuelva al estación en la que se encuentre.
Llama a dicha función con el valor 7 y guarda el resultado en una variable de sesión. Muestra su valor.
Muestra las atracciones que empezaran en PRIMAVERA (tabla ATRACCIONES).
Muestra las ganancias por estación.





Soluciones Ejercicios propuestos Funciones

  • Ejercicio 1: Crea una función de nombre utilidades_getMesEnLetra a la que se le pase un número y devuelva el nombre del mes. En caso de que el número no se corresponda con ningún mes, debe devolver null.
Fijarse que esta función es determinista.
 1 USE CIRCO;
 2 DROP FUNCTION IF EXISTS utilidades_getMesEnLetra;
 3 
 4 DELIMITER $$
 5 CREATE FUNCTION utilidades_getMesEnLetra (p_mes tinyint)
 6 RETURNS VARCHAR(10) DETERMINISTIC NO SQL
 7     COMMENT 'Devuelve el mes en letra que se corresponde con el número de mes'
 8 BEGIN
 9 	DECLARE v_mesEnLetra varchar(10);		-- Valor por defecto NULL
10 
11 	CASE p_mes
12 		WHEN 1 THEN SET v_mesEnLetra = 'ENERO';
13 		WHEN 2 THEN	SET v_mesEnLetra = 'FEBRERO';
14 		WHEN 3 THEN SET v_mesEnLetra = 'MARZO';
15 		WHEN 4 THEN SET v_mesEnLetra = 'ABRIL';
16 		WHEN 5 THEN SET v_mesEnLetra = 'MAYO';
17 		WHEN 6 THEN SET v_mesEnLetra = 'JUNIO';
18 		WHEN 7 THEN SET v_mesEnLetra = 'JULIO';
19 		WHEN 8 THEN SET v_mesEnLetra = 'AGOSTO';
20 		WHEN 9 THEN SET v_mesEnLetra = 'SEPTIEMBRE';
21 		WHEN 10 THEN SET v_mesEnLetra = 'OCTUBRE';
22 		WHEN 11 THEN SET v_mesEnLetra = 'NOVIEMBRE';
23 		WHEN 12 THEN SET v_mesEnLetra = 'DICIEMBRE';
24 	END CASE;
25 	
26 	RETURN v_mesEnLetra;
27 END $$
28 DELIMITER ;


Ejemplos de uso:

  • Llama a la función directamente y guarda el resultado en una variable de sesión.
  • Llama a la función para que muestre los meses en letra en los que se celebró la atracción 'El gran felino'.
  • Llama a la función para que muestre las atracciones que se celebraron en ABRIL (busca por la cadena ABRIL).
 1 SET @mes = utilidades_getMesEnLetra(10);
 2 SELECT @mes;
 3 
 4 SELECT DISTINCT MONTH(fecha) as mesNumero, utilidades_getMesEnLetra(MONTH(fecha)) as mesCadena
 5 FROM ATRACCION_DIA
 6 WHERE nombre_atraccion='El gran felino'
 7 ORDER BY mesNumero;
 8 
 9 SELECT *
10 FROM ATRACCION_DIA
11 WHERE utilidades_getMesEnLetra(MONTH(fecha)) = 'ABRIL' COLLATE 'utf8mb4_spanish2_ci'
12 ORDER BY nombre_atraccion, fecha;



  • Ejercicio 2: Modifica un ejercicio anterior en el que creamos el procedimiento de nombre 'atracciones_getNumPorMes' y crea una función de nombre utilidades_getMesEnNumero a la que se le pase el nombre de un mes y devuelva el número que se corresponde con ese nombre. Si el mes no existe debe devolver -1. Modifica el procedimiento para que haga uso de la función.
 1 USE CIRCO;
 2 DROP FUNCTION IF EXISTS utilidades_getMesEnNumero;
 3 
 4 DELIMITER $$
 5 CREATE FUNCTION utilidades_getMesEnNumero (p_mes varchar(10))
 6 RETURNS INTEGER DETERMINISTIC NO SQL		 
 7     COMMENT 'Devuelve el número de mes que se corresponde con el nombre del mes'
 8 BEGIN
 9 	DECLARE v_mesEnNumero int default -1;		-- Valor por defecto de -1 para el caso de enviar un mes que no exista
10 
11 	CASE p_mes
12 		WHEN 'ENERO' THEN SET v_mesEnNumero = 1;
13 		WHEN 'FEBRERO' THEN	SET v_mesEnNumero = 2;
14 		WHEN 'MARZO' THEN SET v_mesEnNumero = 3;
15 		WHEN 'ABRIL' THEN SET v_mesEnNumero = 4;
16 		WHEN 'MAYO' THEN SET v_mesEnNumero = 5;
17 		WHEN 'JUNIO' THEN SET v_mesEnNumero = 6;
18 		WHEN 'JULIO' THEN SET v_mesEnNumero = 7;
19 		WHEN 'AGOSTO' THEN SET v_mesEnNumero = 8;
20 		WHEN 'SEPTIEMBRE' THEN SET v_mesEnNumero = 9;
21 		WHEN 'OCTUBRE' THEN SET v_mesEnNumero = 10;
22 		WHEN 'NOVIEMBRE' THEN SET v_mesEnNumero = 11;
23 		WHEN 'DICIEMBRE' THEN SET v_mesEnNumero = 12;
24 	END CASE;
25 	
26 	RETURN v_mesEnNumero;
27 END $$
28 DELIMITER ;

Ejemplo de uso: (modificamos el procedimiento)

 1 USE CIRCO;
 2 DROP FUNCTION IF EXISTS utilidades_getMesEnNumero;
 3 
 4 DELIMITER $$
 5 CREATE PROCEDURE `atracciones_getNumPorMesConFuncion`(p_mes varchar(10),p_ano smallint, OUT p_numAtracciones int)
 6     COMMENT 'Calcula la suma de todas las atracciones celebradas en el mes y año indicado y devolver el dato en un parámetro de salida'
 7 label_proc: BEGIN
 8     DECLARE v_mesEnNumero tinyint default 0;
 9     DECLARE v_dia tinyint default 1;	-- Día del mes. Usado para hacer el bucle hasta el día 31
10     DECLARE v_numAtraccPorDia int;
11 
12     SET p_numAtracciones = 0;		-- Por defecto vale NULL y no podríamos usarla para sumar las atracciones diarias
13     SET v_mesEnNumero = utilidades_getMesEnNumero(p_mes);
14     IF (v_mesEnNumero=-1) THEN
15         SET p_numAtracciones = -1;
16         LEAVE label_proc;		-- Sale del procedimiento
17     END IF;
18     
19     IF (p_ano IS NULL) THEN
20         SET p_ano = YEAR(curdate());
21     END IF;
22     
23     bucle_dia: WHILE (v_dia <= 31) DO
24         IF (DATE(CONCAT(p_ano,'-',v_mesEnNumero,'-',v_dia)) IS NULL) THEN	-- No es una fecha válida por meses que pueden tener menos de 31 dias
25             LEAVE bucle_dia;
26         END IF;
27 
28         SELECT COUNT(*)
29         INTO v_numAtraccPorDia
30         FROM ATRACCION_DIA
31         WHERE fecha = CONCAT(p_ano,'-',v_mesEnNumero,'-',v_dia);
32         
33         SET v_dia = v_dia+1;
34         SET p_numAtracciones = p_numAtracciones + v_numAtraccPorDia;
35     END WHILE bucle_dia;
36 
37 END$$
38 DELIMITER ;



  • Ejercicio 3: Crea una función de nombre animales_getEstadoPorAnhos que devuelva la cadena:
  • Si tipo = León
  • anhos < 2: 'JOVEN'
  • anhos >=2 y <=5: 'MADURO'
  • anhos > 5: 'VIEJO'
  • Cualquier otro tipo:
  • anhos < 1: 'JOVEN'
  • anhos >=1 y <=3: 'MADURO'
  • anhos > 3: 'VIEJO'
Llama a la función para mostrar el estado por años de cada uno de los animales del CIRCO.
 1 USE CIRCO;
 2 DROP FUNCTION IF EXISTS animales_getEstadoPorAnhos;
 3 DELIMITER $$
 4 CREATE FUNCTION animales_getEstadoPorAnhos (p_tipo varchar(9), p_anhos tinyint)	-- El tipo se corresponde con el de la tabla
 5 RETURNS CHAR(6)	DETERMINISTIC NO SQL
 6     COMMENT 'Devuelve una cadena indicativa de la edad en función de la edad y tipo de animal'
 7 BEGIN
 8 	DECLARE v_cadena char(6) default '';
 9 
10 	IF (p_tipo='León') THEN
11 		CASE 
12 			WHEN p_anhos < 2 THEN SET v_cadena = 'JOVEN';
13 			WHEN p_anhos >= 2 AND p_anhos <= 5 THEN SET v_cadena = 'MADURO';
14 			WHEN p_anhos > 5 THEN SET v_cadena = 'VIEJO';
15 		END CASE;
16     ELSE
17 		CASE
18 			WHEN p_anhos < 1 THEN SET v_cadena = 'JOVEN';
19 			WHEN p_anhos >= 1 AND p_anhos <= 3 THEN SET v_cadena = 'MADURO';
20 			WHEN p_anhos > 3 THEN SET v_cadena = 'VIEJO';
21 		END CASE;
22     END IF;
23 
24 	RETURN v_cadena;
25 END $$
26 DELIMITER ;

Ejemplo de uso:

1 SELECT *,animales_getEstadoPorAnhos(tipo,anhos) as estado
2 FROM ANIMALES
3 ORDER BY nombre;



  • Ejercicio 4: Crea una función de nombre pistas_getDiferenciaAforo al que se le pase el nuevo aforo de una pista y devuelva la diferencia entre el aforo nuevo y el aforo anterior.
Si la diferencia < 100 debe devolver la cadena 'PEQUEÑA menor que 100'
Si la diferencia está entre 100 y 500 debe devolver la cadena 'REGULAR entre 100 y 500'
Si la diferencia > 500 debe devolver la cadena 'ABISMAL mayor que 500'
Por ejemplo: PISTA1, 150 => Si la pista tiene actualmente un aforo de 100, debe devolver 150-100 = 50 => PEQUEÑA menor que 100
Si la pista no existe debe devolver null.
Acordaos de añadir los modificadores adecuados a la creación de la función.
Muestra los datos de todas las pistas junto la diferencia del aforo empleando la función anterior y enviando un aforo de 600.
Muestra las pistas que tengan una diferencia ABISMAL (busca la cadena con la función INSTR con un aforo propuesto de 1000.
Recuerda hacer uso de collate.
 1 USE CIRCO;
 2 DROP FUNCTION IF EXISTS pistas_getDiferenciaAforo;
 3 
 4 DELIMITER $$
 5 CREATE FUNCTION pistas_getDiferenciaAforo (p_nombrePista varchar(50), p_aforo smallint)	-- El tipo se corresponde con el de la tabla
 6 RETURNS varchar(100) READS SQL DATA 
 7     COMMENT 'Devuelve la diferencia entre el nuevo aforo y el antiguo'
 8 BEGIN
 9     DECLARE v_aforoAntiguo smallint default -1;
10     DECLARE v_cadena varchar(100);
11     DECLARE v_diferenciaAforo smallint default 0;
12 
13     SELECT aforo
14     INTO v_aforoAntiguo
15     FROM PISTAS
16     WHERE nombre = p_nombrePista;
17     
18     IF (v_aforoAntiguo=-1) THEN	-- La pista no existe
19         RETURN v_cadena;
20     END IF;
21     
22     SET v_diferenciaAforo = p_aforo-v_aforoAntiguo;
23     CASE 
24         WHEN v_diferenciaAforo < 100 THEN 
25             SET v_cadena = 'PEQUEÑA menor que 100';
26         WHEN v_diferenciaAforo >= 100 AND v_diferenciaAforo <= 500  THEN 
27             SET v_cadena = 'REGULAR entre 100 y 500';
28         WHEN v_diferenciaAforo > 500 THEN 
29         SET v_cadena = 'ABISMAL mayor que 500';
30     END CASE;
31     
32    RETURN v_cadena;
33 
34 END $$
35 DELIMITER ;

Ejemplo de uso:

1 SELECT *,pistas_getDiferenciaAforo(nombre,600) as estado
2 FROM PISTAS
3 ORDER BY nombre;
4 
5 
6 SELECT *
7 FROM PISTAS
8 WHERE INSTR(pistas_getDiferenciaAforo(nombre,1000),'ABISMAL' COLLATE 'utf8mb4_spanish2_ci')>0
9 ORDER BY nombre;



Enlace a la página principal de la UD7

Enlace a la página principal del curso




-- Ángel D. Fernández González -- (2020).