Procedimiento almacenado de MySQL vs función, ¿cuál usaría cuando?

La diferencia más general entre procedimientos y funciones es que se invocan de manera diferente y para diferentes propósitos:

1. Un procedimiento no devuelve un valor. En su lugar, se invoca con una instrucción CALL para realizar una operación como modificar una tabla o procesar registros recuperados.
2. Se invoca una función dentro de una expresión y devuelve un solo valor directamente al llamador que se utilizará en la expresión.
3. No se puede invocar una función con una instrucción CALL, tampoco puede invocar un procedimiento en una expresión.

La sintaxis para la creación de rutinas difiere un poco para procedimientos y funciones:

1. Los parámetros del procedimiento se pueden definir como solo entrada, solo salida o ambos. Esto significa que un procedimiento puede pasar valores de nuevo a la persona que llama mediante el uso de parámetros de salida. Se puede acceder a estos valores en instrucciones que siguen a la instrucción CALL. Las funciones solo tienen parámetros de entrada. Como resultado, aunque ambos los procedimientos y las funciones pueden tener parámetros, la declaración de parámetros del procedimiento difiere de la de las funciones.

2. Las funciones devuelven el valor, por lo que debe haber una cláusula RETURNS en una definición de función para indicar el tipo de datos del valor devuelto. Además, debe haber al menos una instrucción RETURN dentro del cuerpo de la función para devolver un valor al llamador. DEVOLUCIONES y DEVOLUCIONES no aparecen en las definiciones de procedimiento.

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

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

   • Especificar un parámetro como IN, OUT o INOUT es válido solo para un PROCEDIMIENTO. Para un FUNCIÓN, los parámetros se consideran siempre como EN parámetros.

   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 la función se tratan como EN parámetros.

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

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


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

Una extensión MySQL para el procedimiento almacenado (no funciones) es que un procedimiento puede generar un conjunto de resultados, o incluso varios conjuntos de resultados, que el llamante procesa de la misma manera que el resultado de una instrucción 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 los procedimientos almacenados como a las funciones almacenadas) están asociadas con una base de datos en particular, al igual que las tablas o las vistas. Cuando se suelta una base de datos, cualquier rutina almacenada en la base de datos también son caer.

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 los procedimientos almacenados se puede usar SQL dinámico, pero no en funciones o disparadores.

Las sentencias SQL prepared (PREPARE, EXECUTE, DEALLOCATE PREPARE) se pueden usar en procedimientos almacenados, pero no en funciones o disparadores almacenados. Por lo tanto, las funciones almacenadas y los disparadores no pueden usar SQL dinámico (donde construyes sentencias como cadenas y luego las ejecutas). (SQL dinámico en rutinas almacenadas en MySQL)

Algunas diferencias más interesantes entre la FUNCIÓN y el PROCEDIMIENTO ALMACENADO:

1. (Este punto es copiado de un blogpost.) El procedimiento almacenado es un plan de ejecución precompilado donde las funciones as no lo son. 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, compilados forma.

2. (No estoy seguro para este punto.)
   El procedimiento almacenado tiene la seguridad y reduce la red el tráfico y también podemos llamar procedimiento almacenado en cualquier no. de aplicaciones a la vez. referencia

3. Las funciones se utilizan normalmente para los cálculos donde como los procedimientos se utilizan normalmente para ejecutar la lógica de negocio.

4. Las funciones no pueden afectar el estado de la base de datos (Declaraciones que hacen confirmación explícita o implícita o la reversión no está permitida en la función) Mientras Los procedimientos almacenados Pueden afectar el estado de la base de datos usando commit, etc.
   referencia: J. 1. Restricciones en Rutinas y Disparadores Almacenados

5. Las funciones no pueden usar sentencias FLUSH mientras que los procedimientos almacenados sí pueden hacerlo.

6. Las funciones almacenadas no pueden ser recursivas, mientras que los procedimientos almacenados sí. Nota: Los procedimientos almacenados recursivos están deshabilitados de forma predeterminada, pero se pueden habilitar en el servidor max_sp_recursion_depth server variable del sistema a un valor distinto de cero. Consulte Sección 5.2.3, "Variables del sistema", para obtener más información.

7. Dentro de una función o disparador almacenado, no está permitido modificar una tabla que ya se está utilizando (para leer o escribir) por la instrucción que invocó la función o disparador. Buen ejemplo: ¿Cómo actualizar la misma tabla en la eliminación en MYSQL?

Nota: que aunque algunas restricciones normalmente aplicar a funciones y disparadores almacenados pero no a procedimientos almacenados, esas restricciones se aplican a procedimientos almacenados si se invocan desde dentro de una función o disparador almacenado. Por ejemplo, aunque puede usar FLUSH en un procedimiento almacenado, no se puede llamar a dicho procedimiento almacenado desde una función o disparador almacenado.

Una diferencia significativa es que puede incluir una función en sus consultas SQL, pero los procedimientos almacenados solo se pueden invocar con CALL declaración:

UDF Ejemplo:

CREATE FUNCTION hello (s CHAR(20))
   RETURNS CHAR(50) DETERMINISTIC
   RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE names (id int, name varchar(20));
INSERT INTO names VALUES (1, 'Bob');
INSERT INTO names VALUES (2, 'John');
INSERT INTO names VALUES (3, 'Paul');

SELECT hello(name) FROM names;
+--------------+
| hello(name)  |
+--------------+
| Hello, Bob!  |
| Hello, John! |
| Hello, Paul! |
+--------------+
3 rows in set (0.00 sec)

Ejemplo de Sproc:

delimiter //

CREATE PROCEDURE simpleproc (IN s CHAR(100))
BEGIN
   SELECT CONCAT('Hello, ', s, '!');
END//
Query OK, 0 rows affected (0.00 sec)

delimiter ;

CALL simpleproc('World');
+---------------------------+
| CONCAT('Hello, ', s, '!') |
+---------------------------+
| Hello, World!             |
+---------------------------+
1 row in set (0.00 sec)

Se puede usar una función almacenada dentro de una consulta. Luego podría aplicarlo a cada fila, o dentro de una cláusula WHERE.

Se ejecuta un procedimiento usando la consulta de llamada.

El procedimiento almacenado se puede llamar recursivamente pero la función almacenada no puede