MySQL自訂函數

標籤：time   sql   複合   等等   ntb   if語句   不同   exist   lis   

自訂函數 (user-defined function UDF)就是用一個象ABS() 或 CONCAT()這樣的固有（內建）函數一樣作用的新函數去擴充MySQL。

所以UDF是對MySQL功能的一個擴充

建立和刪除自訂函數文法:

建立UDF:

　　CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])

　　RETURNS {STRING|INTEGER|REAL}

　　runtime_body

簡單來說就是:

　　CREATE FUNCTION 函數名稱(參數列表)

　　RETURNS 傳回值類型

　　函數體

刪除UDF:

　　DROP FUNCTION function_name

調用自訂函數文法:

　　SELECT function_name(parameter_value,...)

文法樣本:

建立簡單的無參UDF

  simpleFun() VARVHAR()  "Hello World!";

說明:

UDF可以實現的功能不止於此,UDF有兩個關鍵點,一個是參數,一個是傳回值,UDF可以沒有參數,但UDF必須有且只有一個傳回值

在函數體重我們可以使用更為複雜的文法,比如複合結構/流程式控制制/任何SQL語句/定義變數等等

複合結構定義文法:

在函數體中,如果包含多條語句,我們需要把多條語句放到BEGIN...END語句塊中

DELIMITER //CREATE FUNCTION IF EXIST deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) BEGINDELETE FROM son WHERE id = uid;RETURN (SELECT COUNT(id) FROM son);END//

修改預設的結束符文法:

DELIMITER // 意思是修改預設的結束符";"為"//",以後的SQL語句都要以"//"作為結尾

特別說明:

UDF中,REURN語句也包含在BEGIN...END中

自訂函數中定義局部變數文法:

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

簡單來說就是:

DECLARE 變數1[,變數2,... ]變數類型 [DEFAULT 預設值]

這些變數的作用範圍是在BEGIN...END程式中,而且定義局部變數語句必須在BEGIN...END的第一行定義

樣本:

DELIMITER //CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) RETURNS SMALLINTBEGINDECLARE a, b SMALLINT UNSIGNED DEFAULT 10;SET  a = x, b = y;RETURN a+b;END//

上邊的代碼只是把兩個數相加,當然,沒有必要這麼寫,只是說明局部變數的用法,還是要說明下:這些局部變數的作用範圍是在BEGIN...END程式中

為變數賦值文法:

SET parameter_name = value[,parameter_name = value...]

SELECT INTO parameter_name

eg: 

...在某個UDF中...DECLARE x int;SELECT COUNT(id) FROM tdb_name INTO x;RETURN x;END//

使用者變數定義文法:(可以理解成全域變數)

SET @param_name = value

SET @allParam = 100;SELECT @allParam;

上述定義並顯示@allParam使用者變數,其範圍只為目前使用者的用戶端有效

自訂函數中流程式控制制語句文法:

預存程序和函數中可以使用流程式控制制來控制語句的執行。

MySQL中可以使用IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句來進行流程式控制制。

每個流程中可能包含一個單獨語句，或者是使用BEGIN...END構造的複合陳述式，構造可以被嵌套

1．IF語句

IF語句用來進行條件判斷。根據是否滿足條件，將執行不同的語句。其文法的基本形式如下：

IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF

其中，search_condition參數表示條件判斷語句；statement_list參數表示不同條件的執行語句。

注意：MYSQL還有一個IF()函數，他不同於這裡描述的IF語句

下面是一個IF語句的樣本。代碼如下：

IF age>20 THEN SET @[email protected]+1;  ELSEIF age=20 THEN SET @[email protected]+1;  ELSE SET @[email protected]+1;  END IF;

該樣本根據age與20的大小關係來執行不同的SET語句。

如果age值大於20，那麼將count1的值加1；如果age值等於20，那麼將count2的值加1；

其他情況將count3的值加1。IF語句都需要使用END IF來結束。

2．CASE語句

CASE語句也用來進行條件判斷，其可以實現比IF語句更複雜的條件判斷。CASE語句的基本形式如下：

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE

其中，case_value參數表示條件判斷的變數；

when_value參數表示變數的取值；

statement_list參數表示不同when_value值的執行語句。

CASE語句還有另一種形式。該形式的文法如下：

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE

其中，search_condition參數表示條件判斷語句；

statement_list參數表示不同條件的執行語句。

下面是一個CASE語句的樣本。代碼如下：

CASE age WHEN 20 THEN SET @[email protected]+1; ELSE SET @[email protected]+1; END CASE ;

代碼也可以是下面的形式：

CASE WHEN age=20 THEN SET @[email protected]+1; ELSE SET @[email protected]+1; END CASE ;

本樣本中，如果age值為20，count1的值加1；否則count2的值加1。CASE語句都要使用END CASE結束。

注意：這裡的CASE語句和“控制流程程函數”裡描述的SQL CASE運算式的CASE語句有輕微不同。這裡的CASE語句不能有ELSE NULL子句

並且用END CASE替代END來終止！！

 

3．LOOP語句

LOOP語句可以使某些特定的語句重複執行，實現一個簡單的迴圈。

但是LOOP語句本身沒有停止迴圈的語句，必須是遇到LEAVE語句等才能停止迴圈。

LOOP語句的文法的基本形式如下：

[begin_label:] LOOP statement_list END LOOP [end_label]

其中，begin_label參數和end_label參數分別表示迴圈開始和結束的標誌，這兩個標誌必須相同，而且都可以省略；

statement_list參數表示需要迴圈執行的語句。

下面是一個LOOP語句的樣本。代碼如下：

add_num: LOOP  SET @[email protected]+1;  END LOOP add_num ;

該樣本迴圈執行count加1的操作。因為沒有跳出迴圈的語句，這個迴圈成了一個死迴圈。

LOOP迴圈都以END LOOP結束。

 

4．LEAVE語句

LEAVE語句主要用於跳出迴圈控制。其文法形式如下：

LEAVE label

其中，label參數表示迴圈的標誌。

 

下面是一個LEAVE語句的樣本。代碼如下：

add_num: LOOP SET @[email protected]+1; IF @count=100 THEN LEAVE add_num ; END LOOP add_num ;

該樣本迴圈執行count加1的操作。當count的值等於100時，則LEAVE語句跳出迴圈。

 

5．ITERATE語句

ITERATE語句也是用來跳出迴圈的語句。但是，ITERATE語句是跳出本次迴圈，然後直接進入下一次迴圈。

ITERATE語句只可以出現在LOOP、REPEAT、WHILE語句內。

ITERATE語句的基本文法形式如下：

ITERATE label

其中，label參數表示迴圈的標誌。

下面是一個ITERATE語句的樣本。代碼如下：

add_num: LOOP SET @[email protected]+1; IF @count=100 THEN LEAVE add_num ; ELSE IF MOD(@count,3)=0 THEN ITERATE add_num; SELECT * FROM employee ; END LOOP add_num ;

該樣本迴圈執行count加1的操作，count值為100時結束迴圈。如果count的值能夠整除3，則跳出本次迴圈，不再執行下面的SELECT語句。

說明：LEAVE語句和ITERATE語句都用來跳出迴圈語句，但兩者的功能是不一樣的。

LEAVE語句是跳出整個迴圈，然後執行迴圈後面的程式。而ITERATE語句是跳出本次迴圈，然後進入下一次迴圈。

使用這兩個語句時一定要區分清楚。

 

6．REPEAT語句

REPEAT語句是有條件控制的迴圈語句。當滿足特定條件時，就會跳出迴圈語句。REPEAT語句的基本文法形式如下：

[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]

其中，statement_list參數表示迴圈的執行語句；search_condition參數表示結束迴圈的條件，滿足該條件時迴圈結束。

下面是一個ITERATE語句的樣本。代碼如下：

REPEAT SET @[email protected]+1; UNTIL @count=100 END REPEAT ;

該樣本迴圈執行count加1的操作，count值為100時結束迴圈。

REPEAT迴圈都用END REPEAT結束。

 

7．WHILE語句

WHILE語句也是有條件控制的迴圈語句。但WHILE語句和REPEAT語句是不一樣的。

WHILE語句是當滿足條件時，執行迴圈內的語句。

WHILE語句的基本文法形式如下：

[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

其中，search_condition參數表示迴圈執行的條件，滿足該條件時迴圈執行；

statement_list參數表示迴圈的執行語句。

下面是一個ITERATE語句的樣本。代碼如下：

WHILE @count<100 DO SET @[email protected]+1; END WHILE ;

該樣本迴圈執行count加1的操作，count值小於100時執行迴圈。

如果count值等於100了，則跳出迴圈。WHILE迴圈需要使用END WHILE來結束。

 

轉載自 http://www.cnblogs.com/caoruiy/p/4485273.html

MySQL自訂函數