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执行并返回指定的结果给调用者

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 Section12.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 Section17.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.

更多相关文章

随机推荐