How to use the decode function in oracle-Mysql Tutorial-php.cn

This article mainly introduces examples of how to use the decode function in Oracle. It is quite good. I hope everyone can master the use of the decode function after learning it. Let’s take a look together.

Several uses of decode

1: Use decode to determine whether the strings are the same

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

means
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

Copy after login

Output result

7369 smith 7499 allen 7521 ward 7566 jones 7654 unknow 7698 unknow 7782 unknow 7788 unknow 7839 unknow 7844 unknow

Copy after login

2: Use decode to compare sizes

select decode(sign(var1-var2),-1, var 1,var2) from dual

sign() function returns 0, 1, or -1 respectively depending on whether a value is 0, a positive number, or a negative number

sql Test

select decode(sign(100-90),-1,100,90) from dual

Copy after login

Output result

Copy after login

100-90=10>0 then it will Returns 1, so the final value of the decode function is 90

Anyway

select decode(sign(100-90),1,100,90) from dual

Copy after login

Output result

Copy after login

##100-90=10>0 returns 1, the judgment result is 1, returns the first variable 100, and the final output result is 100

3: Use decode function segmentation

Salary greater than 5000 is high salary, salary between 3000 and 5000 is medium salary, salary less than 3000 is 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

Copy after login

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

Copy after login

4: Use decode to implement the table Or try to convert rows and 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

Copy after login

The output results are as follows

SMITH ALLEN WARD JONES MARTIN 800 1600 1250 2975 1250

Copy after login

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)

decode function compares the expression and the search word. If it matches, it returns the result; if it does not match, it returns the default value; if the default value is not defined, then Returns a null value.

sql test

SELECT ENAME, SAL, DECODE(INSTR(ENAME, 'S'), 0, '不含有s', '含有s') AS INFO FROM EMP

Copy after login

Output result

SMITH 800 含有s ALLEN 1600 不含有s WARD 1250 不含有s JONES 2975 含有s MARTIN 1250 不含有s BLAKE 2850 不含有s CLARK 2450 不含有s SCOTT 3000 含有s KING 5000 不含有s TURNER 1500 不含有s ADAMS 1100 含有s JAMES 950 含有s FORD 3000 不含有s MILLER 1300 不含有s

Copy after login

Decode function is 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 in 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 value of dir changes from 1 to 0, and from 0 to 1

For example, I Want to query the number of boys and girls in a certain class?

Usually we write like this:

select count(*) from table where Gender = Male;
select count(*) from table where gender=female;

If you want to display them together, you have to union them, which is too troublesome

Use decode, just one sentence

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;

Copy after login

Have you all learned it? Hurry up and give it a try.

Related recommendations:

Issues to note with the json_decode function in php

Detailed explanation of Oracle’s commonly used function Trunc

Detailed explanation of Oracle custom split function example

The above is the detailed content of How to use the decode function in oracle. For more information, please follow other related articles on the PHP Chinese website!