Introduction of decode in oracle

Interpretation of meaning:

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

The meaning of this function 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 (field or field operation, value 1, value 2, value 3)

The result of this function is that when the value of the field or the operation of the field is equal to the value of 1, the function returns a value of 2, otherwise it returns a value of 3
Of course, values 1, 2, and 3 can also be expressions, which makes some 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, 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-1, and the decoding result of decode is "variable 1", which achieves the purpose of taking a smaller value.

2. This function is used in SQL statement, and its functions are described as follows:

The Decode function is similar to the 1 series of nested IF-THEN-ELSE statements. base_exp is compared in turn with compare1, compare2, and so on. If base_exp matches i-th compare, the corresponding value of i-th is returned. If base_exp does not match any compare values, default is returned. Each compare value is evaluated in sequence, and if one match is found, the remaining compare values (if any) are no longer evaluated. One base_exp, which is NULL, is considered to be equivalent to NULL compare. If necessary, every compare value is converted to the same data type as the first compare value, which is also the type of the return value.

Decode function is very useful in actual development

Combined with Lpad function, how to make the value of primary key automatically add 1 and make up 0 before it
select LPAD (decode (count (record number), 0, 1, max (to_number (record number) +1)), 14, '0') Record number from tetdmis

eg:

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

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

For example, I want to inquire about the number of boys and girls in a certain class.

Usually we write this:

select count (*) from Table where Gender = Male;
select count (*) from Table where Gender = Female;

It's too much trouble to display union1 when it comes to one case

With decode, it only needs one sentence

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

3. order by Sort Character Columns Specific

You can also use Decode in Order by.

Example: Table table_subject with subject_name column. It is required to sort according to the order of language, number and foreign language. At this time, you can easily use Decode to fulfill the requirements.

select * from table_subject order by decode (subject_name, 'Chinese', 1, 'Mathematics', 2, 'Foreign Languages', 3)