mysql預存程序學習筆記–區塊,條件,迴圈

塊定義

[label:] BEGIN variable and condition declarations cursor declarations handler declarations program code END[label];

 

可使用 LEAVE [label]來跳出塊

例:

mysql> CREATE PROCEDURE nested_blocks5(  )
 outer_block: BEGIN
        DECLARE l_status int;
        SET l_status=1;
        inner_block: BEGIN
                IF (l_status=1) THEN
                        LEAVE inner_block;
                END IF;
        SELECT 'This statement will never be executed';
        END inner_block;
        SELECT 'End of program';
END outer_block

結果:
mysql> CALL nested_blocks5(  )$$
+----------------+
| End of program |
+----------------+
| End of program |
 

註：如果在命令列建立預存程序，可能需要用DELIMITER 命令將改變預設的命令結束符;改為其它符號，如 DELIMITER $$

 

塊的嵌套:塊可以嵌套出現，但需注意其中變數的有效範圍

1、  塊內聲明的變數，在塊的外部是不存在的

mysql> CREATE PROCEDURE nested_blocks1(  )
BEGIN
        BEGIN
           DECLARE inner_variable VARCHAR(20);
           SET inner_variable='This is my private data';
        END;
        SELECT inner_variable;
END;

結果:
mysql> CALL nested_blocks1(  )
ERROR 1054 (42S22): Unknown column 'inner_variable' in 'field list'
 

2、  塊內可以覆蓋塊外聲明的同名變數

mysql> CREATE PROCEDURE nested_blocks2(  )
BEGIN
        DECLARE my_variable varchar(20);
        SET my_variable='This value was set in the outer block';
        BEGIN
           SET my_variable='This value was set in the inner block';
        END;
        SELECT my_variable, 'Changes in the inner block are visible in the outer block';
END;
結果:
mysql> CALL nested_blocks2(  )
+---------------------+-----------------------------------------------------------+
| my_variable        | Changes in the inner block are visible in the outer block |
+---------------------+-----------------------------------------------------------+
| This value was set  |                                                           |
|  in the inner block | Changes in the inner block are visible in the outer block |
+---------------------+-----------------------------------------------------------+
3、  塊內聲明的變數的改變，不會影響到塊外聲明的同名變數
mysql> CREATE PROCEDURE nested_blocks3(  )
BEGIN
        DECLARE my_variable varchar(20);
        SET my_variable='This value was set in the outer block';
        BEGIN
            DECLARE my_variable VARCHAR(20);
            SET my_variable='This value was set in the inner block';
        END;
        SELECT my_variable, 'Can''t see changes made in the inner block';
END;

結果:

mysql> CALL nested_blocks3(  )$$
+---------------------------+-------------------------------------------+
| my_variable               | Can't see changes made in the inner block |
+---------------------------+-------------------------------------------+
| This value was set in the |                                           |
|   outer block             | Can't see changes made in the inner block |
+---------------------------+-------------------------------------------+
 

條件陳述式
 
IF THEN語句

IF expression THEN commands [ELSEIF expression THEN commands ....] [ELSE commands] END IF;

 
該語句可以嵌套
例:
IF (sale_value > 200) THEN    CALL free_shipping(sale_id);   
    IF (customer_status='PLATINUM') THEN
        CALL apply_discount(sale_id,20);
    ELSEIF (customer_status='GOLD') THEN
        CALL apply_discount(sale_id,15);
    ELSE        CALL apply_discount(sale_id,5);
    END IF;
END IF;
 
Case語句
Case語句有兩種文法

文法一:

CASE expression WHEN value THEN statements [WHEN value THEN statements ...] [ELSE statements] END CASE;

 
 
文法二:

CASE WHEN condition THEN statements [WHEN condition THEN statements...] [ELSE statements] END CASE;

  
 
迴圈語句
 
loop迴圈
 

[label:] LOOP statements END LOOP [label];

 
 
loop迴圈不會自動結束，須用LEAVE來跳出, 同樣地，使用ITERATE label;來執行下一迴圈，類似一般程式設計語言的continue
例
SET i=0;
loop1: LOOP
    SET i=i+1;
    IF i>=10 THEN           LEAVE loop1;
    ELSEIF MOD(i,2)=0 THEN  ITERATE loop1;
    END IF;
    SELECT CONCAT(i," is an odd number");
END LOOP loop1;
 
REPEAT ... UNTIL迴圈
  

[label:] REPEAT statements UNTIL expression END REPEAT [label]

 
當expression為true時跳出，效果同:

some_label:LOOP statements IF expression THEN LEAVE some_label; END IF; END LOOP;

 
 
While迴圈
 

[label:] WHILE expression DO statements END WHILE [label]

類似REPEAT .. UNTIL,區別在於while先判斷條件再執行語句，REPEAT..UNTIL先執行語句，再判斷條件