Stored procedures en MySQL

Disponibles desde MySQL 5.0 Son especialmente útiles:

• Cuando varias aplicaciones necesitan una misma consulta (así está centralizada)
• En entornos donde la seguridad es muy importante, para asegurar que todas las acciones se registran debidamente.

Se necesitan los permisos de CREATE ROUTINE, ALTER ROUNTINE y/o EXECUTE. Sintaxis:

CREATE PROCEDURE sp_name ([parameter[,...]]) [characteristic ...] routine_body

• parameter: [ IN | OUT | INOUT ] param_name type
• type: cualquier tipo válido de MySQL
• characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
• routine_body: comandos SQL válidos

Un procedimiento o función se considera “determinista” si siempre produce el mismo resultado para los mismos parámetros de entrada, y “no determinista” en cualquier otro caso (en el caso de un SELECT es complicado si no se filtra por PK). Para replicación, se debería usar la función NOW(). Las funciones RAND() no tienen porqué crear un resultado no determinista. Varias características proporcionan información sobre la naturaleza de los datos usados por la rutina. CONTAINS SQL indica que la rutina no contiene comandos que leen o escriben datos. NO SQL indica que la rutina no contiene comandos SQL . READS SQL DATA indica que la rutina contiene comandos que leen datos, pero no comandos que escriben datos. MODIFIES SQL DATA indica que la rutina contiene comandos que pueden escribir datos. CONTAINS SQL es el valor por defecto si no se dan explícitamente ninguna de estas características.

Ejemplo simple de procedimiento almacenado

Ejemplo con varias consultas

Si se quiere desarrollar un procedimiento almacenado genérico, pueden utilizarse condiciones para modificar su comportamiento y adaptarlo a varios propósitos:

Ejemplo: Si el parámetro de entrada es 0, retorna todos los registros, en caso contrario filtra.

La principal ventaja del uso de SP es que la base de datos puede preparar un plan de ejecución óptimo :

• No se usará un plan “cualquiera”.
• El plan de ejecución ya está calculado, reduciendo el tiempo de gestión de la consulta.

¡Cuidado!

• Usar nombres autoexplicativos.
• No están soportados por la mayoría de softwares tipo phpmyadmin. MySQL recomienda el uso de la consola.
• Es muy importante no implementar la lógica de negocio dentro del stored procedure .

Nos vemos en el siguiente artículo!