What are the usages of case when in Oracle?

The usage of case when in oracle is: 1. Used in WHERE clause, CASE WHEN can be used to implement more complex filtering conditions, can be used to nest other conditional expressions, and use logical operations symbols (AND, OR, NOT) to combine conditions; 2. Used in the SELECT column list, CASE WHEN can be used to add additional columns to the query results. Different values ​​can be returned based on the results of a condition and displayed as new columns in the result set.

Operating system for this tutorial: Windows 10 system, Oracle version 19c, Dell G3 computer.

CASE WHEN statement is used to implement conditional logic in Oracle. It can be used in the WHERE clause of a SELECT statement, as well as in the column list of a SELECT statement.

The basic syntax of the CASE WHEN statement is as follows:

CASE  
    WHEN condition1 THEN result1  
    WHEN condition2 THEN result2  
    ...  
    ELSE result  
END

In the above syntax, you can return different values ​​based on the result of the condition. First, it evaluates each condition in order, and once it finds one that satisfies the condition, returns the corresponding result, and then exits the CASE expression. If no conditions are met, you can use the ELSE clause to define a default return value.

Usage of CASE WHEN in the WHERE clause

In the WHERE clause, CASE WHEN can be used to implement more complex filtering conditions. You can use it to nest other conditional expressions and combine conditions using logical operators (AND, OR, NOT).

Here is an example that shows how to use CASE WHEN in the WHERE clause:

SELECT column1, column2, ...  
FROM table  
WHERE  
    CASE  
        WHEN condition1 THEN true -- 如果条件1满足，返回true  
        WHEN condition2 THEN true -- 如果条件2满足，返回true  
        ELSE false -- 默认返回false  
    END;

In this example, true or false is returned based on the result of the condition. You can use these conditions with any legal SQL expression or function.

Usage of CASE WHEN in the SELECT column list

In the SELECT column list, CASE WHEN can be used to add additional columns to the query results. You can return different values ​​based on the result of a condition and display them as new columns in the result set.

Here is an example that shows how to use CASE WHEN in a SELECT column list:

SELECT column1, column2,   
    CASE  
        WHEN condition1 THEN result1 -- 如果条件1满足，返回result1  
        WHEN condition2 THEN result2 -- 如果条件2满足，返回result2  
        ELSE result -- 默认返回result  
    END AS new_column  
FROM table;

In this example, different values ​​are returned based on the result of the condition and these values ​​are used as new Column "new_column" is displayed in the result set. You can use these conditions with any legal SQL expression or function.

In summary, the CASE WHEN statement provides a flexible way to implement conditional logic in Oracle. You can use it in WHERE clauses and SELECT column lists to implement more complex filter conditions and add additional columns to query results.

The above is the detailed content of What are the usages of case when in Oracle?. For more information, please follow other related articles on the PHP Chinese website!