Mysql -流程控制

流程控制
Flow Control Statements

MySQL支持 IF, CASE, ITERATE, LEAVE LOOP, WHILE, 和 REPEAT 语句进行流程控制
MySQL不支持FOR LOOPS循环

1.CASE语句

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE

或

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE

不同于case表达式，case语句不支持else null子句，并且case语句通过end case结束而非end

DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;
  END;
  |

DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE 
      WHEN V=2 THEN SELECT v;
      WHEN V=3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;
  END;
  |

2.IF语句

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF

DELIMITER //

CREATE FUNCTION SimpleCompare(n INT, m INT)
  RETURNS VARCHAR(20)

  BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;
  END //

DELIMITER ;

与oracle唯一不同应该就是elseif，oracle是elsif

3.ITERATE语句

ITERATE label

ITERATE只能出现在LOOP,REPEAT和WHILE语句中。ITERATE表示“start the loop again.”
类似于oracle的continue

delimiter $$

create procedure odd_even()
begin
    set @a = 10;
    hehe:while @a > 0 do
        if @a % 2 = 0 then
            select @a;
            set @a = @a - 1;
        else
            set @a = @a - 1;
            ITERATE hehe;
        end if;
    end while hehe;
end $$

delimiter ;

create procedure actor_insert()
begin
    set @x=0;
    ins:loop
        set @x=@x+1;
        if @x=10 then
            leave ins;
        elsif mod(@x,2)=0 then
            iterate ins;
        end if;
        insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x);
    end loop ins;
end;
$$

4.LEAVE语句

LEAVE label

leave语句用于退出指定label的流程控制结构。如果label是最外层的stored program block，leave退出程序
leave可以使用在begin..end和循环结构中

5.LOOP语句

[begin_label:] LOOP
statement_list
END LOOP [end_label]

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END;

6.REPEAT语句

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

有条件的循环控制语句，当满足条件时推出循环

delimiter $$
create procedure dorepear(in p1 int)
begin
	set @x = 0;
	repeat
		set @x=@x+1;
	until @x>p1 end repeat;
end$$
delimiter ;

mysql> select @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

7.RETURN语句

RETURN expr

return语句结束函数并返回expr值给函数调用者。函数中至少要有一个RETURN语句。There may be more than one if the function has multiple exit points.

RETURN语句只用在函数中，LEAVE语句用于procedure，trigger和event中

8.WHILE语句

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

在while中的语句将持续循环直到search_condition=true
statement_list可以包含一至多个sql语句，每一个sql语句以 ‘;’结束

while语句可以打标签label

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END;