All for Joomla All for Webmasters

PL/SQL Case Statement

The PL/SQL CASE statement facilitates you to execute a sequence of satatements based on a selector. A selector can be anything such as variable, function or an expression that the CASE statement checks to a boolean value.

The CASE statement works like the IF statement, only using the keyword WHEN. A CASE statement is evaluated from top to bottom. If it get the condition TRUE, then the corresponding THEN calause is executed and the execution goes to the END CASE clause.

Syntax for the CASE Statement:

  1. CASE [ expression ]
  2. WHEN condition_1 THEN result_1
  3.    WHEN condition_2 THEN result_2
  4.    …
  5.    WHEN condition_n THEN result_n
  6.  ELSE result
  7. END

Example of PL/SQL case statement

Let’s take an example to make it clear:

  2.    grade char(1) := ‘A’;
  3. BEGIN
  4.    CASE grade
  5.       when ‘A’ then dbms_output.put_line(‘Excellent’);
  6.       when ‘B’ then dbms_output.put_line(‘Very good’);
  7.       when ‘C’ then dbms_output.put_line(‘Good’);
  8.       when ‘D’ then dbms_output.put_line(‘Average’);
  9.       when ‘F’ then dbms_output.put_line(‘Passed with Grace’);
  10.       else dbms_output.put_line(‘Failed’);
  11.    END CASE;
  12. END;

After the execution of above code, you will get the following result:

PL/SQL procedure successfully completed.
submit to reddit

Leave a Reply