Funciones almacenadas en MySQL

Las funciones almacenadas en MySQL, se utilizan para encapsular realizar cálculos u operaciones con registros y campos de datos quye se toman de una consulta SQL y son tareas comunes o reglas de negocio.

 

Una gran ventaja es que son reutilizables y el lenguaje de programación en que se desarrollan las funciones es mediante sentencias SQL y estructuras condicionales o repetitivas.

 

A diferencia de un procedimiento almacenado, se puede utilizar una función almacenada en sentencias SQL donde se utiliza una expresión que permite crear reglas condicionales.

 

Veamos un ejemplo creamos una base de datos de un colegio:

CREATE DATABASE `colegio`

A continuación creamos una tabla con notas de exámenes de alumnos

--
-- Estructura de tabla para la tabla `notasexamenes`
--

CREATE TABLE `notasexamenes` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`idalumno` INT(11) NOT NULL,
`idmateria` INT(11) NOT NULL,
`nota1` DECIMAL(10,2) NOT NULL,
`nota2` DECIMAL(10,2) NOT NULL,
`nota3` DECIMAL(10,2) NOT NULL,
`promedio` DECIMAL(10,2) NOT NULL,
`estado` VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

-- Indices de la tabla `notasexamenes`
--
ALTER TABLE `notasexamenes`
  ADD PRIMARY KEY (`id`);

A continuación añadimos algunos datos a la tabla notasexamenes

-- Volcado de datos para la tabla `notasexamenes`

INSERT INTO `notasexamenes` (`id`, `idalumno`, `idmateria`, `nota1`, `nota2`, `nota3`, `promedio`, `estado`) VALUES
(1, 1000, 1, '8.00', '9.25', '7.00', '0.00', ''),
(2, 1001, 1, '6.33', '8.50', '8.00', '0.00', ''),
(3, 1002, 1, '10.00', '7.50', '8.33', '0.00', ''),
(4, 1003, 2, '4.50', '2.00', '5.50', '0.00', ''),
(5, 1004, 1, '3.50', '2.00', '4.00', '0.00', '');

 

A continuación crearemos una función almacenada que recorrerá toda la tabla de notas y calculará el promedio de notas de cada alumno por materia y actualizara la tabla para indicar si esta Aprobado o Desaprobado.

 

Podemos crear la funcion desde Phpmyadmin o cualquier editor que permita escribir código SQL;

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `calcularpromedio`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

/* Declaro variables para la funcion */

  DECLARE final BOOL DEFAULT FALSE;
  DECLARE id INT;
  DECLARE alumno INT;
  DECLARE materia INT;
  DECLARE nota1 FLOAT;
  DECLARE nota2 FLOAT;
  DECLARE nota3 FLOAT;
  DECLARE prom FLOAT;
/* Declaro un recordset o cursor con los datos de la consulta sql  */
  DECLARE rslista CURSOR FOR SELECT id , idalumno , idmateria , nota1 , nota2 , nota3 from notasexamenes;

/* declaro una variable para detectar el final de un bucle repetitivo
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET final= TRUE;
  /* Abro el recordset para inciar el recorrido de los datos con un bucle */
   OPEN rslista; bucle: LOOP

   FETCH rslista INTO id , alumno , materia , nota1, nota2 , nota3;

/* Calculo el promedio */
	 set prom = (nota1 + nota2 + nota3)/3;
/* Lo guardo actualizando la tabla */
	 update notasexamenes set promedio=d where idalumno=alumno and idmateria=materia;
	
/* Si la nota es mayor o igual a 7 actualizo el estado como Aprobado
   sino el estado sera Desaprobado */
	 IF prom>=7 THEN
	 update notasexamenes set estado='Aprobado' where idalumno=alumno and idmateria=materia;
	 ELSE
	  update notasexamenes set estado='Desaprobado' where idalumno=alumno and idmateria=materia;
	 END IF;
 
	 IF finalTHEN
	  CLOSE rslista;
	  LEAVE bucle;
	 END IF;

   END LOOP;
  
   END

A continuación podemos ejecutar la función mediante el siguiente comando:

CALL `calcularpromedio`()

El resultado será la actualización de las columnas promedio y estado en forma automática.

 

 

Las funciones almacenadas en MySQL se utilizan normalmente para cálculos y operaciones, mientras que los procedimientos almacenados se utilizan normalmente para la ejecución de la reglas de negocio.

 

Las funciones no suelen afectar a la estructura de la base de datos, sino para realizar algún calculo, comparar y devolver un resultado o modificar un dato en una tabla de la base de datos, también podemos crear un trigger o disparador para que controle o audite los cambios que hace una función.