MySQL: Procedimientos Almacenados VS Funciones

Desde MySQL 5.0. podemos usar nuevas funcionalidades como son los Procedimientos Almacenados y las Funciones.

Un procedimiento almacenado es un conjunto de comandos SQL que pueden ser almacenados en el servidor. Al realizarse, los clientes no necesitan realizar los comandos individuales en su lugar pueden reutilizar los ya almacenados.

Por otro lado tenemos las funciones que al igual que los procedimientos almacenados, están formadas por un conjunto de comandos SQL y están almacenados en la base de datos.

Ambas parecen ser similares, aunque una función parece ser más limitada. ¿Cuándo y porqué usar un procedimiento y cuando una función?

Las diferencias más generales entre procedimientos y funciones es que cada una es llamada de modo diferente y para propósitos distintos:

1. Un procedimiento no nos regresa un valor. En su lugar, es llamado con una declaración CALL para realizar una operación como modificar una tabla o procesar la recuperación de registros.

2. Una función es llamada dentro de una expresión y nos regresa un valor único directamente al que lo llama para ser utilizado en la expresión.

3. No se puede invocar una función con una instrucción CALL, ni puedes invocar un procedimiento en una expresión.

(Tambien puede interesar: Cómo explicar la Inyección SQL sin Términos Técnicos)

La sintaxis para la creación de rutinas es algo diferente para procedimientos y funciones:

1. Los parámetros de procedimiento se pueden definir como sólo de entrada, sólo de salida o ambos. Esto significa que un procedimiento puede devolver valores al que los llama usando parámetros de salida. Se pueden acceder a estos valores en sentencias que siguen a la instrucción CALL. Las funciones sólo tienen parámetros de entrada. Como resultado, aunque ambos procedimientos y funciones pueden tener parámetros, la declaración de parámetros de procedimiento difiere de la de funciones.

2. Las funciones devuelven valores, por lo que debe haber una cláusula RETURNS en la definición de función para indicar el tipo de datos del valor que se regresara. Además, debe haber al menos una declaración RETURN dentro del cuerpo de la función para devolver un valor al que llama. RETURNS y RETURN no aparecen en las definiciones de los procedimientos.

a) Para invocar un procedimiento almacenado, utilice la instrucción CALL. Para invocar una función almacenada, refiérase a ella en una expresión. La función devuelve un valor durante la evaluación de la expresión.

b) Un procedimiento se invoca mediante una instrucción CALL y sólo puede devolver valores utilizando variables de salida. Una función puede ser llamada desde dentro de una sentencia como cualquier otra función (es decir, invocando el nombre de la función), y puede devolver un valor escalar.

c) Especificar un parámetro como IN, OUT o INOUT sólo es válido para PROCEDIMIENTO. Para una FUNCIÓN, los parámetros siempre se consideran parámetros IN. Si no se da ninguna palabra clave antes de un nombre de parámetro, es un parámetro IN por defecto. Los parámetros para las funciones almacenadas no están precedidos por IN, OUT o INOUT. Todos los parámetros de función se tratan como parámetros IN.

Para definir un procedimiento o una función almacenada, utilice CREATE PROCEDURE o CREATE FUNCTION respectivamente:

CREATE PROCEDURE proc_name ([parameters])
 [characteristics]
 routine_body


CREATE FUNCTION func_name ([parameters])
 RETURNS data_type       //diferente
 [characteristics]
 routine_body

Una extensión de MySQL para el procedimiento almacenado (no para las funciones) es que un procedimiento puede generar un conjunto de resultados, o incluso varios conjuntos de resultados, que el que llama procesa de la misma manera que el resultado de una sentencia SELECT. Sin embargo, el contenido de tales conjuntos de resultados no se puede utilizar directamente en la expresión.

Las rutinas almacenadas (que se refieren tanto a procedimientos almacenados como a funciones almacenadas) están asociadas con una base de datos determinada, al igual que tablas o vistas. Cuando se suelta una base de datos, también se eliminan las rutinas almacenadas en la base de datos.

Los procedimientos y funciones almacenados no comparten el mismo espacio de nombres. Es posible tener un procedimiento y una función con el mismo nombre en una base de datos.

En Procedimientos almacenados se puede utilizar SQL dinámico pero no en funciones o disparadores.

Nota : Las instrucciones preparadas de SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) se pueden utilizar en procedimientos almacenados, pero no en funciones almacenadas o disparadores. Por lo tanto, las funciones almacenadas y disparadores no pueden utilizar Dynamic SQL (donde se construyen las sentencias como cadenas y luego se ejecutan).

(También te puede interesar: MySQL / MariaDB: ¿Es Mejor Usar datetime O timestamp?)

Otras diferencias interesantes entre la FUNCIÓN y el PROCEDIMIENTO ALMACENADO:

1. El procedimiento almacenado es un plan de ejecución precompilado en el que no funcionan las funciones. Función analizada y compilada en tiempo de ejecución. Procedimientos almacenados, almacenados como un pseudo-código en la base de datos, es decir, en forma compilada.
2. Normalmente, las funciones se utilizan para cálculos en los que normalmente se utilizan procedimientos para ejecutar la lógica de negocio.
3. Las Funciones, no pueden afectar el estado de la base de datos (las declaraciones que hacen commit explícita o implícitamente al rollback no son permitidas en la función) Considerando que los Procedimientos Almacenados pueden afectar el estado de la base de datos mediante commit etc. Referencia: J.1. Restricciones sobre las rutinas almacenadas y los desencadenantes
4. Las funciones no pueden usar instrucciones FLUSH mientras que los procedimientos almacenados si.
5. Las funciones almacenadas no pueden ser recursivas considerando que los procedimientos almacenados si lo pueden ser. Nota: Los procedimientos almacenados recursivos están desactivados de forma predeterminada, pero se pueden habilitar en el servidor estableciendo la variable del sistema del servidor max_sp_recursion_depth en un valor distinto de cero. Consulte la Sección 5.2.3, "Variables del sistema" , para obtener más información.
6. Dentro de una función o disparador almacenado, no está permitido modificar una tabla que ya está siendo utilizada (para leer o escribir) por la sentencia que invocó la función o disparador. Un buen Ejemplo: ¿Cómo actualizar la misma tabla en la eliminación en MYSQL?

Nota : Aunque algunas restricciones normalmente se aplican a las funciones y desencadenadores almacenados pero no a los procedimientos almacenados, esas restricciones se pueden aplicar a los procedimientos almacenados si se invocan desde una función o disparador almacenado. Por ejemplo, aunque puede utilizar FLUSH en un procedimiento almacenado, dicho procedimiento almacenado no puede ser llamado desde una función o disparador almacenado.

CONCLUSIÓN

Aunque puede parecer que los Procedimientos Almacenados pueden hacer lo mismo o hasta más que una Función cada una tiene sus propias limitantes y usos.

Los usos típicos para Procedimientos Almacenados es la validación de datos, integrados dentro de la estructura de la base de datos , la "encapsulación" de un API para un proceso complejo que podría requerir la ejecución de varias consultas SQL, tales como la manipulación de un conjunto de datos enormes para producir un resultado resumido y también pueden ser usados para el control de gestión de operaciones.

Mientras que para las Funciones se usan cuando se necesita recibir un único valor simple que se obtiene de una operación recurrente.

¿Qué otros usos imaginas que les puedes dar a ambas funcionalidades? Dejalo en los comentarios.