Example of how to use the decode function in oracle, oracledecode

Example of how to use the decode function in oracle, 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 smith7499 allen7521 ward7566 jones7654 unknow7698 unknow7782 unknow7788 unknow7839 unknow7844 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 salALLEN 1600 low salWARD 1250 low salJONES 2975 low salMARTIN 1250 low salBLAKE  2850 low salCLARK 2450 low salSCOTT 3000 mid salKING 5000 high salTURNER 1500 low salADAMS 1100 low salJAMES 950     low salFORD 3000 mid salMILLER 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,' does not include s', 'contains s') AS INFOFROM EMP

Output result

SMITH 800 contains sALLEN 1600 does not include sWARD 1250 does not include sJONES 2975 contains sMARTIN 1250 does not include sBLAKE 2850 does not include sCLARK 2450 does not include sSCOTT 3000 contains sKING 5000 does not include sTURNER 1500 does not include sADAMS 1100 sJAMES 950 contains sFORD 3000 does not include sMILLER 1300 does not include s

Decode functions are very useful in actual development.

Combined with the Lpad function, how to automatically add 1 to the value of the primary key and add 0 to the front

Select LPAD (decode (count (Record Number), 0, 1, max (to_number (Record Number) + 1), 14, '0') Record Number from tetdmis

Eg:

Select decode (dir, 1, 0, 1) from a1_interval

The dir value is 1 to 0, and 0 to 1.

For example, how many boys and girls are in a class?

We usually write this:
Select count (*) from table where gender = male;
Select count (*) from table where gender = female;

It's too much trouble to union the display together.

What about decode? Only one sentence is required.

Select sum (decode (gender, male, 1, 0), sum (decode (gender, female, 1, 0) from table

Eg:

select sum(decode(siteno,'LT',1,0)),sum(decode(siteno,'SZ',1,0)) from facd605;select sum(case siteno when 'LT' then 1 else 0 end),sum(case siteno when 'SZ' then 1 else 0 end) from facd605;

Summary

The above is all the content about the decode function usage examples in oracle. I hope it will be helpful to you. Welcome to see: Oracle Cursor basic usage details, oracle Database ORA-01196 error solution sharing, oracle common fault categories and planning analysis, thank you for your support for this site!