Oracle / PLSQL: DECODE Function

If we want to use IF-THEN-ELSE statement in Oracle Database query, We want to use Decode Function.

So, This Oracle tutorial explains how to use the Oracle/PLSQL DECODE function with syntax and real example.

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

The expression is the value to compare. Many combinations of search and result can be supplied. Search is compared against the expression, and if it is true, the result is returned.

The DECODE function is an older function, but still quite powerful. It can handle advanced logic but can get hard to read as the function gets longer.

Example

The DECODE function can be used in Oracle/PLSQL.You could use the DECODE function in a SQL statement as follows:

 

SELECT DECODE(O.PRODUCTID,11,'SUNLIGHT SOAP ROSE',12,'SUNLIGHT SOAP WHITE',13,'SUNLIGHT SOAP YELLOW',14,'SUNLIGHT SOAP PURPLE')

FROM M_CORRSQL.ORDERDETAILS O

The above DECODE statement is equivalent to the following IF-THEN-ELSE statement:

IF O.PRODUCTID = 11 THEN

   RESULT := 'SUNLIGHT SOAP ROSE';

ELSIF O.PRODUCTID = 12 THEN

   RESULT := 'SUNLIGHT SOAP WHITE';

ELSIF O.PRODUCTID = 13 THEN

   RESULT := 'SUNLIGHT SOAP YELLOW';

ELSE

   O.PRODUCTID:= 'SUNLIGHT SOAP PURPLE';

END IF;

Also, Oracle started with the decode statement and later refined it in Oracle9i, morphing it into the case statement (Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i).

More Details