Home >Database >Mysql Tutorial >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!