Friday, August 14, 2020
    Home Sql Tutorial CASE statement in Oracle PL/SQL

    CASE statement in Oracle PL/SQL

    The article explains how to use case statement in PL/SQL without any issue.

    CASE statement in Oracle PL / SQL

    Oracle Database versions 9i and later include the ability to use the CASE statement within an SQL statement (SELECT, UPDATE, and so on). The CASE statement allows you to perform the same operations as the PL / SQL IF-THEN-ELSIF-ELSE control statements, but with the particularity that they can be used within an SQL statement. The syntax of the CASE statement is as follows:

    CASE [expression]
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    WHEN condition_n THEN result_n
    ELSE result

    - Advertisement -

    The expression option is optional. This is the value that we would be comparing with the list of conditions (condition_1, condition_2, …, condition_n).

    The conditions, condition_1 to condition_n, are analyzed in the same order in which they are listed and, when one of these conditions is true, the CASE statement returns the corresponding result and stops analyzing the other conditions.

    The results, result_1 to result_n, must all be of the same PLSQL data type. These are the values ​​returned by the CASE statement at the time a condition is true.

    If none of the conditions are true, then the CASE statement returns the value of the ELSE clause. If the ELSE clause is omitted and no condition is true, then the CASE statement returns the value NULL.

    - Advertisement -

    In a CASE statement there can be up to 255 WHEN-THEN-ELSE clauses, therefore we can build up to 128 comparisons.

    Below you can see a couple of examples of a SELECT statement that include a CASE statement:

    SELECT last name,
    CASE name
    WHEN 'Pepe' THEN 'is called Pepe'
    WHEN 'Juan' THEN 'is called Juan'
    ELSE 'is not called neither Pepe nor Juan'
    FROM employees;

    SELECT last name,
    WHEN name = 'Pepe' THEN 'is called Pepe'
    WHEN name = 'Juan' THEN 'is called Juan'
    ELSE 'is not called neither Pepe nor Juan'
    FROM employees;

    Both SQL statements will return the same results even though the second statement was written without using the expression option.

    The CASE statement admits multiple combinations, for example more than one field can be used and the conditions that can be built can be as complex as we can think of. Let’s see another example:

    SELECT last name, first name,
    WHEN salary> payment_species
    AND surname <'P' THEN 'is rich'
    WHEN salary <species_payment
    AND surname> = 'Q' THEN 'is poor'
    ELSE 'is neither rich nor poor'
    FROM employees;

    In our example it would turn out that employees whose salary was greater than what they receive in kind and whose name began with a letter from A to O, would be considered wealthy, employees whose salary was less than what they receive in kind and whose name starting with a letter from Q to Z, they would be considered poor, and the rest of the employees would be considered neither rich nor poor.

    obviously everything you put into SELECT will slow down its execution, but it does not have to be preferable to remove the CASE from SELECT. If in the end we are going to have to perform the comparison (or we are going to have to execute the function) for all the records returned by the SELECT, the performance in both cases will be similar.

    When you want to make strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would be enjoy more advance topics from our partner resource.

    Consider reading to these articles:

    - Advertisement -


    Please enter your comment!
    Please enter your name here

    - Advertisment -

    Most Popular

    ORA-01940: Cannot drop a user that is currently connected

    Detail explanation and solution of ORA-01940: Cannot drop a user that is currently connected error.

    Ubuntu and Oracle SQL Developer

    Blog post explains how to install and use of Oracle SQL Developer on Ubuntu linux server

    Create Schema in Oracle 18c XE

    Article explain how to create new user in Oracle 18c XE and grant some system privileges with connect as new schema user in database.

    Oracle Autonomous JSON database

    The blog article explains about What is the Oracle autonomous JSON database, usage and how to get it from Oracle Cloud.

    Recent Comments

    Margareta Combes on nosql Security Vulnerabilities
    Gordon Coleclough on Oracle Tuning Basics Part-1
    Gordon Coleclough on ASM Disk creation issue