What is the usage of decode in oracle

In Oracle, the decode() function is used to compare the input value with the parameter list in the function, and return a corresponding value based on the input value. The syntax is "decode(condition, value 1, return value 1 , value 2, return value 2,... value n, return value n, default value)".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of decode in oracle

Oracle function decode usage:

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)；

This is the expression of decode.

The DECODE() function compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is composed of several numerical values ​​and their corresponding result values. Several ordinal even forms. Of course, if it fails to successfully match any of the actual parameter sequences, the function also has a default return 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 (field or field operation, value 1, value 2, value 3)

The result of this function is, when When the value of the field or field operation is equal to value 1, the function returns value 2, otherwise it returns value 3

Of course value 1, value 2, and value 3 can also be expressions. This function makes certain sql statements Much simpler

Usage method:

1. Compare size

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 (record number),0,1,max(to_number(record number) 1)),14,'0') The value of record number from tetdmis

eg:

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

dir is 1 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, which is too troublesome.

Use decode. You only need one sentence

select decode(gender, male, 1, 0), decode(gender, female, 1, 0) from table

3, order by to sort the character column in a specific way

You can also use Decode in Order by.

Example: Table table_subject has a subject_name column. It is required to sort them in the order of: language, number, and foreign language. At this time, you can easily use Decode to complete the requirements.

select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

Recommended tutorial: "Oracle Video Tutorial"

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