How to Use the decode function in oracle

Description:
Decode (condition, value 1, return value 1, value 2, return value 2,... value n, return value n, default value)

The function has the following meanings:
Copy codeThe Code is 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)
END IF
Decode (calculation of a field or field, value 1, value 2, value 3)

The result of this function is that when the calculated value of a field or field is equal to 1, the function returns 2; otherwise, the return value is 3.
Of course, values 1, 2, and 3 can also be expressions. This function makes some SQL statements much simpler.

Usage:
1. Compare the size
Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- smaller value
The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.
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 goal of getting a smaller value.

2. This function is used in SQL statements. The functions are described as follows:

The Decode function is similar to a series of nested IF-THEN-ELSE statements. Base_exp is compared with compare1 and compare2 in sequence. If base_exp matches the number I compare, the value corresponding to the number I is returned. If base_exp does not match any compare value, default is returned. Each compare value is evaluated sequentially. If a match is found, the remaining compare values (if any) are not evaluated. A null base_exp is considered to be equivalent to a NULL compare value. If necessary, each compare value is converted to the same data type as the first compare value. This data type is also the type of the returned value.

Decode functions are 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 to the front
Select LPAD (decode (count (Record Number), 0, 1, max (to_number (Record Number) + 1), 14, '0') Record Number from tetdmis
Copy codeThe Code is as follows:
Select decode (dir, 1, 0, 1) from a1_interval

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

For example, how many boys and girls are in a class?

We usually write this:
Copy codeThe Code is as follows:
Select count (*) from table where gender = male;

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

It's too much trouble to union the display together.

What about decode? Only one sentence is required.
Copy codeThe Code is as follows:
Select decode (gender, male, 1, 0), decode (gender, female, 1, 0) from table


3. order by sorts character columns.

You can also use Decode in Order.

For example, the table_subject table has the subject_name column. Sort by language, number, and external order. In this case, you can easily use Decode to complete the requirements.
Copy codeThe Code is as follows:
Select * from table_subject order by decode (subject_name, '', 1, 'mat', 2, '', 3)