Mysql 流程控制

Mysql 流程控制语句

IF语句

IF search_condition THENstatement_list
[ELSEIF search_condition THEN ``statement_list] ...
[ELSEstatement_list]
END IF“`

if-then-elseif-then-else-end if; 这里就不啰嗦了！

CASE语句

CASE case_value
WHEN when_value THENstatement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE；
使用场景：比较对象case_value值与分支的值相同
Or:

CASE
WHEN search_condition THENstatement_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);