Procedimiento MySQL vs función, ¿cuál usaría y cuándo?

Resuelto Anonym asked hace 14 años • 7 respuestas

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

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

Probablemente me equivoque, pero parece que un procedimiento puede hacerlo todo y más que una función. ¿Por qué/cuándo debería utilizar un procedimiento frente a una función?

Anonym avatar Sep 19 '10 08:09 Anonym
Aceptado

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. Se invoca una función dentro de una expresión y devuelve un valor único directamente a la persona que llama para usarlo en la expresión.
  3. No puede invocar una función con una declaración CALL, ni puede invocar un procedimiento en una expresión.

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

  1. Los parámetros del procedimiento se pueden definir como solo de entrada, solo de salida o ambos. Esto significa que un procedimiento puede devolver valores a la persona que llama utilizando parámetros de salida. Se puede acceder a estos valores en declaraciones que siguen a la declaración CALL. Las funciones solo tienen parámetros de entrada. Como resultado, aunque tanto los procedimientos como las funciones pueden tener parámetros, la declaración de parámetros del 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 de retorno. Además, debe haber al menos una declaració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, utilice el archivo CALL statement. Para invocar una función almacenada, consúltela en una expresión. La función devuelve un valor durante la evaluación de la expresión.

    • Un procedimiento se invoca mediante una instrucción CALL y solo puede devolver valores utilizando variables de salida. Se puede llamar a una función desde dentro de una declaració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 parámetros IN.

    Si no se proporciona ninguna palabra clave antes del nombre de un parámetro, es un parámetro IN de forma predeterminada. Los parámetros de 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 almacenado o una función, utilice CREAR PROCEDIMIENTO o CREAR FUNCIÓN 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 procedimientos almacenados (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 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 particular, al igual que las tablas o vistas. Cuando elimina una base de datos, también se eliminan todas 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 los procedimientos almacenados se puede utilizar SQL dinámico, pero no en funciones o activadores.

Las declaraciones preparadas de SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) se pueden utilizar en procedimientos almacenados, pero no en funciones ni activadores almacenados. Por lo tanto, las funciones almacenadas y los desencadenadores no pueden usar SQL dinámico (donde se construyen declaraciones como cadenas y luego se ejecutan). (SQL dinámico en rutinas almacenadas MySQL)

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

  1. ( Este punto está copiado de una publicación de blog ) . El procedimiento almacenado es un plan de ejecución precompilado, mientras que las funciones no lo son. Función Analizada y compilada en tiempo de ejecución. Procedimientos almacenados, almacenados como pseudocódigo en una base de datos, es decir, en forma compilada.

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

  3. Las funciones normalmente se usan para cálculos, mientras que los procedimientos normalmente se usan para ejecutar la lógica empresarial.

  4. Las funciones no pueden afectar el estado de la base de datos (las declaraciones que realizan una confirmación o reversión explícita o implícita no están permitidas en la función) Mientras que los procedimientos almacenados pueden afectar el estado de la base de datos mediante la confirmación, etc.
    referencia: J.1. Restricciones sobre activadores y rutinas almacenadas

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

  6. Las funciones almacenadas no pueden ser recursivas, mientras que los procedimientos almacenados sí pueden serlo. Nota: Los procedimientos almacenados recursivos están deshabilitados de forma predeterminada, pero se pueden habilitar en el servidor configurando 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 o disparador almacenado, 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 el disparador. Buen ejemplo: ¿Cómo actualizar la misma tabla al eliminarla en MYSQL?

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

Grijesh Chauhan avatar Nov 22 '2012 10:11 Grijesh Chauhan

No puede mezclar procedimientos almacenados con SQL ordinario, mientras que con funciones almacenadas sí 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.

nos avatar Sep 19 '2010 01:09 nos

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

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 proceso:

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)
Daniel Vassallo avatar Sep 19 '2010 01:09 Daniel Vassallo

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

Se ejecuta un procedimiento mediante la consulta CALL.

Evert avatar Sep 19 '2010 01:09 Evert