Meaning and usage of oracle decode (), oracledecode

Source: Internet
Author: User

Meaning and usage of oracle decode (), oracledecode


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)


RETURN (default)


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.


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


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


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;


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.