The CASE statement in Oracle PL/SQL is a control flow statement that allows you to conditionally execute one or more statements based on a specified condition or expression. The basic syntax for a CASE statement is as follows:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE result_else END CASE;
In the above syntax, the conditions are evaluated in the order they are specified. When a condition evaluates to TRUE, the corresponding result is executed and the CASE statement is exited. If none of the conditions evaluate to TRUE, then the ELSE result is executed.
CASE can also be used in SELECT statement as an expression.
SELECT column1, column2, CASE column3 WHEN 'value1' THEN 'result1' WHEN 'value2' THEN 'result2' ELSE 'result_else' END as column4 FROM table_name;
CASE statement can also be used in SQL statement as well.
SELECT ename, job, CASE job WHEN 'MANAGER' THEN 'MANAGERIAL' WHEN 'SALESMAN' THEN 'SALES' ELSE 'OTHER' END "JOB CATEGORY" FROM emp;
DECLARE v_num NUMBER:=5; v_out VARCHAR2(12); BEGIN CASE v_num WHEN 1 THEN v_out:='A'; WHEN 5 THEN v_out:='B'; WHEN 8 THEN v_out:='C'; ELSE v_out:='D'; END CASE; DBMS_OUTPUT.put_line(v_out); END;
CASE statement can be a powerful tool for adding logic and complexity to your PL/SQL programs and SQL statements.