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