stored - MySQL almacenado procedimiento vs función, ¿cuál usaría cuando?

mysql workbench stored procedure

Question

Estoy mirando los procedimientos y funciones almacenados de MySQL. ¿Cuál es la diferencia real?

Parecen ser similares, pero una función tiene más limitaciones.

Probablemente estoy equivocado, pero parece que un procedimiento almacenado puede hacer de todo y más puede hacerlo una función almacenada. ¿Por qué / cuándo usaría un procedimiento frente a una función?

Answer 1

No puede mezclar procedimientos almacenados con SQL ordinario, mientras que con la función almacenada puede hacerlo.

por ejemplo, SELECT get_foo(myColumn) FROM mytable no es válido si get_foo() es un procedimiento, pero puede hacerlo si get_foo() es una función. El precio es que las funciones tienen más limitaciones que un procedimiento.

Answer 2

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

Ejemplo de UDF:

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)

Answer 3

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

Un procedimiento se ejecuta utilizando la consulta CALL.

Answer 4

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 cambio, se invoca con una instrucción CALL para realizar una operación como modificar una tabla o procesar registros recuperados.
2. Una función se invoca dentro de una expresión y devuelve un único valor directamente a la persona que llama para ser utilizada en la expresión.
3. No puede invocar una función con una instrucción CALL, ni puede invocar un procedimiento en una expresión.

La sintaxis para la creación de rutina difiere un tanto para los procedimientos y funciones:

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

2. Las funciones devuelven 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 a la persona que llama. DEVOLUCIONES y DEVOLUCIÓN no aparecen en las definiciones de procedimientos.

   • Para invocar un procedimiento almacenado, use la CALL statement . 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.

   • Un procedimiento se invoca usando una instrucción CALL, y solo puede devolver valores usando variables de salida. Se puede llamar a una función desde dentro de una instrucción 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 una FUNCIÓN, los parámetros siempre se consideran como parámetros IN.

   Si no se da una palabra clave antes del nombre de un 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 funciones se tratan como parámetros IN.

Para definir un procedimiento o función almacenada, use 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 de MySQL para el procedimiento almacenado (no funciones) es que un procedimiento puede generar un conjunto de resultados, o incluso múltiples conjuntos de resultados, que la persona que llama procesa de la misma manera que el resultado de una instrucción SELECT. Sin embargo, el contenido de dichos conjuntos de resultados no se puede usar 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 particular, al igual que las tablas o las vistas. Cuando suelta una base de datos, cualquier rutina almacenada en la base de datos también se descarta.

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 preparadas de SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) se pueden usar en procedimientos almacenados, pero no en funciones almacenadas o disparadores. Por lo tanto, las funciones almacenadas y desencadenantes no pueden usar SQL dinámico (donde se construyen enunciados como cadenas y luego se ejecutan). (SQL dinámico en rutinas almacenadas de MySQL)

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

1. ( Este punto se copia de una publicación de blog ) . El procedimiento almacenado es un plan de ejecución precompilado donde las funciones no lo son. Función Parsed y compilado en tiempo de ejecución. Procedimientos almacenados, almacenados como un pseudocódigo en la base de datos, es decir, formulario compilado.

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

3. Las funciones se usan normalmente para cálculos donde los procedimientos se usan normalmente para ejecutar la lógica comercial.

4. Funciones No puede afectar el estado de la base de datos (las declaraciones que hacen commit o rollback explícito o implícito no están permitidas en la función) Mientras que los procedimientos almacenados pueden afectar el estado de la base de datos usando commit etc.
   refrence: J.1. Restricciones sobre rutinas almacenadas y desencadenantes

5. Las funciones no pueden usar instrucciones FLUSH , mientras que los procedimientos almacenados pueden hacerlo.

6. Las funciones almacenadas no pueden ser recursivas, mientras que los procedimientos almacenados pueden ser. Nota: Los procedimientos almacenados recursivos están deshabilitados de manera 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.

7. Dentro de una función almacenada o disparador, no está permitido modificar una tabla que ya está siendo utilizada (para lectura o escritura) por la declaración que invocó la función o disparador. Buen ejemplo: ¿Cómo actualizar la misma tabla al eliminar en MYSQL?

Nota : aunque algunas restricciones normalmente se aplican a las funciones almacenadas y desencadenadores pero no a los procedimientos almacenados, esas restricciones se aplican a los procedimientos almacenados si se invocan desde una función almacenada o activador. Por ejemplo, aunque puede usar FLUSH en un procedimiento almacenado, dicho procedimiento almacenado no puede invocarse desde una función almacenada o activador.