Meaning and usage of oracle decode (), oracledecode

Meaning and usage of oracle decode (), oracledecode

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:

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

Selectdecode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- take a 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

SelectLPAD (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 dir value is 1 to 0, and 0 to 1.

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

We usually write this:

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.

Select sum (decode (gender, male, 1, 0), sum (decode (gender, female, 1, 0) from table

Eg:

Select sum (decode (siteno, 'lt ',), sum (decode (siteno, 'sz',) 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