Mysql 流程控制语句
IF语句
IF
search_condition THEN
statement_list
[ELSEIF
search_condition THEN ``statement_list] ...
ELSE
[statement_list]
END IF“`
if-then-elseif-then-else-end if; 这里就不啰嗦了!
CASE语句
CASE
case_value
WHEN
when_value THEN
statement_list
[WHEN
when_value THEN
statement_list] …
[ELSE
statement_list]
END CASE
;
使用场景:比较对象case_value值与分支的值相同
Or:
CASE
WHEN
search_condition THEN
statement_list
[WHEN
search_condition THEN
statement_list] …
[ELSE
statement_list]
END CASE
使用场景:满足when 条件的分支表达式不确定
LOOP循环语句
[begin_label:] LOOP
statement_list
END LOOP [end_label]
loop -leave-end loop
REPEAT循环语句
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
WHILE语句
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
– 个人建议使用常见的
代码案例
DROP PROCEDURE IF EXISTS procedure_function;
CREATE PROCEDURE procedure_function (in inputparam VARCHAR(64))
BEGIN
DECLARE tableName VARCHAR(64) DEFAULT '';
DECLARE tableId BIGINT(64) DEFAULT 0;
DECLARE num BIGINT(64) UNSIGNED DEFAULT 5;
-- IF
IF num > 1 THEN
SELECT * FROM t_bversion ;
SET num = num - 3;
ELSE
SELECT * FROM t_iteration;
END IF;
-- CASE
CASE num
WHEN 1 THEN
SELECT CONCAT('NUM1=',num);
WHEN 3 THEN
SELECT CONCAT('NUM3=',num);
ELSE
SELECT CONCAT('NUM=',num);
END CASE;
CASE
WHEN num > 0 AND num < 2 THEN
SELECT CONCAT('表达式不确定');
SELECT CONCAT('列表不确定');
WHEN num = 0 OR num = 2 THEN
SELECT CONCAT('NUM=',num);
ELSE
SELECT CONCAT('other=',num);
END CASE;
-- LOOP
lable:LOOP
SET num = num -1;
SELECT CONCAT('Loop循环',num);
IF num != 0 THEN
ITERATE lable; -- 继续循环
END IF;
LEAVE lable; -- 跳出循环
END LOOP lable;
-- REPEAT
SET num = 3;
REPEAT
SELECT CONCAT('REPEAT循环',num);
SET num = num -1;
UNTIL num = 0 -- 为真跳出
END REPEAT;
-- WHILE
SET num = 5;
WHILE num < 0 DO -- 为真循环
SELECT CONCAT('while循环',num);
SET num = num -1;
END WHILE;
END;
CALL procedure_function(NULL);