case

SQL/PL

Case statement

Syntax

case_stmt:
          CASE value_expr
               {WHEN = value_expr  THEN statement_list}...
               [ELSE {OKAY | statement_list}]
          END CASE
     |    CASE
               {WHEN conditional_expr  THEN statement_list}...
               [ELSE {OKAY | statement_list}]
          END CASE

Description

Two forms of the case statement are provided. In the first form, the case expression is evaluated and the statement_list of the first when for which its associated expression evaluates to that same value is executed. In the second form, the statement_list of the first when conditional_expr that evaluates to true is executed. In both forms, if none of the specified when clauses satisfies the CASE and no ELSE clause is specified then a "case violation" exception is raised (required by the standard). If an ELSE clause is specified then either its statement_list is executed or, if OKAY is specified, the case completes successfully.

Note that the ELSE OKAY clause is a non-standard RaimaDB SQL extension.

Both forms of the CASE statement are used in the showcase procedure below. Note also that the CREATE PROCEDURE explicitly specifies that this procedure is associated with the bookshop database even though it does not contain any statements that access the database.

Example

create procedure bookshop.showcase(in a1 integer, in a2 integer)
begin
    select "a1 = ", a1;
    select "a2 = ", a2;
    select "a1 + a2 = ", a1 + a2;
    case a1+a2
        when = 1 then
            select "a1 + a2 = 1";
        when = 2 then
            select "a1 + a2 = 2";
        when = 4 then
            select "a1 + a2 = 4";
        when = 100 then
            select "a1 + a2 = 100";
        else
            select "a1 + a2 != 1, 2, 4, 100";
            case
                when a1 = 2 * a2 then
                    select "a1 = 2 * a2";
                when a1 = 3 * a2 then
                    select "a1 = 3 * a2";
                when a1 < a2 then
                    select "a1 < a2";
                else okay
            end case;
    end case;
end;