MySQL存储过程vs函数，什么时候使用?

I'm looking at MySQL stored procedures and function. What is the real difference?

我正在查看MySQL存储过程和函数。真正的区别是什么?

They seem to be similar, but a function has more limitations.

它们看起来很相似，但是函数有更多的限制。

I'm likely wrong, but it seems a stored procedure can do everything and more a stored function can. Why/when would I use a procedure vs a function?

我可能错了，但是存储过程似乎可以做任何事情，而存储函数则可以。为什么/什么时候我会使用过程而不是函数?

4 个解决方案

#1

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.

您不能将存储过程与普通SQL混合，而使用存储函数则可以。

e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.

例如，如果get_foo()是一个过程，那么从mytable中选择get_foo(myColumn)是无效的，但是如果get_foo()是一个函数，则可以这样做。代价是函数比过程有更多的限制。

#2

210

The most general difference between procedures and functions is that they are invoked differently and for different purposes:

程序和函数之间最普遍的区别是它们的调用方式不同，用途也不同:

A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.
过程不返回值。相反，它通过调用语句来执行操作，比如修改表或处理检索到的记录。
A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
调用一个函数在一个表达式并向调用者返回一个值直接在表达式中使用。
You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.
不能使用CALL语句调用函数，也不能在表达式中调用过程。

Syntax for routine creation differs somewhat for procedures and functions:

例程创建的语法在过程和函数上有所不同:

Procedure parameters can be defined as input-only, output-only, or both. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement. Functions have only input parameters. As a result, although both procedures and functions can have parameters, procedure parameter declaration differs from that for functions.
过程参数可以定义为仅输入、仅输出或两者。这意味着一个过程可以使用输出参数将值传回给调用者。可以在CALL语句后面的语句中访问这些值。函数只有输入参数。因此，虽然过程和函数都可以有参数，但是过程参数声明与函数不同。
Functions return value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller. RETURNS and RETURN do not appear in procedure definitions.

函数返回值，因此函数定义中必须有一个return子句来指示返回值的数据类型。此外，函数体中必须至少有一个返回语句，以向调用者返回一个值。返回和返回不会出现在过程定义中。
- To invoke a stored procedure, use the CALL statement. To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.
  
  要调用存储过程，请使用CALL语句。要调用一个存储函数，请在表达式中引用它。函数在表达式求值期间返回一个值。
- A procedure is invoked using a CALL statement, and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value.
  
  使用CALL语句调用过程，并且只能使用输出变量返回值。可以像调用其他函数(即通过调用函数名)一样从语句内部调用函数，并可以返回标量值。
- Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.
  
  将参数指定为IN、OUT或INOUT仅对过程有效。对于函数，参数总是被视为参数。
If no keyword is given before a parameter name, it is an IN parameter by default. Parameters for stored functions are not preceded by IN, OUT, or INOUT. All function parameters are treated as IN parameters.

如果在参数名之前没有给出关键字，默认情况下它是一个IN参数。存储函数的参数前面没有IN、OUT或INOUT。所有的函数参数都被当作参数来处理。

To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION respectively:

要定义存储过程或函数，分别使用CREATE procedure或CREATE function:

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


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

A MySQL extension for stored procedure (not functions) is that a procedure can generate a result set, or even multiple result sets, which the caller processes the same way as the result of a SELECT statement. However, the contents of such result sets cannot be used directly in expression.

存储过程(而非函数)的MySQL扩展是一个过程可以生成一个结果集，甚至多个结果集，调用者以与SELECT语句相同的方式处理这些结果集。但是，这些结果集的内容不能直接用于表达式中。

Stored routines (referring to both stored procedures and stored functions) are associated with a particular database, just like tables or views. When you drop a database, any stored routines in the database are also dropped.

存储例程(指存储过程和存储函数)与特定的数据库相关联，就像表或视图一样。当您删除一个数据库时，数据库中的任何存储例程也会被删除。

Stored procedures and functions do not share the same namespace. It is possible to have a procedure and a function with the same name in a database.

存储过程和函数不共享相同的名称空间。在数据库中可以有一个具有相同名称的过程和函数。

In Stored procedures dynamic SQL can be used but not in functions or triggers.

在存储过程中，可以使用动态SQL，但不能在函数或触发器中使用。

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use Dynamic SQL (where you construct statements as strings and then execute them). (Dynamic SQL in MySQL stored routines)

SQL准备语句(准备、执行、释放准备)可以用于存储过程，但不能用于存储函数或触发器。因此，存储的函数和触发器不能使用动态SQL(将语句构造为字符串，然后执行它们)。(MySQL存储例程中的动态SQL)

Some more interesting differences between FUNCTION and STORED PROCEDURE:

一些更有趣的函数和存储过程之间的差异:

(This point is copied from a blogpost.) Stored procedure is precompiled execution plan where as functions are not. Function Parsed and compiled at runtime. Stored procedures, Stored as a pseudo-code in database i.e. compiled form.

(这一点是从一篇博文中抄来的。)存储过程是预编译的执行计划，而不是函数。函数在运行时进行解析和编译。存储过程，存储为数据库中的伪代码，即编译形式。
(I'm not sure for this point.)
Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any no. of applications at a time. reference

(我不确定这一点。)存储过程的安全性,减少了网络流量,也可以在任何没有调用存储过程。的应用程序。参考
Functions are normally used for computations where as procedures are normally used for executing business logic.

函数通常用于计算，因为程序通常用于执行业务逻辑。
Functions Cannot affect the state of database (Statements that do explicit or implicit commit or rollback are disallowed in function) Whereas Stored procedures Can affect the state of database using commit etc.
refrence: J.1. Restrictions on Stored Routines and Triggers

函数不能影响数据库的状态(函数中不允许执行显式或隐式提交或回滚的语句)，而存储过程可以使用commit等方法影响数据库的状态。对存储例程和触发器的限制。
Functions can't use FLUSH statements whereas Stored procedures can do.

函数不能用冲洗语句而存储过程。
Stored functions cannot be recursive Whereas Stored procedures can be. Note: Recursive stored procedures are disabled by default, but can be enabled on the server by setting the max_sp_recursion_depth server system variable to a nonzero value. See Section 5.2.3, “System Variables”, for more information.

存储函数不能递归，而存储过程可以递归。注意:默认情况下禁用递归存储过程，但是可以通过将max_sp_recursion_depth服务器系统变量设置为非零值在服务器上启用递归存储过程。有关更多信息，请参见第5.2.3节“系统变量”。
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. Good Example: How to Update same table on deletion in MYSQL?

在一个存储函数或触发,它不允许修改一个表已经被使用(读或写)的声明,函数调用或触发。很好的例子:如何更新相同的MYSQL表删除吗?

Note: that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger. For example, although you can use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger.

注意:通常,尽管一些限制适用于存储函数和触发器但不存储过程,这些限制也适用于存储过程从存储中调用函数或触发器。例如,尽管你可以在存储过程中使用冲洗,这样一个不能从一个存储调用函数或存储过程触发。

#3

One significant difference is that you can include a function in your SQL queries, but stored procedures can only be invoked with the CALL statement:

一个重要的区别是，您可以在SQL查询中包含一个函数，但是只能使用CALL语句调用存储过程:

UDF Example:

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)

Sproc Example:

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)

#4

A stored function can be used within a query. You could then apply it to every row, or within a WHERE clause.

存储函数可以在查询中使用。你可以将它应用到每一行,或者在一个WHERE子句。

A procedure is executed using the CALL query.

使用调用查询执行过程。

#1