SQL – WHILE , REPEAT , LOOP & LEAVE , ITERATE

多筆的 SQL 敘述,可以儲存成為 Stored Routines (Procedure 或是 function),.

在 Stored Routines 會常用到 SQL Flow Control Statements 除了 CASE , IF (詳細請參考 – http://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 這個 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)

定義 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 ;

建立好 Function 之後,可以透過下面指令來檢視 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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料