mysql 存储过程 goto_MySQL存储过程之流程控制

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

1 CREATE PROCEDURE p12 (IN parameter1 INT)2 BEGIN

3 DECLARE variable1 INT;4 SET variable1 = parameter1 + 1;5 IF variable1 = 0 THEN

6 INSERT INTO t VALUES (17);7 END IF;8 IF parameter1 = 0 THEN

9 UPDATE t SET s1 = s1 + 1;10 ELSE

11 UPDATE t SET s1 = s1 + 2;12 END IF;13 END; //

2. CASE指令

1 CREATE PROCEDURE p13 (IN parameter1 INT)2 BEGIN

3 DECLARE variable1 INT;4 SET variable1 = parameter1 + 1;5 CASEvariable16 WHEN 0 THEN

7 INSERT INTO t VALUES (17);8 WHEN 1 THEN

9 INSERT INTO t VALUES (18);10 ELSE

11 INSERT INTO t VALUES (19);12 END CASE;13 END; //

3. Loops循环

1) WHILE...END WHILE

1 CREATE PROCEDUREp14 ()2 BEGIN3 DECLARE v INT;4 SET v = 0;5 WHILE v 

2) REPEAT...END REPEAT

1 CREATE PROCEDUREp15 ()2 BEGIN

3 DECLARE v INT;4 SET v = 0;5 REPEAT6 INSERT INTO t VALUES(v);7 SET v = v + 1;8 UNTIL v >= 5 /*此处引号可省,也可不写*/

9 ENDREPEAT;10 END; //

3) LOOP...END LOOP

1 CREATE PROCEDUREp16 ()2 BEGIN

3 DECLARE v INT;4 SET v = 0;5 loop_label: LOOP6 INSERT INTO t VALUES(v);7 SET v = v + 1;8 IF v >= 5 THEN

9 LEAVE loop_label;10 END IF;11 ENDLOOP;12 END; //

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

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

1 CREATE PROCEDUREp17 ()2 label_1: BEGIN

3 label_2: WHILE 0 = 1DO4 LEAVE label_2;5 END WHILE;6 label_3: REPEAT7 LEAVE label_3;8 UNTIL 0 =0

9 ENDREPEAT;10 label_4: LOOP11 LEAVE label_4;12 ENDLOOP;13 END; //

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

1 CREATE PROCEDUREp18 ()2 label_1: BEGIN

3 label_2: WHILE 0 = 1DO4 LEAVE label_2;5 END WHILElabel_2;6 label_3: REPEAT7 LEAVE label_3;8 UNTIL 0 =0

9 ENDREPEAT label_310 label_4: LOOP11 LEAVE label_4;12 ENDLOOP label_413 END label_1 //

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

1 CREATE PROCEDURE p19 (parameter1 CHAR)2 label_1: BEGIN

3 label_2: BEGIN

4 label_3: BEGIN

5 IF parameter1 IS NOT NULL THEN

6 IF parameter1 = 'a' THEN

7 LEAVE label_1;8 ELSE

9 BEGIN

10 IF parameter1 = 'b' THEN

11 LEAVE label_2;12 ELSE

13 LEAVE label_3;14 END IF;15 END;16 END IF;17 END IF;18 END;19 END;20 END;//

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

1 CREATE PROCEDUREp20 ()2 BEGIN

3 DECLARE v INT;4 SET v = 0;5 loop_label: LOOP6 IF v = 3 THEN

7 SET v = v + 1;8 ITERATE loop_label;9 END IF;10 INSERT INTO t VALUES(v);11 SET v = v + 1;12 IF v >= 5 THEN

13 LEAVE loop_label;14 END IF;15 ENDLOOP;16 END; //

4) GOTO

1 CREATE PROCEDUREp...2 BEGIN

3 ...4 LABEL label_name;5 ...6 GOTOlabel_name;7 ...8 END;

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