MySQL存储过程之流程控制

1. 条件分支IF-THEN-ELSE-END IF

CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = parameter1 + 1;
    IF variable1 = 0 THEN
        INSERT INTO t VALUES (17);
    END IF;
    IF parameter1 = 0 THEN
        UPDATE t SET s1 = s1 + 1;
    ELSE
        UPDATE t SET s1 = s1 + 2;
    END IF;
END; //

2. CASE指令

CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = parameter1 + 1;
    CASE variable1
    WHEN 0 THEN
        INSERT INTO t VALUES (17);
    WHEN 1 THEN
        INSERT INTO t VALUES (18);
    ELSE
        INSERT INTO t VALUES (19);
    END CASE;
END; // 

3. Loops循环

　　1) WHILE...END WHILE

CREATE PROCEDURE p14 ()
BEGIN  
    DECLARE v INT;    
    SET v = 0;  
    WHILE v < 5 DO  
        INSERT INTO t VALUES (v);
        SET v = v + 1;
    END WHILE;
END; // 

　　2) REPEAT...END REPEAT

CREATE PROCEDURE p15 ()
BEGIN
    DECLARE v INT;
    SET v = 0;
    REPEAT
        INSERT INTO t VALUES (v);
        SET v = v + 1;
        UNTIL v >= 5    /* 此处引号可省,也可不写 */
    END REPEAT;
END; //

　　3) LOOP...END LOOP

CREATE PROCEDURE p16 ()
BEGIN
    DECLARE v INT;
    SET v = 0;
    loop_label: LOOP
        INSERT INTO t VALUES (v);
        SET v = v + 1;
        IF v >= 5 THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END; //

　　a. LOOP循环与WHILE相似,不需要初始条件,同时又与REPEAT循环一样没有结束条件.在循环开始的loop_label:用于标识该循环,而IF结构里的LEAVE loop_label表示离开循环.

　　b. Labels标号可用在BEGIN,WHILE,REPEAT或者LOOP之前,语句标号只能在合法的语句前使用,所以,LEAVE <标号名称>意味着离开与该标号对应的语句或复合语句:

CREATE PROCEDURE p17 ()
label_1: BEGIN
    label_2: WHILE 0 = 1 DO
        LEAVE label_2;
    END  WHILE;
    label_3: REPEAT 
        LEAVE label_3; 
        UNTIL 0 =0  
    END REPEAT;
    label_4: LOOP 
        LEAVE label_4; 
    END LOOP;
END; //     

　　c. End Labels标号结束符,可以用在在由标号定义的语句结束之后,无功能性作用,只起到说明的作用:

CREATE PROCEDURE p18 ()
label_1: BEGIN
    label_2: WHILE 0 = 1 DO
        LEAVE label_2; 
    END  WHILE label_2;
    label_3: REPEAT
        LEAVE label_3; 
        UNTIL 0 =0  
    END REPEAT label_3
    label_4: LOOP
        LEAVE label_4;
    END LOOP  label_4
END label_1  //    

　　d. LEAVE and Labels:LEAVE语句使程序跳出复杂的复合语句:

CREATE PROCEDURE p19 (parameter1 CHAR)
label_1: BEGIN
    label_2: BEGIN
        label_3: BEGIN
            IF parameter1 IS NOT NULL THEN
                IF parameter1 = 'a' THEN
                    LEAVE label_1;
                ELSE 
                    BEGIN
                        IF parameter1 = 'b' THEN
                            LEAVE label_2;
                        ELSE
                            LEAVE label_3;
                        END IF;
                    END;
                END IF;
            END IF;
        END;
    END;
END;//

　　e. ITERATE迭代:如果目标是迭代语句,就必须用到LEAVE语句,ITERATE和LEAVE语句一样可以在循环内部使用,类似c语言的continue:

CREATE PROCEDURE p20 ()
BEGIN 
    DECLARE v INT;
    SET v = 0; 
    loop_label: LOOP
        IF v = 3 THEN
            SET v = v + 1;
            ITERATE loop_label;
        END IF;
        INSERT INTO t VALUES (v);
        SET v = v + 1;
        IF v >= 5 THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END; // 

　　4) GOTO

CREATE PROCEDURE p...  
BEGIN
    ...
    LABEL label_name;
    ...
    GOTO label_name;
    ...
END;

　　虽然不是标准的SQL语句,MySQL的存储过程中仍然可以使用GOTO语句,此处标号的使用与前面不同,出于和其他DBMS兼容,此处用法慢慢被淘汰.