• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Using CASE statements and CASE expressions in PL/SQL

by admin

You have learned to write PL/SQL blocks containing declarative and executable sections. You have also learned to include expressions and SQL statements in the executable block. In this post, you will learn how to use control structures such as CASE statements in a PL/SQL block.

CASE Expressions

A CASE expression returns a result based on one or more alternatives. To return the result, the CASE expression uses a selector, which is an expression whose value is used to return one of several alternatives. The selector is followed by one or more WHEN clauses that are checked sequentially. The value of the selector determines which result is returned. If the value of the selector equals the value of a WHEN clause expression, that WHEN clause is executed and that result is returned.

CASE selector
   WHEN expression1 THEN result1
   [WHEN expression2 THEN result2
   ...
   WHEN expressionN THEN resultN]
   [ELSE resultN+1]
END;

PL/SQL also provides a searched CASE expression, which has the form:

CASE
  WHEN search_condition1 THEN result1
  [WHEN search_condition2 THEN result2
  ...
  WHEN search_conditionN THEN resultN]
  [ELSE resultN+1]
END;

A searched CASE expression has no selector. Furthermore, the WHEN clauses in CASE expressions contain search conditions that yield a Boolean value rather than expressions that can yield a value of any type.

CASE Expressions: Example

Consider the example shown below:

SET VERIFY OFF
DECLARE
  v_grade CHAR(1) := UPPER('&grade');
  v_appraisal VARCHAR2(20);
BEGIN
  v_appraisal := CASE v_grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      ELSE 'No such grade'
    END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade ||
                  'Appraisal' || v_appraisal);
END;
/

In the example above, the CASE expression uses the value in the v_grade variable as the expression. This value is accepted from the user by using a substitution variable. Based on the value entered by the user, the CASE expression returns the value of the v_appraisal variable based on the value of the v_grade value.

Result
When you enter a or A for v_grade, as shown in the Substitution Variable window, the output of the example is as follows:

anonymous block completed
Grade: A   Appraisal Excellent

Searched CASE Expressions

Consider the example shown below:

DECLARE
  v_grade CHAR(1) := UPPER('&grade');
  v_appraisal VARCHAR2(20);
BEGIN
  v_appraisal := CASE
      WHEN v_grade = 'A' THEN 'Excellent'
      WHEN v_grade IN ('B','C') THEN 'Good'
      ELSE 'No such grade'
    END;
  DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade ||
                     ‘ Appraisal ' || v_appraisal);
END;
/

In the previous example, you saw a single test expression, the v_grade variable. The WHEN clause compares a value against this test expression. In searched CASE statements, you do not have a test expression. Instead, the WHEN clause contains an expression that results in a Boolean value. The same example is rewritten above to show searched CASE statements.

Output
The output of the example is as follows when you enter b or B for v_grade:

anonymous block completed
Grade: B   Appraisal Good

CASE Statement

Conside the example shown below:

DECLARE
  v_deptid NUMBER;
  v_deptname VARCHAR2(20);
  v_emps NUMBER;
  v_mngid NUMBER:= 108;
BEGIN
  CASE v_mngid
    WHEN 108 THEN
      SELECT department_id, department_name
      INTO v_deptid, v_deptname FROM departments
      WHERE manager_id=108;
      SELECT count(*) INTO v_emps FROM employees
      WHERE department_id=v_deptid;
      WHEN 200 THEN
      ...
  END CASE;
DBMS_OUTPUT.PUT_LINE ('You are working in the '|| v_deptname||
' department. There are '||v_emps ||' employees in this department');
END;
/

Recall the use of the IF statement. You may include n number of PL/SQL statements in the THEN clause and also in the ELSE clause. Similarly, you can include statements in the CASE statement, which is more readable compared to multiple IF and ELSIF statements.

How a CASE Expression Differs from a CASE Statement

A CASE expression evaluates the condition and returns a value, whereas a CASE statement evaluates the condition and performs an action. A CASE statement can be a complete PL/SQL block.

– CASE statements end with END CASE;
– CASE expressions end with END;

The output of the example code above is as follows:

anonymous block completed
You are working in the Finance department. There are 6 employees in this department.
Note: Whereas an IF statement is able to do nothing (the conditions could be all false and the ELSE clause is not mandatory), a CASE statement must execute some PL/SQL statement.

Filed Under: oracle, PL/SQL

Some more articles you might also be interested in …

  1. Using Explicit Cursors in PL/SQL
  2. Types of Workload Distribution (Load Balancing) in Oracle RAC
  3. Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value
  4. How to Optimize a Data Guard Configuration
  5. ASMLib-Managed Disks on Multipathed iSCSI Targets are not Discovered after Server Reboot in CentOS/RHEL 7
  6. How to upgrade Oracle database to 12c Pluggable database (Multitenant) using Transportable tablespace(TTS) method
  7. How to Enable Fast-Start Failover in Oracle Data Guard
  8. Basics of PL/SQL LOOPs
  9. How To Recover From Lost SYS Password in Oracle Database
  10. Where to find ASMLib / oracleasm RPMs for CentOS/RHEL, SUSE, OEL

You May Also Like

Primary Sidebar

Recent Posts

  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found
  • macof: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright