How to use Oracle Database decode () function

Decode function: It can compare an input value to a parameter list in a function, and return a corresponding value based on the input value. The parameter list of a function is a number of sequential pairs of numbers and their corresponding result values. Of course, the function also has a default return value if it fails to match any of the argument-order pairs successfully.

Decode function: It can compare an input value to a parameter list in a function, and return a corresponding value based on the input value. The parameter list of a function is a number of sequential pairs of numbers and their corresponding result values. Of course, the function also has a default return value if it fails to match any of the argument-order pairs successfully.

Other functions that differ from SQL, the Decode function can also recognize and manipulate null values.

The syntax is as follows:

DECODE (Control_value,value1,result1[,value2,result2 ...) [, Default_result]);

Control _value

The number you are trying to process. The decode function compares this value to a sequence of subsequent sequences to determine the return value.

Value1

is a numerical value that consists of a sequence pair. If the input value matches successfully, the corresponding result is returned. corresponding to an empty return value, you can use the keyword NULL in the corresponding

Result1

Is the result value of a constituent sequence pair.

The default value that the function returns when Default_result fails to match any of the values.

Examples are as follows:

Select Decode (x, 1, ' x is 1 ', 2, ' X are 2 ', ' others ') from dual

Returns ' X is 1 ' When x equals 1 o'clock.

Returns ' X is 2 ' When x equals 2 o'clock.

Otherwise, return to others '.

When you need to compare 2 values, we can work with the sign () function.

SELECT DECODE (SIGN (5-6), 1 ' is Positive ',-1, ' are nagative ', ' is Zero ')

Similarly, you can use case implementation:

SELECT case SIGN (5-6)

When 1 THEN ' is Positive '

WHEN-1 THEN ' is nagative '

ELSE ' is Zero ' end

From DUAL

In addition, you can also use decode in order by.

Example: Table Table_subject, there are subject_name columns. The requirements are sorted in the order of language, number, and outside. At this time, it is very easy to use decode to complete the request.

SELECT * from Table_subject ORDER by decode (subject_name, ' language ', 1, ' Math ', 2,, ' Foreign Language ', 3)