MySQL存储过程之代码块、条件控制、迭代

1，代码块
代码顺序

1，变量和条件声明
2，Cursor声明
3，Handler声明
4，程序代码

可以给代码块加lebel，这样END匹配比较直观，还可以用LEAVE语句来终结代码块:

[label:] BEGIN
    varaiable and condition declarations
    cursor declarations
    handler declarations

    program code
END [label];

代码块可以嵌套:

CREATE PROCEDURE nested_blocks()
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;

LEAVE的例子:

CREATE PROCEDURE nested_blocks()
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;

2，条件控制
IF:

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

例子:

IF (sale_value > 200) THEN
    CALL free_shipping(sale_id);    /*Free shipping*/
    IF (customer_status='PLATINUM') THEN
        CALL apply_discount(sale_id,20); /* 20% discount */
    ELSEIF (customer_status='GOLD') THEN
        CALL apply_discount(sale_id,15); /* 15% discount */
    ELSEIF (customer_status='SILVER') THEN
        CALL apply_discount(sale_id,10); /* 10% discount */
    ELSEIF (customer_status='BRONZE') THEN
        CALL apply_discount(sale_id,5); /* 5% discount*/
    END IF;
END IF;

CASE:

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

例子:

CASE
    WHEN (sale_value>200) THEN
        CALL free_shipping(sale_id);
        CASE customer_status
            WHEN 'PLATINUM' THEN
                CALL apply_discount(sale_id,20);
            WHEN 'GOLD' THEN
                CALL apply_discount(sale_id,15);
            WHEN 'SILVER' THEN
                CALL apply_discount(sale_id,10);
            WHEN 'BRONZE' THEN
                CALL apply_discount(sale_id,5);
        END CASE;
END CASE;

CASE与SELECT语句结合的妙用:

SELECT (CASE WHEN (t.a = 1 AND t.b = 0) THEN t.c ELSE 'N/A' END) AS result FROM test t order by result asc

3，迭代
LOOP

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

REPEAT...UNTIL

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

WHILE

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

LEAVE语句

SET i=1;
myloop: LOOP
    SET i=i+1;
    IF i=10 then
        LEAVE myloop;
    END IF:
END LOOP myloop;
SELECT 'I can count to 10';

ITERATE语句

SET i=0;
loop1: LOOP
    SET i=i+1;
    IF i>=10 THEN                 /*Last number - exit loop*/
        LEAVE loop1;
    ELSEIF MOD(i, 2)=0 THEN       /*Even number - try again*/
        ITERATE loop1;
    END IF;

    SELECT CONCAT(i, " is an odd number");
END LOOP loop1;

嵌套循环

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;