Oracle function-decode

Decode is translated as decoding or decoding, because it can translate the query results into content that is easy for the user to understand.

Grammar:

Expr: An expression

Search: Searching for values

Result: Results value

Default: Defaults

The first argument to the Decode function is an expression, followed by a paired search value-the result value, and finally a default value. The parameter can be any number type or character type.

The purpose of the Decode function is to compare the value of an expression to some column search values, return the result value corresponding to the search value when the value of the expression is equal to the search value, and return the default value if no equal search value is found, or null if the default value is not defined. The execution logic is similar to the case statement.

The parameter types are handled as follows:

• If the expression and search values are character data, Oracle uses nonpadded semantics to compare them. The result value is the VARCHAR2 type and is the same as the character set for the first result value.
• If the first pair of search values-the result value is a numeric type-Oracle compares all of the search values-result values and expressions-by numeric precedence to determine the type of the parameter, and converts the type of the other parameter to that type.

Search values, result values, and default values can be evaluated by an expression (not the first expression argument). The Oracle database uses short-circuit calculations to settle search values only when comparing each search value with an expression, rather than calculating all the search values at once. So when Oracle finds a value equal to an expression, it does not calculate it even if there is a search value later.

When Oracle compares expressions and each search value, it first converts them to the first search-worthy data type. Oracle automatically converts the returned result value to the data type of the first result value. If the data type of the first result value is char or NULL, then Oracle converts the result value to VARCHAR2.

In the Decode function, Oracle considers two null values to be equal if the expression is null,oracle and returns the result value of the first null search value.

The maximum number of decode parameters is 255.

The following example shows the use of the Decode function. Decode translates the value of warehouse_id into a string that is easy to understand. If the value of warehouse_id is 1, the function returns Southlake, if the value of warehouse_id is 2, the function returns the San Francisco ... If the value of warehouse_id is not 1,2,3,4, non domestic is returned.

SELECT

product_id, DECODE (warehouse_id, 1, 'southlake' , 2, 'San Francisco' , 3, 'New Jersey' , 4, 'Seattle' , 'Non domestic' ) "Location" from inventories WHERE product_id < 1775 ORDER by product_id, "Location";

Oracle function-decode