MySQL - 5.1 Reference Manual参考手册 - 12.8.6.2. CASE Statement - CASE语句

最新推荐文章于 2021-04-10 03:28:30 发布

胡争辉

最新推荐文章于 2021-04-10 03:28:30 发布

阅读量1.5k

点赞数

分类专栏： MySQL 文章标签： reference mysql list search sql 数据库

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

MySQL - 5.1 Reference Manual参考手册 - 12.8.6.2. CASE Statement - CASE语句

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

Or:

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

The CASE statement for stored programs implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.

If no when_value or search_condition matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results.

Each statement_list consists of one or more statements; an empty statement_list is not allowed. To handle situations where no value is matched by any WHEN clause, use an ELSE containing an empty BEGIN ... END block, as shown in this example:

DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;
    
    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;
  END;
  |

(The indentation used here in the ELSE clause is for purposes of clarity only, and is not otherwise significant.)

Note

The syntax of the CASE statement used inside stored programs differs slightly from that of the SQL CASE expression described in Section 11.3, “Control Flow Functions”. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.