mysql 流程控制方法

目录

case语法

IF语法

REPEAT语法

while语法

---

case语法

语法一

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

语法二

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

相关案例

select (case store_type when 3 then 3 else 'other' end ) as test_case from store;

| test_case |
+-----------+
| 3         |
| 3         |
| other     |
| 3         |
| other     |
| other     |
| other     |
| other     |
+-----------+

IF语法

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

案例介绍

DELIMITER //

CREATE FUNCTION VerboseCompare (n INT, m INT)
  RETURNS VARCHAR(50)

  BEGIN
    DECLARE s VARCHAR(50);

    IF n = m THEN SET s = 'equals';
    ELSE
      IF n > m THEN SET s = 'greater';
      ELSE SET s = 'less';
      END IF;

      SET s = CONCAT('is ', s, ' than');
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m, '.');

    RETURN s;
  END //

DELIMITER ;

//使用
select VerboseCompare(10,9);
+-----------------------+
| VerboseCompare(10,9)  |
+-----------------------+
| 10 is greater than 9. |
+-----------------------+

REPEAT语法

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

案例介绍

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
       BEGIN
         SET @x = 0;
         REPEAT
           SET @x = @x + 1;
         UNTIL @x > p1 END REPEAT;
       END
       //

mysql> CALL dorepeat(1000)//

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+

while语法

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

案例介绍

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END;