PL/SQL:CASE Statement

CASE expressions let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.In PL SQL CASE provides a different approach to conditional evaluation.It simplifies the syntax a bit by requiring the condition to be passed only one time.While CASE constructs don’t offer any fundamentally new semantics, they do allow a more compact notation and some elimination of repetition with respect to what otherwise would be expressed with an IF construct.

The syntax is

CASE expr

WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]

END

The CASE Expression

In a simple CASE expression, the Oracle Server searches for the first WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN … THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, the
Oracle Server returns null. You cannot specify the literal NULL for all the return_exprs and the else_expr.

There are two types of CASE expressions: simple and searched. You can use CASE expressions anywhere that expressions are valid in your SQL or PL/SQL programs.

A simple CASE expression lets you choose an expression to evaluate based on a scalar value that you provide as input. The following example shows a simple CASE expression

SET SERVEROUTPUT ON

DECLARE

v_book_category     VARCHAR2(10);

v_discount_percent NUMBER(10,2);

v_isbn NUMBER(10)   := '72230665';

BEGIN

SELECT book_category INTO v_book_category FROM books

WHERE isbn = v_isbn;

-- Determine discount based on category

CASE v_book_category

WHEN 'Oracle Tuning THEN v_discount := .15;

WHEN 'Oracle SQL THEN v_discount := .10;

END CASE;

DBMS_OUTPUT.PUT_LINE('The discount is '||v_discount*100||' percent');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

Using CASE Statement in SELECT Queries

In the below SQL statement, the value of JOB_ID is decoded. If JOB_ID is PROGRAMMER, the salary increase is 10%; if JOB_ID is CLERK, the salary increase is 15%; if JOB_ID is MANAGER, the salary increase is 20%. For all other job roles, there is no increase in salary.

SELECT last_name, job_id, salary,

      CASE job_id WHEN 'PROGRAMMER' THEN 1.10*salary

          WHEN 'CLERK' THEN 1.15*salary

          WHEN 'MANAGER' THEN 1.20*salary

      ELSE salary END "REVISED_SALARY"

FROM employees;