What is the usage of Oracle's decode function?

Oracle's decode function usage is: 1. Basic usage, simple conditional judgment based on a conditional field; 2. Multi-conditional judgment, returning different results based on multiple conditions; 3. Nested DECODE function , use the DECODE function nested to achieve more complex conditional judgment and result return; 4. Use in combination with other SQL statements: Use the DECODE function in combination with other SQL statements (such as WHERE clause) to achieve more flexible data query and Processing and so on.

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

Oracle's DECODE function is a polymorphic function that can perform conditional judgment on one or more expressions based on given conditions and return the corresponding results. Its general syntax is as follows:

DECODE(expression, search, result [, search, result]... [, default])

where:

expression is the expression to be used for conditional judgment.

search is the value to search for.

result is the result returned if a matching value is found.

default (optional) is the default result returned if no matching value is found.

The working principle of the DECODE function is to compare it with expression one by one starting from the first search value. If a matching value is found, the corresponding result is returned and the comparison is stopped. If no matching value is found, comparison continues with the next search value until there are no more search values ​​to compare. If no matching value is found and the default parameter is provided, the default result is returned; otherwise NULL is returned.

The following are some common uses of the DECODE function:

1. Basic usage: Make a simple conditional judgment based on a conditional field.

SELECT name, decode(gender, 'M', 'Male', 'F', 'Female', 'Unknown') as gender_description FROM users;

In the above example, the corresponding description is returned according to the value of the gender field (gender). If the gender is not 'M' or 'F', 'Unknown' is returned.

2. Multi-condition judgment: return different results based on multiple conditions.

SELECT department, decode(salary, 5000, 'Low', 10000, 'High', 'Medium') as salary_range FROM employees;

In the above example, the corresponding salary range description is returned according to the range of salary (salary). If the salary is below 5,000, 'Low' is returned; if it is above 10,000, 'High' is returned; otherwise In this case, 'Medium' is returned.

3. Nested DECODE function: Nest the DECODE function to achieve more complex conditional judgment and result return.

SELECT product_name, decode(category, 'Electronics', 'Electronics products', 'Clothing', 'Clothing items', 'Unknown') as product_category FROM products;

In the above example, the corresponding product category description is returned according to the product category (category). If the category is 'Electronics', return 'Electronics products'; if it is 'Clothing', return 'Clothing items' '; otherwise, returns 'Unknown'.

4. Use in combination with other SQL statements: Use the DECODE function in combination with other SQL statements (such as WHERE clause) to achieve more flexible data query and processing.

It should be noted that the DECODE function can be used in combination with other SQL statements, subqueries, etc. to achieve more complex functions. In addition, starting from Oracle 11g, it is recommended to use the CASE statement instead of the DECODE function, because the DECODE function may produce unpredictable results in some cases.

The above is the detailed content of What is the usage of Oracle's decode function?. For more information, please follow other related articles on the PHP Chinese website!