Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use case and decode in oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Summary:

Comparison between DECODE and CASE WHEN

1.DECODE is only available in Oracle, and other databases do not support it.

The use of 2.CASE WHEN is supported by Oracle, SQL Server and MySQL.

3.DECODE can only be used to judge equality, but it can be used with sign function to judge whether it is greater than, less than, or equal to. CASE when can be used for =, > =, select ename,deptno,sal,case when deptno=10 then sal*1.1.

2 when deptno=20 then sal*1.2

3 when deptno=30 then sal*1.3

4 else sal

5 end isal from test_emp

ENAME DEPTNO SAL ISAL

--

AAAABC

SMITH 20 800 960

ALLEN 30 1600 2080

WARD 30 1250 1625

JONES 20 2975 3570

MARTIN 30 1250 1625

BLAKE 30 2850 3705

CLARK 10 2450 2695

SCOTT 20 3002 3602.4

KING 10 5000 5500

TURNER 30 1500 1950

ADAMS 20 1100 1320

JAMES 30 950 1235

FORD 20 3000 3600

MILLER 10 1300 1430

15 rows selected.

Usage of case non-equivalent expressions:

SQL > select ename,deptno,sal,case when sal select ename,sal,comm,case when comm is null then sal else sal+comm end isal from emp

ENAME DEPTNO SAL ISAL

--

AAAABC

SMITH 800 800

ALLEN 1600 300 1900

WARD 1250 500 1750

JONES 2975 2975

MARTIN 1250 1400 2650

BLAKE 2850 2850

CLARK 2450 2450

SCOTT 3002 3002

KING 5000 5000

TURNER 1500 0 1500

ADAMS 1100 1100

JAMES 950 950

FORD 3000 3000

MILLER 1300 1300

15 rows selected.

Second, the usage of decode function

Syntax:

DECODE (col | expression, search2, result1

[, search3, result2,...,]

...

[, searchn, resultn,...,]

[, default])

If condition = value 1, then display result 1

If condition = value 2, then display result 2

....

If the condition = value n, then the result n is displayed

If neither match, the default value is displayed

The use of the equivalent expression of the decode function is simpler than that of the case expression above:

SQL > select ename,deptno,sal,decode (deptno,10,1.1*sal,20,1.2*sal,30,1.3*sal) isal from emp

ENAME DEPTNO SAL ISAL

--

AAAABC

SMITH 20 800 960

ALLEN 30 1600 2080

WARD 30 1250 1625

JONES 20 2975 3570

MARTIN 30 1250 1625

BLAKE 30 2850 3705

CLARK 10 2450 2695

SCOTT 20 3002 3602.4

KING 10 5000 5500

TURNER 30 1500 1950

ADAMS 20 1100 1320

JAMES 30 950 1235

FORD 20 3000 3600

MILLER 10 1300 1430

15 rows selected.

Note: decode cannot be used for non-equivalent usage.

Extended usage: compare the size with the sign function

Note: the sign () function returns 0, 1,-1, respectively, depending on whether the value of parameter 1 is 0, positive or negative.

Select ename,sal,decode (sign (sal-2000), 1,'> 2000',-1,'

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.