Shulou(Shulou.com)06/01 Report--

This article is to share with you about the use of the decode function in oracle, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Several uses of decode

1: use decode to determine whether the string is the same

DECODE (value,if1,then1,if2,then2,if3,then3,...,else)

The meaning is

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 rownum0 returns 1, so the decode function ends up with a value of 90

Anyway

Select decode (sign (100-90), 1pm 100je 90) from dual

Output result

one hundred

100-90 percent 10 > 0 returns 1, the judgment result is 1, the first variable 100 is returned, and the final output is 100.

3: use the decode function to segment

A salary greater than 5000 is a high salary, a salary between 3000 and 5000 is medium, and a salary less 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 achieve table or attempted row-column transformation

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 is as follows

SMITH ALLEN WARD JONES MARTIN 800 1600 1250 2975 1250

5: use the decode function to search for strings using expressions

Decode (expression, search_1, result_1, search_2, result_2,...., search_n, result_n, default)

The decode function compares the expression with the search word and returns a result if it matches, a default value if it does not match, or a null value if no default value is defined.

Sql test

SELECT ENAME, SAL, DECODE (INSTR (ENAME,'S'), 0, 'does not contain swords,' contains s') AS INFOFROM EMP

Output result

SMITH 1600 contains sALLEN 1600, does not contain sWARD 1250, does not contain sJONES 2975, does not contain sMARTIN 1250, does not contain sBLAKE 2850, does not contain sCLARK 2450, does not contain sSCOTT 3000, does not contain sKING 5000, does not contain sTURNER 1500, does not contain sADAMS 1100, contains sFORD 3000. does not contain sMILLER 1300s.

The Decode function is very useful in practical development.

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

Select LPAD (decode (count (record number), 0Magne1Maginmax (to_number (record number) + 1)), 14memoir 0') record No. From tetdmis

Eg:

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

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

For example, I want to inquire about the number of boys and girls in a class.

Usually we write like this:

Select count (*) from table where gender = male

Select count (*) from table where gender = female

It's too troublesome to union if you want to show it together.

With decode, it only takes one sentence.

Select sum (decode (gender, male, 1pm 0)), sum (decode (Sex, female, 1pl 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

Have you learned it? Let's give it a try.

Related recommendations:

Problems that should be paid attention to in json_decode function in php

Detailed explanation of the common function Trunc of Oracle

Detailed explanation of Oracle user-defined split function example

The above is the use of the decode function in oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.