MySQL procedure 与 function 的区别

1. 只能说是看各自的习惯的,用procedure可以实现的,大部分用函数都可以的,所以有些package中函数的实现一个，过程的实现一个。

 

2.考虑是使用函数还是使用存储过程可以根据以下方面：
从参数的返回情况来看：如果返回多个参数值最好使用存储过程，如果只有一个返回值的话可以使用函数；
从调用情况来看：

(1)如果在SQL语句（DML或SELECT）中调用的话一定是存储函数或存储的封装函数不可以是存储过程，但调用存储函数的时候还有好多限制以及函数的纯度等级的问题，你可以参考《ORACLE 9I PL\SQL程序设计》（机械工业出版社）；
(2)如果是在过程化语句中调用的话，就要看你要实现什么样的功能。函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等），所以虽然他们的语法上很相似但用户在使用他们的时候所需要完成的功能大部分情况下是不同的。

3. 让我来简单的说PROCEDURE可以返回值,也可以做一件事,比如几个TABLE之间的数据转来转去,而FUNCTION一定要有返回值,还只能返回一个.....

4.三点与存储过程不同的地方：
a，必须要一个RETURNS语句来定义返回值类型
b，不能指定参数的IN、OUT或INOUT修饰符，所有参数隐式的为IN
c，Function体必须包含RETURN语句来终结Function执行并返回指定的结果给调用者 
 

 

5.

http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of these restrictions apply to stored functions but not to stored procedures.

The restrictions for stored functions also apply to triggers.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:

• The table-maintenance statements CHECK TABLE and OPTIMIZE TABLE. This restriction is lifted beginning with MySQL 5.0.17.

• The locking statements LOCK TABLES and UNLOCK TABLES.

• ALTER VIEW. (Before MySQL 5.0.46, this restriction is enforced only for stored functions.)

• LOAD DATA and LOAD TABLE.

• SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE). Implication: You cannot use dynamic SQL within stored routines (where you construct dynamically statements as strings and then execute them). This restriction is lifted as of MySQL 5.0.13 for stored procedures; it still applies to stored functions and triggers.

  In addition, SQL statements that are not permitted within prepared statements are also not permitted in stored routines. See Section 12.7, “SQL Syntax for Prepared Statements”, for a list of statements supported as prepared statements. Statements not listed there are not supported for SQL prepared statements and thus are also not supported for stored routines unless noted otherwise in Section 17.2, “Using Stored Routines (Procedures and Functions)”.

• Inserts cannot be delayed. INSERT DELAYED syntax is accepted but the statement is handled as a normal INSERT.

For stored functions (but not stored procedures), the following additional statements or operations are disallowed:

• Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to allow them.

• Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. A function can process a result set either with SELECT ... INTO var_list or by using a cursor and FETCH statements. See Section 12.8.3.3, “SELECT ... INTO Statement”.

• FLUSH statements.

• Before MySQL 5.0.10, stored functions created with CREATE FUNCTION must not contain references to tables, with limited exceptions. They may include some SET statements that contain table references, for example SET a:= (SELECT MAX(id) FROM t), and SELECT statements that fetch values directly into variables, for example SELECT i INTO var1 FROM t.

• Stored functions cannot be used recursively.

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

• If you refer to a temporary table multiple times in a stored function under different aliases, a Can't reopen table: 'tbl_name' error occurs, even if the references occur in different statements within the function.

• A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. Statements that invoke a function are recorded rather than the statements executed within the function. Consequently, stored functions that update the same underlying tables do not execute in parallel. In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log. See Section 17.5, “Binary Logging of Stored Programs”.