Luego de de una extensa compilación de información dimos con la respuesta este dilema que pueden tener ciertos de nuestros usuarios. Te regalamos la respuesta y nuestro objetivo es resultarte de gran apoyo.
Solución:
La diferencia más general entre procedimientos y funciones es que se invocan de manera diferente y para diferentes propósitos:
- 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.
- Una función se invoca dentro de una expresión y devuelve un único valor directamente al llamador para que lo utilice en la expresión.
- 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 rutinas difiere algo para los procedimientos y funciones:
- Los parámetros de procedimiento se pueden definir como solo entrada, solo salida o ambos. Esto significa que un procedimiento puede devolver valores al llamador 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 tanto los procedimientos como las funciones pueden tener parámetros, la declaración de parámetros de procedimiento difiere de la de las funciones.
-
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 al llamador. RETURNS y RETURN no aparecen en las definiciones de los procedimientos.
-
Para invocar un procedimiento almacenado, use el
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 mediante variables de salida. Una función se puede llamar 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 una palabra clave antes del nombre de un parámetro, es un parámetro IN por defecto.
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 una función o procedimiento almacenado, 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 procedimiento almacenado (no funciones) es que un procedimiento puede generar un conjunto de resultados, o incluso varios conjuntos de resultados, que el llamador 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.
Rutinas almacenadas (refiriéndose tanto a los procedimientos almacenados como a las funciones almacenadas) están asociados con una base de datos en particular, como tablas o vistas. Cuando descarta una base de datos, también se descartan 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 disparadores.
Las sentencias preparadas de SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) se pueden usar en procedimientos almacenados, pero no en funciones o disparadores almacenados. Por lo tanto, las funciones y los disparadores almacenados no pueden usar SQL dinámico (donde usted construye declaraciones como cadenas y luego las ejecuta). (SQL dinámico en rutinas almacenadas de MySQL)
Algunas diferencias más interesantes entre FUNCIÓN y PROCEDIMIENTO ALMACENADO:
-
(Este punto está copiado de una publicación de blog.) El procedimiento almacenado es un plan de ejecución precompilado donde las funciones no lo son. Función Analizada y compilada en tiempo de ejecución. Procedimientos almacenados, almacenados como un pseudocódigo en la base de datos, es decir, en forma compilada.
-
(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. referencia -
Las funciones se utilizan normalmente para cálculos, mientras que los procedimientos se utilizan normalmente para ejecutar la lógica empresarial.
-
Funciones No pueden afectar el estado de la base de datos (las declaraciones que hacen una confirmación o una 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 -
Las funciones no pueden usar declaraciones FLUSH, mientras que los procedimientos almacenados pueden hacerlo.
-
Las funciones almacenadas no pueden ser recursivas, mientras que los procedimientos almacenados pueden serlo. Nota: Los procedimientos almacenados recursivos están deshabilitados de forma predeterminada, pero se pueden habilitar en el servidor estableciendo la variable de 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.
-
Dentro de una función o desencadenador almacenado, no se permite modificar una tabla que ya está siendo utilizada (para leer o escribir) por la declaración que invocó la función o desencadenador. Buen ejemplo: ¿Cómo actualizar la misma tabla al eliminar en MYSQL?
Nota: que aunque algunas restricciones normalmente se aplican a funciones almacenadas y desencadenadores, pero no a procedimientos almacenados, esas restricciones se aplican a procedimientos almacenados si se invocan desde dentro de 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 desencadenador.
No puede mezclar procedimientos almacenados con SQL ordinario, mientras que con la función almacenada sí puede hacerlo.
p.ej 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.
Una diferencia significativa es que puede incluir una función en sus consultas SQL, pero los procedimientos almacenados solo se pueden invocar con el CALL declaració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 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)