There are two types of expressions used in CASE statements: simple and searched.
A simple CASE statement chooses which of several sequences of PL/SQL statements to execute based on the results of a single expression.
Simple CASE statements take the following form:
CASE expression -- w ww .j av a 2 s . c o m WHEN result1 THEN statements1 WHEN result2 THEN statements2 ... ELSE statements_else END CASE;
A simple CASE expression selects a result from one or more alternatives, and returns the result.
DECLARE -- from w w w.ja va 2 s .c om grade CHAR(1) := 'A'; v_result VARCHAR2(20); BEGIN v_result := CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' END; DBMS_OUTPUT.PUT_LINE (v_result); END; /
The code above generates the following result.
The optional ELSE clause works the same way to the ELSE clause in an IF statement.
If the value of the selector is covered by a WHEN clause, the ELSE clause is executed.
If no ELSE clause is provided and none of the WHEN clauses are matched, the expression returns NULL.
A searched CASE expression lets you test different conditions instead of comparing a single expression to various values.
A searched CASE expression has no selector.
A searched CASE statement evaluates a list of Boolean expressions, when it finds an expression that evaluates to TRUE, executes a sequence of statements associated with that expression.
Searched CASE statements have the following form:
CASE -- from w w w . ja v a 2s .com WHEN expression1 THEN statements1 WHEN expression2 THEN statements2 ... ELSE statements_else END CASE;
-- from ww w. j a v a2s . co m CASE WHEN salary >= 100 AND salary <=200 THEN raise_salary(employee_id, 1500); WHEN salary > 200 AND salary <= 400 THEN raise_salary(employee_id, 1000); WHEN salary > 400 THEN raise_salary(employee_id, 500); ELSE raise_salary(employee_id, 0); END CASE;
CASE statements can be nested just as IF statements can.
CASE -- w w w.j av a 2 s . co m WHEN salary >= 100 THEN CASE WHEN salary <= 200 THEN raise_salary(employee_id, 1500); WHEN salary > 400 THEN raise_salary(employee_id, 500); WHEN salary > 200 THEN raise_salary(employee_id, 1000); END CASE; WHEN salary < 100 THEN raise_salary(employee_id,0); END CASE;