Detailed explanation of the usage of decode function in Oracle

decode() function is one of the most powerful functions in ORACLE PL/SQL. Currently, only ORACLE's SQL provides this function, and other database manufacturers' SQL implementations do not yet have this function. This article mainly introduces the relevant information about the usage of decode function in Oracle. Friends who need it can refer to it. I hope it will be helpful.

Explanation of meaning:

decode(condition, value 1, return value 1, value 2, return value 2,... value n, return value n, default value)

The meaning of this function is as follows:

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF
decode(字段或字段的运算，值1，值2，值3）

The result of this function is that when the value of the field or field operation is equal to the value 1, the function returns the value 2 , otherwise return value 3
Of course value 1, value 2, and value 3 can also be expressions. This function makes certain sql statements much simpler

Usage:

1. Compare sizes

select decode(sign(variable 1-variable 2),-1, variable 1, variable 2) from dual; --take the smaller value

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

For example:

Variable 1=10, Variable 2=20

Then sign(variable 1-variable 2) returns -1, and the decode decoding result is "variable 1", achieving the purpose of taking a smaller value.

2. This function is used in SQL statements. The function is introduced as follows:

The Decode function is similar to a series of nested IF-THEN-ELSE statements. base_exp is compared with compare1, compare2, etc. in sequence. If base_exp matches the i-th compare item, return the i-th corresponding value. If base_exp does not match any compare value, default is returned. Each compare value is evaluated in sequence, and if a match is found, the remaining compare values ​​(if any) are not evaluated again. A NULL base_exp is considered equivalent to a NULL compare value. If necessary, each compare value is converted to the same data type as the first compare value, which is also the type of the return value.

The 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(记录编号),0,1,max(to_number(记录编号)+1)),14,'0') 记录编号 from tetdmis
 eg:
 select decode(dir,1,0,1) from a1_interval

The value of dir is 1 It becomes 0, and if it is 0, it becomes 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 表 where 性别 ＝ 男；
select count(*) from 表 where 性别 ＝ 女；

To If you want to display them together, you have to union them, it’s too troublesome.

Use decode, just one sentence

select sum(decode(性别，男，1，0）)，sum(decode(性别，女，1，0）) from 表
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;
vinson

Related recommendations:

PHP json_decode Method that cannot parse special question mark characters

How PHP implements json_decode without escaping Chinese method introduction

Compare the difference between json_encode and json_decode

The above is the detailed content of Detailed explanation of the usage of decode function in Oracle. For more information, please follow other related articles on the PHP Chinese website!