How to use decode in oracle

The DECODE function in Oracle can select a value from multiple values ​​based on conditions. The syntax is: DECODE(expression, value1, result1, value2, result2, ..., default_result). It evaluates an expression against a range of values ​​and returns the corresponding result if there is a match, or a default result if there is no match. DECODE supports nesting, the number of value and result parameters must appear in pairs, and the default_result parameter is optional.

Usage of DECODE in Oracle

The DECODE function is a useful tool in Oracle that allows you Selects a value from multiple values ​​based on specified criteria. The syntax is as follows:

<code>DECODE(expression, value1, result1, value2, result2, ..., default_result)</code>

Usage details:

1. expression: The expression to be evaluated, it can be any valid Oracle expression.
2. value1, value2, ...: Specifies a set of values, which can be constants, variables or expressions.
3. result1, result2, ...: The results associated with each value.
4. default_result: The result returned when expression does not match any of the given values.

How it works:

DECODE function compares expression with the given value one by one. If a match is found, the result associated with that value is returned. If no match is found, default_result is returned.

Example:

<code>SELECT DECODE(customer_type, 'standard', 10%, 'premium', 20%, 'vip', 30%, 0)
FROM sales_data;</code>

This query calculates customer discounts based on the value of the customer_type column. Standard customers get 10% discount, premium customers get 20% discount, and vip customers get 30% discount. If the value of the customer_type column is not within the given range, no discount will be applied.

Note:

• DECODE function supports nesting to handle complex conditions.
• The number of value and result parameters can vary, but they must appear in pairs.
• The default_result parameter is optional and if omitted, NULL is returned when no matching conditions are found.

The above is the detailed content of How to use decode in oracle. For more information, please follow other related articles on the PHP Chinese website!