Several usage methods of oracle decode function, oracledecode
Decode usage
1: Use decode to determine whether the string is the same
DECODE (value, if1, then1, if2, then2, if3, then3,..., else)
Meaning:
IF condition = value 1 THEN
RETURN (value 1)
ELSIF condition = value 2 THEN
RETURN (value 2)
......
ELSIF condition = value n THEN
RETURN (value 3)
ELSE
RETURN (default)
END IF
SQL Test
Select empno, decode (empno, 7369, 'Smith ', 7499, 'allen', 7521, 'ward ', 7566, 'Jones', 'unknow ') as name from emp where rownum <= 10
Output result
7369 smith
7499 allen
7521 ward
7566 jones
7654 unknow
7698 unknow
7782 unknow
7788 unknow
7839 unknow
7844 unknow
2: Use decode to compare the size
Select decode (sign (var1-var2),-1, var 1, var2) from dual
The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.
SQL Test
Select decode (sign (100-90),-1,100, 90) from dual
Output result
90
100-90 = 10> 0, 1 is returned, so the final value of the decode function is 90.
Anyway
Select decode (sign (100-90), 1,100, 90) from dual
Output result
100
100-90 = 10> 0 returns 1, the result is 1, the first variable 100 is returned, and the final output result is 100.
3: Use the decode function for segmentation
A higher salary than 5000 is a high salary. A Salary ranging from 3000 to 5000 is medium, and a lower salary than 3000 is a low salary.
SQL Test
SELECT
Ename, sal,
DECODE (SIGN (sal-5000 ),
1,
'High sal ',
0,
'High sal ',
-1,
DECODE (SIGN (sal-3000 ),
1,
'Mid sal ',
0,
'Mid sal ',
-1,
DECODE (SIGN (sal-1000 ),
1,
'Low sal ',
0,
'Low sal ',
-1,
'Low sal ')))
FROM
Emp
Output result
SMITH 800 low sal
ALLEN 1600 low sal
WARD 1250 low sal
JONES 2975 low sal
MARTIN 1250 low sal
BLAKE 2850 low sal
CLARK 2450 low sal
SCOTT 3000 mid sal
KING 5000 high sal
TURNER 1500 low sal
ADAMS 1100 low sal
JAMES 950 low sal
FORD 3000 mid sal
MILLER 1300 low sal
4: Use decode to convert tables or columns
SQL Test
SELECT
SUM (DECODE (ENAME, 'Smith ', SAL, 0) SMITH,
SUM (DECODE (ENAME, 'allen ', SAL, 0) ALLEN,
SUM (DECODE (ENAME, 'ward ', SAL, 0) WARD,
SUM (DECODE (ENAME, 'Jones ', SAL, 0) JONES,
SUM (DECODE (ENAME, 'martin ', SAL, 0) MARTIN FROM EMP
The output result is as follows:
SMITH ALLEN WARD JONES MARTIN
800 1600 1250 2975 1250
5: Use the decode function to search strings using expressions
Decode (expression, search_1, result_1, search_2, result_2,..., search_n, result_n, default)
The comparison expression and search word of the decode function. If the expression matches, the return result is returned. If the expression does not match, the default value is returned. If the default value is not defined, the return value is null.
SQL Test
SELECT
ENAME,
SAL,
DECODE (INSTR (ENAME,'s '),
0,
'S not included ',
'Contains s') AS INFO
FROM
EMP
Output result
SMITH 800 contains s
ALLEN 1600 does not include s
WARD 1250 does not include s
JONES 2975 contains s
MARTIN 1250 does not include s
BLAKE 2850 does not include s
CLARK 2450 does not include s
SCOTT 3000 contains s
KING 5000 does not include s
TURNER 1500 does not include s
ADAMS 1100 contains s
JAMES 950 contains s
FORD 3000 does not include s
MILLER 1300 does not include s