Movatterモバイル変換

CASEcase_value    WHENwhen_value THENstatement_list    [WHENwhen_value THENstatement_list] ...    [ELSEstatement_list]END CASE

Or:

CASE    WHENsearch_condition THENstatement_list    [WHENsearch_condition THENstatement_list] ...    [ELSEstatement_list]END CASE

TheCASE statement for stored programs implements a complex conditional construct.

For the first syntax,case_value is an expression. This value is compared to thewhen_value expression in eachWHEN clause until one of them is equal. When an equalwhen_value is found, the correspondingTHEN clausestatement_list executes. If nowhen_value is equal, theELSE clausestatement_list executes, if there is one.

This syntax cannot be used to test for equality withNULL becauseNULL = NULL is false. SeeSection 5.3.4.6, “Working with NULL Values”.

For the second syntax, eachWHEN clausesearch_condition expression is evaluated until one is true, at which point its correspondingTHEN clausestatement_list executes. If nosearch_condition is equal, theELSE clausestatement_list executes, if there is one.

If nowhen_value orsearch_condition matches the value tested and theCASE statement contains noELSE clause, aCase not found for CASE statement error results.

Eachstatement_list consists of one or more SQL statements; an emptystatement_list is not permitted.

To handle situations where no value is matched by anyWHEN clause, use anELSE containing an emptyBEGIN ... END block, as shown in this example. (The indentation used here in theELSE clause is for purposes of clarity only, and is not otherwise significant.)

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;  |