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

whitek

　　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]