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_valueWHEN 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:] LOOPstatement_listEND 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:] REPEATstatement_listUNTIL search_conditionEND REPEAT [end_label]--举例REPEATFETCH 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 DOstatement_listEND WHILE [end_label]
?