多筆的 SQL 敘述,可以儲存成為 Stored Routines Procedure 或是 User-Defined Function (UDF) .
在 Stored Routines 會常用到 SQL Flow Control Statements 除了 CASE , IF (詳細請參考 – https://benjr.tw/101629) 就是 迴圈 WHILE , REPEAT , LOOP & LEAVE , ITERATE ,下面就依序來介紹迴圈的語法.
下面以自訂函數 Function 為範例.自訂函數 (Function) 設定格式如下:
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
測試環境為 CentOS8 x86_64 (虛擬機)
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed
WHILE
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
先確認之前沒有定義過 looptest 這個 User-Defined Function ,有的話移除.
MariaDB [testdb]> DROP FUNCTION IF EXISTS looptest; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [testdb]> SHOW WARNINGS; +-------+------+-----------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------+ | Note | 1305 | FUNCTION testdb.looptest does not exist | +-------+------+-----------------------------------------+ 1 row in set (0.001 sec)
定義 User-Defined Function ,名稱為 looptest ,用來算指數 ab (a: base , b: exponent or index or power).
MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE FUNCTION looptest (a INT ,b INT)
-> RETURNS INT
-> BEGIN
-> DECLARE COUNT ,POWERSUM INT;
-> SET COUNT = 0;
-> SET POWERSUM = 1;
-> WHILE COUNT < b DO
-> SET POWERSUM = POWERSUM*a;
-> SET COUNT=COUNT+1;
-> END WHILE;
-> RETURN POWERSUM;
-> END//
Query OK, 0 rows affected (0.009 sec)
MariaDB [testdb]> DELIMITER ;
建立好 User-Defined Function 之後,可以透過下面指令來檢視.
MariaDB [testdb]> SHOW FUNCTION STATUS; +--------+----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------+----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | testdb | looptest | FUNCTION | root@localhost | 2020-02-12 12:44:37 | 2020-02-12 12:44:37 | DEFINER | | latin1 | latin1_swedish_ci | latin1_swedish_ci | +--------+----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.003 sec)
MariaDB [testdb]> SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME='looptest'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ROUTINE_DEFINITION | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | BEGIN DECLARE COUNT ,POWERSUM INT; SET COUNT = 0; SET POWERSUM = 1; WHILE COUNT < b DO SET POWERSUM = POWERSUM*a; SET COUNT=COUNT+1; END WHILE; RETURN POWERSUM; END | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec)
指令說明:
- DELIMITER // //
mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述. - DELIMITER ;
把結束符號修改回為 分號 “;” . - CREATE FUNCTION looptest (a INT ,b INT)
建立一個函數,須包含函數名稱與傳入值. - RETURNS INT
回傳值為整數. - RETURN POWERSUM;
回傳值. - BEGIN END
函數程式宣告必須包含在裡面.
測試一下 looptest 函數執行結果.
MariaDB [testdb]> SELECT looptest(2,3) AS Power; +-------+ | Power | +-------+ | 8 | +-------+ 1 row in set (0.001 sec)
REPEAT
這次使用 REPEAT.
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
MariaDB [testdb]> DROP FUNCTION IF EXISTS looptest;
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE FUNCTION looptest (a INT ,b INT)
-> RETURNS INT
-> BEGIN
-> DECLARE COUNT ,POWERSUM INT;
-> SET COUNT = 0;
-> SET POWERSUM = 1;
-> REPEAT
-> SET POWERSUM = POWERSUM*a;
-> SET COUNT=COUNT+1;
-> UNTIL COUNT >= b END REPEAT;
-> RETURN POWERSUM;
-> END//
Query OK, 0 rows affected (0.002 sec)
MariaDB [testdb]> DELIMITER ;
測試一下 looptest 函數執行結果.
MariaDB [testdb]> SELECT looptest(3,4) AS Power; +-------+ | Power | +-------+ | 81 | +-------+ 1 row in set (0.001 sec)
LOOP & ITERATE , LEAVE
這次使用 LOOP.
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP 沒有像是 WHILE 或 REPEAT 可以定義 search_condition 來決定是否執行迴圈內的指令,它需搭配 ITERATE (繼續) , LEAVE (離開) 來使用.
- ITERATE : 回到有定義標籤的流控制結構 (flow control construct) 的開頭.
- LEAVE : 用於退出有定義標籤的流控制結構 (flow control construct), 如果標籤是定義於最外面存儲的程序時，則退出程序.
只要有定義 Label 不只 LOOP, 其他 REPEAT 與 WHILE 都可以使用 ITERATE 與 LEAVE.
MariaDB [testdb]> DROP FUNCTION IF EXISTS looptest;
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE FUNCTION looptest (a INT ,b INT)
-> RETURNS INT
-> BEGIN
-> DECLARE COUNT ,POWERSUM INT;
-> SET COUNT = 0;
-> SET POWERSUM = 1;
-> PowerLabel: LOOP
-> SET POWERSUM = POWERSUM*a;
-> SET COUNT=COUNT+1;
-> IF COUNT < b THEN ITERATE PowerLabel; END IF;
-> LEAVE PowerLabel;
-> END LOOP PowerLabel;
-> RETURN POWERSUM;
-> END//
Query OK, 0 rows affected (0.002 sec)
MariaDB [testdb]> DELIMITER ;
測試一下 looptest 函數執行結果.
MariaDB [testdb]> SELECT looptest(4,5) AS Power; +-------+ | Power | +-------+ | 1024 | +-------+ 1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容