A partir de la versión 5.0 de Mysql disponemos de nuevas funcionalidades denominadas Rutinas formadas por procedimientos almacenados y funciones.
Estas funcionalidade nos permiten agrupar un conjunto de comandos o consultas mysql bajo un nombre que puede ser invocado desde cualquier consulta.
Especialmente interesante me parece el uso de funciones cuando necesitamos optimizar consultas especialmente complejas o cuyos subconjuntos pueden ser operados indivialmente.
La sintaxis de la definición de las rutinas es la siguiente:
CREATE PROCEDURE sp_name ([parameter[,...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([parameter[,...]])
RETURNS type
[characteristic ...] routine_body
parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
procedimientos almacenados o comandos SQL válidos
Una rutina una vez definida queda registrada en la base de datos, de forma similar a como lo hacen las tablas o los disparadores.
Para incluir una rutina mediante una consulta SQL es necesario incluir la definición de delimitadores y el definidor (ámbito sobre el que se aplica).
Otra forma de incluirla es desde los propios sistemas de gestión como phpmyadmin, o aplicaciones externas como HeidiSql, que facilitan su creación.
Veamos una reciente implementanción de una función que me permitía incluir nuevos datos en una consulta suficientemente compleja y pesada, con la mínima carga adicional.
El problema:
Se trataba de incluir en el listado de pedidos de un Prestashop 1.6, una nueva columna con los valores de los atributos de los productos que contenían cada pedido.
La inclusión de nuevas tablas en la consulta original mediantela inclusión de nuevos joins (al menos 3) sobredimensionaría y disparaía los tiempos de carga. Se hacía necesaria afrontarlo con el uso de una función que pasando el id_order devolviera ya procesados los datos necesarios.
La función:
DELIMITER $$
CREATE DEFINER=`[user]`@`localhost`
FUNCTION `getWarehouse`(`id_order` INT(11)) RETURNS varchar(255) CHARSET latin1
READS SQL DATA
return (
SELECT GROUP_CONCAT(at.name," ") as warehouse
FROM `ps_order_detail` d
LEFT JOIN `ps_product_attribute_combination` c
ON c.id_product_attribute= d.product_attribute_id
LEFT JOIN `ps_attribute_lang` att
ON at.id_attribute= c.id_attribute AND att.id_lang=1
WHERE d.id_order = id_order
)$$
DELIMITER ;
La solución:
Nos bastaba incluir en la función original un nuevo campo, para disponer de la información necesaria:
SELECT …, getWarehouse(a.id_order) as warehouse, … FROM …
Con esta solución la consulta realizada dentro de nuestra función está reducida a la unión de tablas para uno valores muy limitado (un único pedido), a diferencia de sobrecargar la consulta original con joins que trabajarían sobre todo el espacio de valores de pedidos del sistema.
En conclusión, el uso de funciones y procedimientos almacenados, nos puede permitir disponer en cualquier consulta de datos adicionales que nuestro proyecto requiera asíduamente, u optimizar y reducir el espacio de valores de consultas existentes.
Referencias: https://manuales.guebs.com/mysql-5.0/stored-procedures.html