Mysql那些事儿之（十五）流程的控制

IF语句

---语法结构
IF search_condition THEN statement_list
	[ELSEIF search_condition THEN statement_list]....
        [ELSE statement_list]
END IF

---举例
if i_staff_id = 2 then
  set @x1 = @x1 + d_amount;
else
  set @x2 = @x2 + d_amount;
end if;

CASE语句

---CASE语句的语法格式
CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]....
	[ELSE statement_list]
END CASE

---case语句举例：
case
  when i_staff_id = 2 then 
    set @x1 = @x1 + d_amount;
  else
    set @x2 = @x2 + d_amount;
end case

 LOOP语句

[begin_label:] LOOP
	statement_list
END LOOP [end_label]
---如果不在statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环。

 LEAVE语句

---将结束符替换为$$
delimiter $$
---创建存储过程
CREATE PROCEDURE actor_num()
BEGIN
  set @x = 0;
  ins:LOOP
    set @x = @x + 1;
    IF @x = 100 THEN
      leave ins;
    END IF;
    INSERT INTO actor(first_name,last_name) VALUES('TEST',222);
  END LOOP ins;
END;
$$
delimiter ;

 ITERATE语句

--必须用在循环中，作用就是跳过当前的循环直接进入下一轮循环。
delimiter $$
CREATE PROCEDURE actor_num()
BEGIN
  set @x = 0;
  ins:LOOP
    set @x = @x + 1;
    IF @x = 100 THEN
      leave ins;
    ELSEIF mod(@x/2,0) = 0 THEN
      iterate ins;
    END IF;
    INSERT INTO actor(first_name,last_name) VALUES('TEST',222);
  END LOOP ins;
END;
$$
delimiter ;

 REPEAT 语句

--有条件循环，当满足条件的时候退出循环。
--语法：
[begin_label:] REPEAT
	statement_list
UNTIL search_condition
END REPEAT [end_label]
--举例
REPEAT
FETCH cur_payment INTO i_staff_id,d_amount;
  if i_staff_id = 2 then
    set @x1 = @x1 + d_amount;
  else
    set @x2 = @x2 + d_amount;
  end if;
UNTIL 0 END REPEAT;

 WHILE 语句

---语法结构
[begin_label:] WHILE search_condition DO
	statement_list
END WHILE [end_label]