MySQL學習筆記—自訂函數

標籤：

MySQL學習筆記—自訂函數注釋文法：

MySQL伺服器支援3種注釋風格：

• 從‘#’字元從行尾。

• 從‘– ’序列到行尾。請注意‘– ’(雙破折號)注釋風格要求第2個破折號後面至少跟一個空格符(例如空格、tab、分行符號等等)。該文法與標準SQL注釋文法稍有不同。

• 從/序列到後面的/序列。結束序列不一定在同一行中，因此該文法允許注釋跨越多行。

下面的例子顯示了3種風格的注釋：

mysql> SELECT 1+1;     # This comment continues to the end of linemysql> SELECT 1+1;     -- This comment continues to the end of linemysql> SELECT 1 /* this is an in-line comment */ + 1;mysql> SELECT 1+/*this is amultiple-line comment*/1;

自訂使用者變數:

• 可以先在使用者變數中儲存值然後在以後引用它；這樣可以將值從一個語句傳遞到另一個語句。使用者變數與串連有關。也就是說，一個用戶端定義的變數不能被其它用戶端看到或使用。當用戶端退出時，該用戶端串連的所有變數將自動釋放。

• 使用者變數的形式為@var_name，其中變數名var_name可以由當前字元集的文字數字字元、‘.’、‘_’和‘$’組成。 預設字元集是cp1252 (Latin1)。可以用mysqld的–default-character-set選項更改字元集。使用者變數名對大小寫不敏感。
  設定使用者變數的一個途徑是執行SET語句：

SET @var_name = expr [, @var_name = expr] ...

對於SET，可以使用=或:=作為分配符。分配給每個變數的expr可以為整數、實數、字串或者NULL值。

也可以用語句select代替SET來為使用者變數分配一個值。在這種情況下，分配符必須為:=而不能用=，因為在非SET語句中=被視為一個比較 操作符：

mysql> SET @t1=0, @t2=0, @t3=0;mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

自訂函數基本方式

• 1.自訂函數
  使用者自訂函數(user-defined function, UDF)是一種對MySQL擴充的途徑, 其用法與內建函數相同。
  自訂函數的兩個必要條件:

  • a.參數
  • b.傳回值

  函數可以返回任意類型的值,同樣可以接受這些類型的參數；
  函數的參數與傳回值之間，沒有必然的聯絡。

• 2.建立自訂函數
  CREATE FUNCTION function_name ([func_parameter[,…]])
  RETURNS {STRING|INTEGER|REAL|DECIMAL|…}
  routine_body - 函數體

  預設地，子程式與當前資料庫關聯。要明確地把子程式與一個給定資料庫關聯起來，可以在建立子程式的時候指定其名字為db_name.function_name。
  如果子程式名和內建的SQL函數名一樣，定義子程式時，你需要在這個名字和隨後括弧中間插入一個空格，否則發生語法錯誤。當你隨後調用子程式的時候也要插入。
  RETURNS字句只能對FUNCTION做指定，對函數而言這是強制的。它用來指定函數的傳回型別，而且函數體必須包含一個RETURN value語句

• 3.關於函數體

  • 函數體由合法的SQL文法構成;
  • 函數體可以是簡單的SELECT或INSERT語句;
  • 函數體如果為複合結構則使用BEGIN…END語句；
  • 複合結構可以包括聲明，迴圈，控制結構。

例子

• 建立一個不帶參數的自訂函數：
  該函數將系統的時間按照設定的格式返回

CREATE FUNCTION myTime()RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H點%i分%s秒‘);

• 建立一個帶有參數的自訂函數：
  該函數可計算兩個傳入參數的平均值

CREATE FUNCTION avg(num1 SMALLINT UNSIGNED ,num2 SMALLINT UNSIGNED)RETURNS FLOAT(3,2) RETURN (num1+num2)/2;

BEGIN … END複合陳述式

BEGIN … END複合陳述式格式：

[begin_label:] BEGIN    [statement_list]END [end_label]

儲存子程式可以使用BEGIN … END複合陳述式來包含多個語句。statement_list 代表一個或多個語句的列表。statement_list之內每個語句都必須用分號（;）來結尾。

複合陳述式可以被標記。除非begin_label存在,否則end_label不能被給出,並且如果二者都存在,他們必須是同樣的。

使用多重語句需要用戶端能發送包含語句定界符;的查詢字串。這個符號在命令列用戶端被用delimiter命令來處理。改變查詢結尾定界符 “ ;” （比如改變為“//”）使得; 可被用在子程式體中。
例如在mysql中執行以下命令：

delimiter //

則以後命令結尾的符號就變成 “ // ”而不是“ ；”

以下例子建立了一個函數，用於向tb1表中插入資料（資料包括username，age），並返回age最大的那一條資料：

• 先執行：

delimiter //

• 再輸入函數：

create function addUser(username varchar(20),age tinyint(3) unsigned)returns tinyint(3) unsignedbegininsert tb1(username,age) values(username,age);return (select max(age) from tb1);end //

• 改回結尾定界符：

delimiter ;

• 執行函數：

select addUser(‘chenjs‘,20);

刪除函數的語句

刪除函數的語句

drop function function_name;

此處只需寫上函數名即可，函數的參數可以不用寫出來。
例如刪除上面建立的函數 addUser(username varchar(20),age tinyint(3) unsigned)，可以直接用以下語句刪除：

drop function addUser;

流量控制構造IF語句

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

IF實現了一個基本的條件構造。如果search_condition求值為真，相應的SQL語句列表被執行。如果沒有search_condition匹配，在ELSE子句裡的語句列表被執行。statement_list可以包括一個或多個語句。
請注意，也有一個IF() 函數，它不同於這裡描述的IF語句：
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 不等於 0 且 expr1 不等於 NULL)，則 IF()的傳回值為expr2; 否則傳回值則為 expr3。IF() 的傳回值為數字值或字串值，具體情況視其所在語境而定。
例如：

返回2，並將2賦給變數@m。

CASE語句

CASE語句

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

或:

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

儲存程式的CASE語句實現一個複雜的條件構造。如果search_condition 求值為真，相應的SQL被執行。如果沒有搜尋條件匹配，在ELSE子句裡的語句被執行。

• 例子
  該例子根據傳入一個表示函數名稱的字串與一個待處理的數字，對出入的數字執行不同的操作（在此之前已將結尾定界符改為”//”）

create function caseTest(str varchar(5),num int)returns int begin case strwhen ‘power‘ then set @result=power(num,2);when ‘ceil‘ then set @result=ceil(num);when ‘floor‘ then set @result=floor(num);when ‘round‘ then set @result=round(num);else set @result=0;end case;return (select @result);end //

測試如下：

LOOP語句

LOOP語句格式

[begin_label:] LOOP    statement_listEND LOOP [end_label]

LOOP允許某特定語句或語句群的重複執行，實現一個簡單的迴圈構造。在迴圈內的語句一直重複直到迴圈被退出，退出通常伴隨著一個LEAVE 語句。

LOOP語句可以被標註。除非begin_label存在，否則end_label不能被給出，並且如果兩者都出現，它們必須是同樣的。

• LEAVE語句
  格式

LEAVE label

這個語句被用來退出任何被標註的流程式控制制構造。它和BEGIN … END或迴圈一起被使用。

• ITERATE語句
  格式

ITERATE label

ITERATE只可以出現在LOOP, REPEAT, 和WHILE語句內。ITERATE意思為：“再次迴圈。”

例如，其建立方式使用的是create procedure而不是create function，這是因為procedure不需要returns 與return欄位，而function必須有returns與return欄位：

CREATE PROCEDURE doiterate(p1 INT)BEGIN  label1: LOOP    SET p1 = p1 + 1;    IF p1 < 10 THEN ITERATE label1;     END IF;    LEAVE label1;  END LOOP label1;  SET @x = p1;END

REPEAT語句

REPEAT語句格式

[begin_label:] REPEAT    statement_listUNTIL search_conditionEND REPEAT [end_label]

REPEAT語句內的語句或語句群被重複，直至search_condition 為真。

REPEAT 語句可以被標註。 除非begin_label也存在，end_label才能被用，如果兩者都存在，它們必須是一樣的。

使用repeat來實現上面的程式，程式如下：

create function doRepeat(p1 int)returns intbegin repeat set p1 = p1 + 1; until p1>10  end repeat;return p1;end

測試:

WHILE語句

WHILE語句格式

[begin_label:] WHILE search_condition DO    statement_listEND WHILE [end_label]

WHILE語句內的語句或語句群被重複，直至search_condition 為真。

WHILE語句可以被標註。 除非begin_label也存在，end_label才能被用，如果兩者都存在，它們必須是一樣的。

例子：

CREATE PROCEDURE dowhile()BEGIN  DECLARE v1 INT DEFAULT 5;  WHILE v1 > 0 DO    SET v1 = v1 - 1;  END WHILE;END

• DECLARE僅被用在BEGIN … END複合陳述式裡，並且必須在複合陳述式的開頭，在任何其它語句之前。用於聲明一個局部變數，該變數在函數外不可訪問，如果想要訪問必須將數值返回，此時應該用create function而不可以用create procedure，因為只有create function才可以有傳回值，函數更改如下：

CREATE function dowhile()RETURNS intBEGIN  DECLARE v1 INT DEFAULT 5;  WHILE v1 > 0 DO    SET v1 = v1 - 1;  END WHILE;RETURN v1;END

MySQL學習筆記—自訂函數