MySQL 存储过程与函数，我将在什么时候使用？

MySQL stored procedure vs function, which would I use when?

提问by Anonym

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?

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

采纳答案by nos

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

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

e.g. SELECT get_foo(myColumn) FROM mytableis 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.

egSELECT get_foo(myColumn) FROM mytable是无效的 ifget_foo()是一个过程，但你可以这样做 ifget_foo()是一个函数。代价是函数比过程有更多的限制。

回答by Grijesh Chauhan

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

过程和函数之间最普遍的区别在于它们的调用方式和目的不同：

1. 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.
2. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
3. You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.

1. 过程不返回值。相反，它使用 CALL 语句调用以执行诸如修改表或处理检索到的记录之类的操作。
2. 函数在表达式中被调用，并直接向调用者返回单个值以在表达式中使用。
3. 您不能使用 CALL 语句调用函数，也不能在表达式中调用过程。

Syntax for routine creation differs somewhat for procedures and functions:

例程创建的语法与过程和函数有所不同：

1. 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.

2. 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.

   • 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.

   • 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.

   • Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.

   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.

1. 过程参数可以定义为仅输入、仅输出或两者兼而有之。这意味着过程可以通过使用输出参数将值传递回调用者。可以在跟在 CALL 语句之后的语句中访问这些值。函数只有输入参数。因此，虽然过程和函数都可以有参数，但过程参数声明与函数的参数声明不同。

2. 函数有返回值，所以在函数定义中必须有RETURNS子句来指示返回值的数据类型。此外，函数体内必须至少有一个 RETURN 语句来向调用者返回一个值。RETURNS 和 RETURN 不会出现在过程定义中。

   • 要调用存储过程，请使用CALL statement. 要调用存储的函数，请在表达式中引用它。该函数在表达式计算期间返回一个值。

   • 过程是使用 CALL 语句调用的，并且只能使用输出变量传回值。函数可以像任何其他函数一样从语句内部调用（即通过调用函数的名称），并且可以返回标量值。

   • 将参数指定为 IN、OUT 或 INOUT 仅对 PROCEDURE 有效。对于 FUNCTION，参数始终被视为 IN 参数。

   如果参数名前没有给出关键字，则默认为 IN 参数。 存储函数的参数前面没有 IN、OUT 或 INOUT。所有函数参数都被视为 IN 参数。

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 准备好的语句（PREPARE、EXECUTE、DEALLOCATE PREPARE）可用于存储过程，但不能用于存储函数或触发器。因此，存储的函数和触发器不能使用动态 SQL（将语句构造为字符串然后执行它们）。（MySQL存储例程中的动态SQL）

Some more interesting differences between FUNCTION and STORED PROCEDURE:

FUNCTION 和 STORED PROCEDURE 之间一些更有趣的区别：

1. (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.

2. (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

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. 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

5. Functions can't use FLUSHstatements whereas Stored procedures can do.

6. 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.

7. 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?

1. （这一点是从博客文章中复制的。）存储过程是预编译的执行计划，而函数则不是。函数在运行时解析和编译。存储过程，以伪代码形式存储在数据库中，即编译形式。

2. （这一点我不确定。）
   存储过程具有安全性并减少了网络流量，而且我们可以在任何编号中调用存储过程。一次的应用程序。参考

3. 函数通常用于计算，而过程通常用于执行业务逻辑。

4. 函数不能影响数据库的状态（函数中不允许执行显式或隐式提交或回滚的语句）而存储过程可以使用提交等影响数据库的状态。
   参考：J.1。对存储例程和触发器的限制

5. 函数不能使用FLUSH语句，而存储过程可以。

6. 存储函数不能递归，而存储过程可以。注意：默认情况下禁用递归存储过程，但可以通过将 max_sp_recursion_depth 服务器系统变量设置为非零值在服务器上启用。有关更多信息，请参阅第 5.2.3 节，“系统变量”。

7. 在存储的函数或触发器中，不允许修改已被调用函数或触发器的语句使用（用于读取或写入）的表。好例子：如何在 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.

注意：虽然一些限制通常适用于存储函数和触发器而不适用于存储过程，但这些限制确实适用于从存储函数或触发器中调用的存储过程。例如，虽然您可以在存储过程中使用 FLUSH，但不能从存储函数或触发器中调用这样的存储过程。

回答by Daniel Vassallo

One significant difference is that you can include a functionin your SQL queries, but stored procedurescan only be invoked with the CALLstatement:

一个显着的区别是您可以在 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)

回答by Evert

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.

使用 CALL 查询执行过程。

回答by palash140

Stored procedure can be called recursively but stored function can not

存储过程可以递归调用，但存储函数不能