How to use Decode functions in Oracle _oracle

Meaning explanation:
Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)

The meaning of the function is as follows:

Copy Code code as follows:

IF condition = value 1 THEN
return (translation value 1)
elsif condition = value 2 THEN
Return (translation value 2)
......
elsif condition = value N THEN
return (translation value N)
ELSE
return (default value)
End IF
Decode (Operation of field or field, value 1, value 2, value 3)

The result of this function is that when the value of the operation of a field or field equals 1 o'clock, the function returns a value of 2, otherwise the value 3 is returned.
Of course, a value of 1, a value of 2, and a value of 3 can also be an expression, which makes some SQL statements a lot simpler

How to use:
1. Compare size
Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.
For example:
Variable 1=10, variable 2=20
Then sign (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1", which achieves the goal of taking a smaller value.

2, this function is used in SQL statements, the function is described as follows:

The Decode function is similar to a series of nested if-then-else statements. Base_exp and Compare1,compare2 and so on are compared in sequence. If the Base_exp matches the first compare entry, the value of the first I corresponds is returned. If the base_exp does not match any of the compare values, the default is returned. Each compare value is evaluated sequentially, and if a match is found, the remaining compare value (if any) is no longer evaluated. A null base_exp is considered equivalent to the null compare value. If necessary, each compare value is converted to the same data type sing Woo 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 make up 0 in front
Select Lpad (Decode (count (record number), 0,1,max (To_number (record number) +1)), 14, ' 0 ') record number from Tetdmis

Copy Code code as follows:

Select Decode (dir,1,0,1) from A1_interval

The value of dir is 1 to 0, and 0 to 1.

For example, I want to inquire about the number of boys and girls in a class is how much?

Usually we write this:

Copy Code code as follows:

Select COUNT (*) from table where gender = male;

Select COUNT (*) from table where gender = female;

It's too much to be a union to show it together.

With decode, you just need a word.

Copy Code code as follows:

Select decode (Sex, male, 1,0), decode (sex, female, 1,0) from table

3,order by to a specific sort of character column

You can also use decode in order by.

Example: Table Table_subject, there are subject_name columns. The requirements are sorted in the order of language, number, and outside. At this time, it is very easy to use decode to complete the request.

Copy Code code as follows:

SELECT * from Table_subject ORDER by decode (subject_name, ' language ', 1, ' Math ', 2,, ' Foreign Language ', 3)