MySQL

zhouandtao

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
Section11.3, “Control Flow Functions”. The
CASE
statement cannot have an ELSE NULL clause,
and it is terminated with END CASE instead
of END.




[MySQL - 5.1 Reference Manual 参考手册 - 12 SQL Statement Syntax
- SQL语句语法 - 12.8 MySQL Compound-Statement Syntax - MySQL复合语句语法 - 12.8.6
Flow Control Constructs流程控制结构 -12.8.6.2. CASE Statement - CASE语句 - 文档]
　　http://dev.mysql.com/doc/refman/5.1/en/case-statement.html

[MySQL - 5.1 Reference Manual参考手册]
　　MySQL
- 5. 数据库管理 - 5.3. mysqld：MySQL服务器 - 5.3.3. 服务器系统变量 -
max_allowed_packet,
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#server-system-variables
　　9.
语言结构 - 9.2. 数据库、表、索引、列和别名 - 9.2.2. 识别符大小写敏感性,
http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html#name-case-sensitivity
13. SQL语句语法 - 13.1.数据定义语句 - 13.1.5.CREATE TABLE语法, http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#create-table
12
SQL Statement Syntax - SQL语句语法 - 12.8 MySQL Compound-Statement Syntax -
MySQL复合语句语法 - 12.8.6 Flow Control Constructs流程控制结构 -12.8.6.2. CASE
Statement - CASE语句,
http://dev.mysql.com/doc/refman/5.1/en/case-statement.html