MySQL 流程控制的迴圈


2013-05-02_115713

這是MySQL 5實力養成暨評量裡的8-24.『下列何者為預儲程序中流程控制可用的迴圈式?』

答案:(C)WHILE … END WHILE (D)LOOP … END LOOP

這題倒是讓ㄚ琪NC了不少,明明就說是迴圈式了,幹嘛 還多選了一個CASE … END CASE這種判斷式呢?就算無法全知所有的迴圈式,也是可以答這題的,不過現在我們需要舉一反三了,到底預儲程序中可以用哪些mysql 迴圈?

  1. REPEAT
  2. WHILE
  3. LOOP, LEAVE & ITERATE

就是上面這三種mysql loop,在MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.6 MySQL Compound-Statement Syntax :: 13.6.5 Flow Control Statements,朋友們可以參考。

簡體中文手冊可以參考MySQL 5.1參考手冊 :: 20.存儲程序和函數::20.2. 存儲程序的語法::20.2.12. 流程控制構造

轉譯成繁體如下:

IF, CASE, LOOP, WHILE, ITERATE, 及LEAVE 構造被完全實現。

這些構造可能每個包含要嘛一個單獨語句,要嘛是使用BEGIN … END複合語句的一塊語句。構造可以被嵌套。

目前還不支持FOR迴圈。

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語句。請參閱12.2節,“ 控制流程函數”。

20.2.12.2. CASE語句

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

Or:

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

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

注意:這裡介紹的用在預儲程序裡的CASE語句與12.2節,“ 控制流程函數”裡描述的SQL CASE表達式的CASE語句有輕微不同。這裡的CASE語句不能有ELSE NULL子句,並且用END CASE替代END來終止。

20.2.12.3. LOOP語句

[ begin_label :] LOOP
    statement_list
END LOOP [ end_label ]

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

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

20.2.12.4. LEAVE語句

LEAVE label

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

20.2.12.5. ITERATE語句

ITERATE label

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

例如:

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

20.2.12.6. REPEAT語句

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

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

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

例如:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    -> SET @x = 0;   
    -> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;   
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x    |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

20.2.12.7. WHILE語句

[ begin_label :] WHILE search_condition DO
    statement_list
END 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

以上, 我們以語法及實例介紹了流程控制的所有狀況在資料庫的應用中,是很重要的一個環節,一定要搞懂它,認證才能必勝

mysql for loop

前面提過MySQL 不支援 FOR迴圈這種方式,但是還是可以運用前面幾種語法來達成for迴圈的要求,我們先看看while 用法:

drop procedure if exists sum1;
create procedure sum1(a int) 
begin
    declare sum int default 0;  
    declare i int default 1;
    
    while i <= a DO -- 迴圈開始
            set sum = sum + i;
            set i = i + 1;
    end while; -- 迴圈结束

    select sum; -- 印出結果 
end
-- 執行
call sum1(10);

這個是利用迴圈算出等差級數和 1+2+……+10,所以其實也是還可以用,只是沒有for迴圈那麼順。

mysql for loop第二個範例,巢狀迴圈:

DECLARE i,j INT DEFAULT 1;
outer_loop: LOOP
    SET j=1;
    inner_loop: LOOP
        SELECT concat(i," times ", j," is ",i*j);
        SET j=j+1;
        IF j>12 THEN
            LEAVE inner_loop;
        END IF;
    END LOOP inner_loop;
    SET i=i+1;
    IF i>12 THEN
        LEAVE outer_loop;
    END IF;
END LOOP outer_loop;

Stored procedure讀取資料庫欄位迴圈範例

這個mysql loop來自麥克的學習紀錄,範例說明如下:

CREATE PROCEDURE myProc ()

BEGIN

        --定義變數

        DECLARE l_id          INT;

        DECLARE l_code1      CHAR(2);

        DECLARE l_code2      CHAR(2);

        DECLARE l_count      INT;

        DECLARE no_more_maps INT;

       

        --定義資料庫cursor

        DECLARE dept_csr CURSOR FOR

        SELECT id,code1,code2 FROM map;

       

        --宣告一個繼續作業的HANDLER,此HANDLER監聽'NOT FOUND'狀態, 如果出現'NOT FOUND'的狀態時, 就把變數的值設為1

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_maps=1;

       

        --將handler設為初始化設定為0

        SET no_more_maps=0;

       

        --開啟資料庫cursor

        OPEN dept_csr;

       

        --用repeat…until….end repeat的語法(至少會迴圈一次)

        dept_loop:REPEAT

                --用FETCH將cursor逐筆讀入

                FETCH dept_csr INTO l_id,l_code1,l_code2;

                IF no_more_maps=0 THEN

                        SET l_count=l_count+1;

                        select  l_id,l_code1,l_code2;

                END IF;

        UNTIL no_more_maps

        END REPEAT dept_loop;

        CLOSE dept_csr;

        SET no_more_maps=0;

END$$

※2022/04/10 找到一部簡體中文的mysql数据库优化全剖析1- 24 MySQL 高级 存储过程 语法 mysql loop循环 Youtube影片:

※2022/05/22

找到了mysql loop的Youtube影片《MySQL Stored Procedure Beginners Tutorial #11 – Loop Statements MySQL Stored Procedure | While Loop》可以觀看,是英文的,可以將就看一下

更多資料庫好文:

資料庫 正規化
MySQL 註解語法
何謂資料隱碼(SQL injection)攻擊?程式設計師應如何預防?

感謝你看到這裡,很快就可以離開了,但最好的獎勵行動就是按一下幫我分享或留言,感恩喔~

點我分享到Facebook

發佈留言

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