Oracle decode functions

The Decode function is used extensively in Oracle SQL query statements and is often applied to the PL/SQL statement block.

The basic expression of the 1,decode () function statement is:

Decode (EXPR1,EXPR2,EXPR3,[EXPR4])

This expression is understood by the individual, and can be called the decode comparison operation, which can be compared with the NVL () function and the coalesce () function. The expression can be understood as follows:

1, if the EXPR1 = Expr2,decode function returns the value of the EXPR3 expression;

2, if the expr1! = Expr2,decode function returns the value of the EXPR4 expression and returns null if EXPR4 is unspecified;

Using Example 1:

　　

 select  decode (1  , - 1 , 100 , 90 ), decode (- 1 , -  1 , , 90 ), decode (0 , - 1 , 100 ) from  dual; 
 DECODE (1,-1,100,90) DECODE ( -1,-1,100,90) DECODE (0,-1,100) 

------------------- -------------------- -------------------

90 100

Example Description: The first decode function expression, 1! = 1, so return 90, the second decode function expression, 1 =-1, so return 100, the third decode function expression, 0! =-1, but the value of the 4th expression is not specified, Therefore, the function returns a null value.

Example 2,decode function Alternative usage: For example, we want to find out the EMP table, the total number of employees with bonuses and no bonuses

Typically, we need two query statements:

Select COUNT (*) from EMP where comm are NOT null;

Select COUNT (*) from the EMP where comm is null;

But with the Decode function, we can do it in one line of queries:

　　

 select  sum  (Decode (NVL (Comm,1 ), 1 , 1 , 0 )) Count_no_comm,sum  (Decode (NVL (Comm,1 ), 1 , 0 , 1 )" Conut_comm from   emp ; Count_no_comm conut_comm  -- ------------- -------- 10  4  

Code Description: With the help of the NVL () function to determine whether the bonus Comm is empty, if the return value of NULL is 1, and then take the return value of NVL and 1 to compare, if equal, return 1 (indicating that comm is empty), not equal to return 0 (description comm not empty); Finally, sum is added to the return result of decode, and the result is obtained.

2,decode piecewise function, which is a variable of the above decode comparison operation, is similar to the case expression and can be used as a reference comparison

Syntax structure:

Decode (EXPR1,EXPR2,RETURN_EXPR2,--if EXPR1=EXPR2, return to RETURN_EXPR2;

EXPR3,RETURN_EXPR2,--if EXPR1=EXPR3, return to RETURN_EXPR3;

EXPRN,RETURN_EXPRN,--if EXPR1=EXPR2, return to RETURN_EXPRN;

EXPRX) [new_expr]--if EXPR1 no longer between expr2-exprn above, return return_exprx; New_expr as Alias

Use example: Adjust the salary accordingly, depending on the Department ID

1, we first implement the case expression:

　　　　

SelectEname,deptno,sal, CaseDeptno when Ten  ThenSal* 1.1                                                when  -  ThenSal* 1.2                                               when  -  ThenSal* 1.3                                              ElseSalEndNew_sal fromEmpOrder  bydeptno,new_sal; ename DEPTNO SAL new_sal-------------------- ---------- ---------- ----------MILLERTen       1800       1980CLARKTen       2950       3245KINGTen        the       5500SMITH -       1300       1560ADAMS -        the       1920x1080FORD -        the       3600SCOTT -        the       3600JONES -       3475       4170JAMES -       1450       1885WARD -       1750       2275MARTIN -       1750       2275TURNER -        -       2600ALLEN -       2100       2730BLAKE -       3350       4355

2, using the Decode function to implement

　　

SelectEname,deptno,sal,decode (Deptno,Ten, Sal* 1.1,                                 -, Sal* 1.2,                                 -, Sal* 1.3, Sal) new_sal fromEMP; ename DEPTNO SAL new_sal-------------------- ---------- ---------- ----------SCOTT -        the       3600SMITH -       1300       1560ALLEN -       2100       2730WARD -       1750       2275JONES -       3475       4170MARTIN -       1750       2275BLAKE -       3350       4355CLARKTen       2950       3245KINGTen        the       5500TURNER -        -       2600ADAMS -        the       1920x1080JAMES -       1450       1885FORD -        the       3600MILLERTen       1800       1980

In some cases, you can use the Decode function to achieve the same effect as a case expression.

Oracle decode functions