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先执行语句，再判断条件