[MySQL 自定義函數和存儲過程的使用](https://itbilu.com/database/mysql/Nye1nX0Bb.html) MySQL支持自定義函數和存儲過程，創建函數和存儲過程分別使用`CREATE PROCEDURE`和`CREATE FUNCTION`，兩者在語法結構上比較相似，但是它們的使用方法及使用場景有所有不同。 1. [存儲過程](https://itbilu.com/database/mysql/Nye1nX0Bb.html#procedure) * [1.1 語法結構](https://itbilu.com/database/mysql/Nye1nX0Bb.html#proc-syntax) * [1.2 存儲過程的使用](https://itbilu.com/database/mysql/Nye1nX0Bb.html#proc-example) 2. [函數](https://itbilu.com/database/mysql/Nye1nX0Bb.html#function) * [2.1 語法結構](https://itbilu.com/database/mysql/Nye1nX0Bb.html#func-syntax) * [2.2 自定義函數的使用](https://itbilu.com/database/mysql/Nye1nX0Bb.html#func-example) * [2.3 函數與存儲過程的區別](https://itbilu.com/database/mysql/Nye1nX0Bb.html#dif-fun-proc) ### 1\. 存儲過程 #### 1.1 語法結構 ~~~ CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body ~~~ 在上面語句中，包含以下參數： * `sp_name`?- 存儲過程名，可以是任何合法的MySQL標識符 * `[proc_parameter]`?- 參數列表，可選。其形式為 ~~~ [ IN | OUT | INOUT ] param_name type ~~~ * `[ IN | OUT | INOUT ]`?- 其中，`IN`表示輸入參數，`OUT`表示輸出參數，`INOUT`表示輸入輸出參數。默為輸入參數 * `param_name`?- 參數名 * `type`?- 參數類型，可以是任何合法的MySQL數據類型 * `characteristic`?- 存儲過程特性描述，其取值如下： ~~~ characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } ~~~ * `COMMENT`?- 注釋信息 * `LANGUAGE`?- 指明`routine_body`部分由SQL組成。注：`SQL`是LANGUAGE特性的唯一值 * `[NOT] DETERMINISTIC`?- 指明存儲過程執行的結果是否確定。DETERMINISTIC 表示結果是確定的，即：每次執行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是不確定的，即：相同的輸入可能得到不同的輸出。默認為 NOT DETERMINISTIC。 * `CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA`?- 指明子程序使用SQL語句的限制，默認為`CONTAINS SQL` * `CONTAINS SQL`?- 表明子程序包含SQL語句，但是不包含讀寫數據的語句 * `NO SQL`?- 表明子程序不包含SQL語句 * `READS SQL DATA`?- 說明子程序包含讀數據的語句 * `MODIFIES SQL DATA`?- 子程序包含寫數據的語句 * `SQL SECURITY { DEFINER | INVOKER }?`- 執行權限。DEFINER表示定義者調用，INVOKER表示擁有調用權限都可以執行 * `routine_body`?- 程序體，即：包含在`BEGIN...END`中的，要執行的SQL語句 #### 1.2 存儲過程的使用 定義一個包含傳出參數的存儲過程： ~~~ delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END // delimiter ; ~~~ 在上例中，我們定義了一個名為`simpleproc`，這個存儲過程會統計表`t`的數據，并將其插入`param1`參數傳了。`delimiter`用于定義分隔符，MySQL中默認的分隔符是`;`，而存儲過程中可能包含多條SQL語句，所以需要通過`delimiter`來重新指定分隔符。 存儲過程的調用 存儲過程定義后，可以使用`CALL`來調用存儲過程。可以客戶端單獨調用，也可以在SQL語名或另一個存儲過程中調用。 ~~~ mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec) ~~~ 存儲過程的刪除 MySQL中的大多數自定義對象都可以通過`DROP`語句刪除。如，刪除上面存儲過程： ~~~ DROP PROCEDURE simpleproc ~~~ ### 2\. 函數 #### 2.1 語法結構 ~~~ CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body ~~~ 創建一個函數，`sp_name`表示函數名。函數的定義與[存儲過程定義](https://itbilu.com/database/mysql/Nye1nX0Bb.html#proc-syntax)非常相似，只是以下兩個參數略有不同： * `func_parameter`?- 函數參數列表，其形式如下： ~~~ param_name type ~~~ * `param_name`?- 參數名 * `type`?- 參數類型，可以是任何合法的MySQL數據類型 * `RETURNS type`?- 表示函數的返回值類型 #### 2.2 自定義函數的使用 定義一個返回字符串的自定義函數： ~~~ DELIMITER // CREATE FUNCTION getName(id INT) RETURNS CHAR(50) RETURN (SELECT name FROM t WHERE id=id); // DELIMITER ; ~~~ 函數的調用 定義函數后，可以通過`SELECT`語句來調用函數： ~~~ SELECT getName(1); +----------------+ | getName(1) | +----------------+ | itbilu.com | +----------------+ 1 row in set (0.00 sec) ~~~ 函數的調用 函數同樣可以通過`DROP`語句刪除。如，刪除上面定義的函數： ~~~ DROP FUNCTION getName; ~~~ #### 2.3 函數與存儲過程的區別 1. 功能不同：函數實現的功能針對性比較強，而存儲過程實現的功能要復雜一此。存儲過程，可以執行包括修改表等一系列數據庫操作；而自定義函數不能用于執行一組修改全局數據庫狀態的操作。 2. 返回值方式及類型不同：存儲過程通過參數返回值，返回值類型可以是記錄集；而函數只能返回值或者表對象。函數只能返回一個變量；而存儲過程可以返回多個(通過多個返回參數實現)。 3. 參數類型不同：存儲過程的參數可以有`IN`、`OUT`、`INOUT`三種類型，而函數只能有`IN`類型。存儲過程返回類型不是必須的，而函數需要返回類型，且函數體中必須包含一個有效的`RETURN`語句。 4. 適用范圍不同：存儲過程一般是作為一個獨立的部分來執行，而函數可以作為查詢語句的一個部分來調用(如：SELECT中)。由于函數可以返回一個表對象，因此它可以在查詢語句中FROM關鍵字的后面。 SQL語句中不可用存儲過程，而可以使用函數。