IF Statement

Control Statements

LOOP Statement

CASE Statement (SQL)

statement_CASE ::= simple_CASE_statement | search_CASE_statement
simple_CASE_statement ::= CASE expression { WHEN expression2 THEN statement } [ ELSE statement… ] END CASE;
search_CASE_statement ::= CASE { WHEN condition THEN statement } [ ELSE statement… ] END CASE;

The CASE statement is used for the selection and execution of statements from multiple possibilities. Individual statement orders are specified in separate branches after the WHEN or ELSE keywords.

The expression value is searched for in the branches and compared to the expression2 values in the simple CASE statement. The branch where the condition is fulfilled is searched for in the search CASE statement. The search is always done in the specified order of expressions. If some branch is found, the statements following the THEN keyword specified in this branch are executed.

If no suitable branch is found, the statements following the ELSE keyword are executed if the statement has an ELSE branch. If there is no ELSE branch, an error occurs sqlstate 20000 (SQ_CASE_NOT_FOUND_STMT).

If an error occurs when executing the order of statements, operation continues according to the error handler, if available. If no CONTINUE handler is found, execution is terminated.

602SQL Variations

• Does not require the expression evaluation to be deterministic or without side effects. The expression is evaluated only once.

Example:

Search CASE

TRIGGER befupd BEFORE UPDATE OF NUMBER ON Tab3
REFERENCING NEW ROW AS newrow
FOR EACH ROW
BEGIN
  CASE
    WHEN newrow.number < 0 THEN
      SET newrow.number=-newrow.number;
  END CASE;
END

Simple CASE

PROCEDURE ChangePriceList();
BEGIN 
  FOR row AS curval SENSITIVE CURSOR FOR 
    SELECT item_number, price
    FROM PriceList
  DO 
    BEGIN
      CASE SUBSTRING(row.item_number FROM 1 FOR 1) 
        WHEN 'X' THEN 
          UPDATE  SET price = price*1.1 WHERE CURRENT OF curval;
        WHEN 'A' THEN 
          UPDATE  SET price = price*2 WHERE CURRENT OF curval;
        ELSE
          DELETE WHERE CURRENT OF curval;
      END CASE;
    END;
  END FOR;
END;

IF Statement

Control Statements

LOOP Statement