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