MySQL 存储过程、函数和例程之间有什么区别？

What's the differences between stored procedures, functions and routines?

提问by Yang

In MySQL database context, what is the difference among these 3 terms:

在 MySQL 数据库上下文中，这 3 个术语之间的区别是什么：

• stored procedure
• stored function
• stored routine

• 存储过程
• 存储函数
• 存储例程

Also the build-in functions like those date time functions (e.g. WEEKDAY()etc) are considered as what?

像那些日期时间函数（例如WEEKDAY()等）这样的内置函数也被认为是什么？

回答by Jakob

Google is your friend. The first match for "mysql routine function procedure" is this: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

谷歌是你的朋友。“mysql 例程函数过程”的第一个匹配是这样的：http: //dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

A quick summary:

快速总结：

A stored routine is either a procedure or a function.

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 and can return a scalar value.

存储例程是过程或函数。

过程是使用 CALL 语句调用的，并且只能使用输出变量传回值。

函数可以像任何其他函数一样从语句内部调用，并且可以返回标量值。

回答by Sujeet Kumar

Here I have tried to summarize the differences between functions and procedures:

这里我尝试总结一下函数和过程的区别：

1. A FUNCTION alwaysreturns a value using the return statement. PROCEDURE mayreturn one or more values through parameters or may not return any at all.
2. Functionsare normally used for computations where as proceduresare normally used for executing business logic.
3. A Function returns 1 value only. Procedure can return multiple values (max 1024).
4. Stored procedure always returns an integer value of zero by default. Whereas function return types could be scalar or table or table values.
5. Stored procedures have a precompiled execution plan, where as functions are not.
6. A function can be called directly by SQL statement like SELECT func_name FROM DUALwhile procedures cannot.
7. Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any number of applications at a time.
8. A Function can be used in the SQL queries while a procedure cannot be used in SQL queries. That causes a major difference between function and procedures.

1. FUNCTION总是使用 return 语句返回一个值。PROCEDURE可能会通过参数返回一个或多个值，也可能根本不返回任何值。
2. 函数通常用于计算，而过程通常用于执行业务逻辑。
3. 一个函数只返回 1 个值。过程可以返回多个值（最多 1024 个）。
4. 默认情况下，存储过程始终返回整数值零。而函数返回类型可以是标量或表或表值。
5. 存储过程有一个预编译的执行计划，而函数则没有。
6. SQL 语句可以直接调用函数，SELECT func_name FROM DUAL而过程则不能。
7. 存储过程具有安全性并减少了网络流量，而且我们可以一次在任意数量的应用程序中调用存储过程。
8. 函数可以在 SQL 查询中使用，而过程不能在 SQL 查询中使用。这导致函数和过程之间的主要区别。

回答by Deepak Mani

Difference between MySQL function and mysql procedure

MySQL函数和mysql过程的区别

MYSQL Function

MYSQL 函数

It must return value. IN, OUTand INOUTcannot be used in function.But return datatype must be declare when create a function. function can be called from a SQL statement. Function return one values.

它必须返回值。IN，OUT而INOUT不能在function.But返回的数据类型使用时创建功能必须申报。可以从 SQL 语句调用函数。函数返回一个值。

MYSQL Procedure

MYSQL 程序

Return Values is not mandatory but may be uses the OUT parameter to procedure returns. Can use the IN| OUT| INOUTparameters. Procedure cannot be called from the SQL Statement. procedure return multiple values by using OUTor INOUTparameters.

返回值不是强制性的，但可以使用 OUT 参数来处理返回值。可以使用IN| OUT| INOUT参数。无法从 SQL 语句调用过程。过程通过使用OUT或INOUT参数返回多个值。

回答by venkat

PROCEDURES VS FUNCTIONS

程序与功能

1.  PROCEDURES may or may not return a value but FUNCTION must return a value
2.  PROCEDURES can have input/output parameter but FUNCTION only has input parameter.
3.  We can call FUNCTION from PROCEDURES but cannot call PROCEDURES from a function. 
4.  We cannot use PROCEDURES in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with FUNCTION.
5.  We can use try-catch exception handling in PROCEDURES but we cannot do that in FUNCTION. 
6.  We can use transaction in PROCEDURES but it is not possible in FUNCTION.

1.  PROCEDURES may or may not return a value but FUNCTION must return a value
2.  PROCEDURES can have input/output parameter but FUNCTION only has input parameter.
3.  We can call FUNCTION from PROCEDURES but cannot call PROCEDURES from a function. 
4.  We cannot use PROCEDURES in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with FUNCTION.
5.  We can use try-catch exception handling in PROCEDURES but we cannot do that in FUNCTION. 
6.  We can use transaction in PROCEDURES but it is not possible in FUNCTION.

回答by James

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Functions can be called from Procedure whereas Procedures cannot be called from Function.

函数必须返回一个值，但在存储过程中它是可选的（过程可以返回零或 n 个值）。函数只能有输入参数，而过程可以有输入/输出参数。可以从过程调用函数，而不能从函数调用过程。