Comparison of Oracle Decode () function and Case statement

Oracle Decode () functions and case statements are all common use, so what is the difference between them? Here's a detailed description of

The difference between the Oracle Decode () function and the case statement for your reference.

First, give 2 simple examples to compare the differences between the 2.

1.CASE statement:

The following is a code fragment:

SELECT Case SIGN (5-5)





when 1 THEN ' is Positive '





WHEN-1 THEN ' is Negative '





ELSE ' is Zero ' end





from DUAL;

Background implementation:

The following is a code fragment:

if (SIGN (5–5) = 1) {





' is Positive ';





} else if (SIGN (5–5) = 2) {





' is Negative ';





}else {





' is Zero '





}

2. Decode function:

The following is a code fragment:

SELECT DECODE (SIGN (5–5), 1,





' is Positive ',-1, ' are Negative ', ' is Zero ')





fromdual

Background implementation:

The following is a code fragment:

switch (SIGN (5–5))





{





Case 1: ' Is Positive '; Break





Case 2: ' Is Negative '; Break





default: ' Is Zero '





}

In the above example, 2 of them seem to be achievable. However, Decode () is quite complex to implement when encountering extraordinary problems.

For example:

The following is a code fragment:

SELECT Case X-field





when X-field < THEN ' X-field < 40 '





when X-field < THEN ' X-field < 50 '





When X-field < THEN ' X-field < 60 '





ELSE ' Unbeknown ' end





from DUAL

Therefore, the individual considers that the case statement is very flexible in dealing with similar problems. When you just need to match a small number of values, use decode more succinctly.