Several usage methods of oracle decode function, oracledecode

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