Wednesday, October 28, 2020
dbametrix
More
    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
    END

    - Advertisement -
    dbametrix

    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 -
    dbametrix

    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'
    END
    FROM employees;

    SELECT last name,
    CASE
    WHEN name = 'Pepe' THEN 'is called Pepe'
    WHEN name = 'Juan' THEN 'is called Juan'
    ELSE 'is not called neither Pepe nor Juan'
    END
    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,
    CASE
    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'
    END
    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 -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments