mysql自訂函數

標籤：_for   成功   war   需要   localhost   局部變數   帶來   函數定義   engine   

原文：http://www.cnblogs.com/zhangminghui/p/4113160.html

引言

　　MySQL本身提供了內建函數，這些函數的存在給我們日常的開發和資料操作帶來了很大的便利，比如我前面提到過的彙總函式SUM()、AVG()以及日期時間函數等等，可是我們總會出現其他的需求：我們需要調用一個函數，這個函數需要按照我們的要求來實現我們自己的功能，可是這個函數式系統不能提供的因為這中需求的不確定性。因此我們需要自己來解決這種需求。還好MySQL設計的擴充性給了我們這個機會，我們可以通過自訂函數的功能解決這個問題。

1.MySQL自訂函數簡介

　　在MySQL中使用自訂函數也需要相應的要求，文法如下，

　　建立新函數：

　　　　Create function function_name(參數列表)

　　　　returns傳回值類型

　　　　函數體內容

　　相關說明，

• 函數名：應該合法的標識符，並且不應該與已有的關鍵字衝突。一個函數應該屬於某資料庫，可以使用db_name.funciton_name的形式執行當前函數所屬資料庫

　　　　，否則預設為當前資料庫。

• 參數列表：可以有一個或者多個函數參數，甚至是沒有參數也是可以的。對於每個參數，由參數名和參數類型組成。
• 傳回值：指明傳回值類類型
• 函數體：自訂函數的函數體由多條可用的MySQL語句，流程式控制制，變數聲明等語句構成。需要指明的是函數體中一定要含有return 返回語句。

2.自訂樣本　　a.無參函數定義

mysql> DROP FUNCTION IF EXISTS hello;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> DELIMITER $$mysql> CREATE FUNCTION hello()    -> RETURNS VARCHAR(255)    -> BEGIN    -> RETURN ‘Hello  world,i am mysql‘;    -> END $$Query OK, 0 rows affected (0.11 sec)

調用自訂函數：

mysql> DELIMITER ;mysql> SELECT hello();+-------------------------+| hello()                 |+-------------------------+| Hello  world,i am mysql |+-------------------------+1 row in set (0.00 sec)

可見已經調用成功了，需要注意的幾點

　　之所以使用DELIMITER是因為MySQL中預設是使用分號來結束一個命令的，我們定義的函數體中一條命令寫完時會用分號來結束，而MySQL會誤以為函數體已經定義完成，因為我們需要定義一個新的標識符來標識一個命令的結束，因為就可以使用DELIMITER。

b:含有參數的自訂函數

　　在我們格式化日期的時候我們會調用DATE_FORMAT(date,format)函數，第一個參數是日期第二個參數是需要格式化的格式，也就是說需要傳遞兩個參數，我們現在就把這個函數給改下

mysql> DELIMITER $$mysql> DROP FUNCTION IF EXISTS test.formatDate $$Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> CREATE FUNCTION   test.formatDate(fdate datetime)    -> RETURNS VARCHAR(255)    -> BEGIN    -> DECLARE x VARCHAR(255) DEFAULT ‘‘;    -> SET x= date_format(fdate,‘%Y年%m月%d日%h時%i分%s秒‘);    -> RETURN x;    -> END $$Query OK, 0 rows affected (0.11 sec)mysql> DELIMITER ;mysql> SELECT   formatDate(now());+----------------------------+| formatDate(now())          |+----------------------------+| 2014年11月21日03時41分21秒 |+----------------------------+1 row in set (0.18 sec)

這樣當我們需要定製這樣的函數時就可以自己實現了，那就是通過含參的自訂函數。

3.自訂函數相關文法及變數　　a:變數聲明

　　　　DECLARE var_name[,...] type [DEFAULT value] ，這個語句被用來聲明局部變數。要給變數提供一個預設值，請包含一個DEFAULT子句。值可以被指定為一個運算式，不需要為一個常數。如果沒有DEFAULT子句，初始值為NULL。使用語序使用 set 和 select into語句為變數賦值。

　　b:IF  條件陳述式、

IF search_conditionTHENstatement_list[ELSEIF search_conditionTHENstatement_list]...[ELSE statement_list]ENDIF;

 　c:CASE語句

CASE case_valueWHEN when_valueTHENstatement_list[WHEN when_value THENstatement_list]...[ELSE statement_list]END CASE; 

　　d:迴圈語句

While[begin_label:]WHILEsearch_conditionDOstatement_listEND WHILE [end_label];退出整個迴圈leave 相當於break退出當前迴圈iterate 相當於 continue通過退出的標籤決定退出哪個迴圈。

 

查看定義的函數  SHOW FUCNTION STATUS LIKE ‘function_name‘，或者是使用SHOW  CREATE FUNCTION function_name;

　　

mysql> SHOW FUNCTION STATUS LIKE ‘hello‘;+------+-------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db   | Name  | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |+------+-------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| test | hello | FUNCTION | [email protected] | 2014-11-21 14:48:05 | 2014-11-21 14:48:05 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |+------+-------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+1 row in set (0.18 sec)mysql> SHOW  CREATE FUNCTION hello;+----------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Function | sql_mode                                                       | Create Function           | character_set_client | collation_connection | Database Collation |+----------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| hello    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `hello`() RETURNS varchar(255) CHARSET latin1BEGINRETURN ‘Hello  world,i am mysql‘;END | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |+----------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1 row in set (0.01 sec)

mysql自訂函數