PL/SQL DECODE

Introduction to PL/SQL DECODE

Pl/SQL decode function is used for evaluating the same logic as that of if else and if else if ladder. The decode function has the advantage that the while of the if else condition or else if ladder can be placed in the single line simplifying the code for reading. However, we can only compare the values specified in the parameters of the function in decode function. If they evaluate to true, then a particular value is returned; else, if not specified the default value for the false evaluation of condition inside parameters, decode function returns the NULL value.
In this article, we will study the general syntax of the decode function along with the help of arguments and will also try to understand its working and implementation along with the help of some examples.

Syntax:

The syntax of the DECODE function in the PL/ SQL Oracle database is as shown in the below description –

DECODE (expression/value, search expression 1, return value 1 [, search expression 2, return value 2], …. [, search expression n, return value n] [, default value])

The terminologies used in the above syntax are as described here –

Expression or value – This is the literal value of an expression or a column name of the table, which we have to compare with the search expressions. Before comparing this value, it is converted into the datatype of the search expression 1. If they evaluate to true, the return value is returned or else if default value is specified then it is returned else NULL is return if a comparison of the expression and the search expression evaluates false.

Search expression 1, Search expression 2, Search expression 3, …. Search expression n – This is the expressions with which the expression will be compared one by one. Each of the search expressions is firstly converted to the appropriate datatype and then compared with the expression.

Note: If for any of the search expressions the comparison evaluates to true, then there is no datatype conversion taking place for the further search expressions. Which means that if a comparison of expression and search expression r-1 evaluates to true, then all the expressions starting from search expression r until last are not even converted to compatible datatypes for conversion.

Return value 1, Return value 2, Return value 3, …. Return value n – If the comparison of expression and any of the search expression revaluates to true, then its corresponding return valuer is sent as an output.
Default value – If for all the mentioned search expressions the comparison evaluates to false, then the NULL value is returned from the decode function if this default value parameter is not specified. This is an optional parameter. IF comparison evaluates to false by all the search expression comparisons, then the decode function returns the default value as the output of the function.

Note: All these parameters search expressions, return values, and the default value can be expressions. When they are specified as expressions, their value is evaluated only when we have to do a comparison which involves those expression participants, or else, they are not evaluated when specified.

Working and Examples of PL/SQL DECODE

Let us try to understand the working of the DECODE function with the help of simple examples.

Example #1

Consider the following query statement in PL/ SQL:

SELECT DECODE (500, 500, “Five Hundred”) FROM dual;

The output of the above query statement after execution is as shown below –

Internally the PL/ SQL compares the two parameters 500 and 500, which are equal, and hence as the condition evaluates to true, it returns the string Five Hundred mentioned in the third parameter.
The above query statement works similar to the logic of following the if-else logic program shown below –

IF 500===500 THEN
RETURN “Five Hundred”
END IF;

Example #2

If we try to execute the following query statement with the DECODE function shown below, then it returns the NULL value –

SELECT DECODE (500,600,” Five Hundred”) FROM dual;

The output of the execution of the above query statement is as shown below –

This is because when the comparison of the first two arguments evaluates to false, then as no default value is mentioned, the last parameter, the return value by default for false evaluation, is NULL which is given as output. The working of the above decode function is internally similar to following if-else condition –

IF 500=600 THEN
RETURN “Five Hundred”
ELSE
RETURN NULL
END IF;

Example #3

We can also provide more than one search expression for comparison; in that case, our decode function will be behaving the same as that of the if-else if ladder. Let us consider an example for that scenario as well. Let us try executing the following query statement of decode function –

SELECT DECODE (500, 600, “Six Hundred”, 500, “Five Hundred”) FROM dual;

The output of the execution of the above query statement is as shown below –

The above query statement behaves in the same manner as shown in the below program of PL/ SQL of If else if ladder.

IF 500 = 600 THEN
RETURN 'Six Hundred';
ELSEIF 500 = 500 THEN
RETURN 'Five Hundred';
END IF;

Example #4

We can even try providing the default value when neither of the search expression matches with the specified expression. Consider the following query statement –

SELECT DECODE (300, 600, “Six Hundred”, 500, “Five Hundred”,” Not even Five or six hundred”) FROM dual;

The output of the execution of the above query statement is as shown below:

The above query statement behaves in the same manner as shown in the below program of PL/ SQL of If else if ladder.

IF 300 = 600 THEN
RETURN 'Six Hundred';
ELSEIF 300 = 500 THEN
RETURN 'Five Hundred';
ELSE
RETURN “Not even Five or six hundred”
END IF;

Null Play

Even though in all other cases, when you compare a null value with a null value, they are not treated as equal while using the DECODE function if you try to provide the NULL in first as well as any of the search expressions, the corresponding return value is returned as output which means that the two null values are treated equal.

Conclusion – PL/SQL DECODE

We can make the use of the DECODE function in PL/ SQL for making the use of the same functionality provided by if else if ladders that are for conditional statement evaluation. We can even specify the default value for false evaluation.

Recommended Articles

This is a guide to PL/SQL DECODE. Here we discuss Introduction, syntax, Working of the DECODE Function with Examples, respectively. You may also have a look at the following articles to learn more –

1. PL/SQL Collections
2. PL/SQL TRIM
3. Cursors in PL/SQL
4. PL/SQL Commands