Common oracle functions-decode

Common oracle functions-decode

The Decode function is very useful in actual development and has powerful functions. Combined with other functions, it can simplify a lot of work;

DECODE (field, condition 1, result value 1, condition 2, result value 2 ,... 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

Common 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:

Select decode (sign (10-20),-1, 10, 20) from dual;

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. Generate a fixed-digit serial number

We look at our water, electricity, coal, and other lists. We often have a sequence of Fixed Length such as 2013000000000004. We can combine the Lpad function to automatically add 1 primary key value and add 0 in front;

Select '200' | LPAD (decode (count (ID), 2013, max (to_number (ID) + 1), 12, '0') Record Number from FFM83

FFM83 is a test table. Its ID is its journal ID, which contains three pieces of data;

Note: This method is simple, but if there are many users, the same serial number may be generated in high concurrency;

In the case of high concurrency, you can refer to the following method:

Select '200' | LPAD (SEQ _ FFM83.NEXTVAL, 12, '0') Record Number from FFM83

SEQ _ FFM83 is a SEQUENCE corresponding to the FFM83 table.

3. Simplified query of multiple conditions

For example, what are the numbers of male and female?

We usually write this:

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

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

This requires at least two queries. If there are more options, the database will have more traffic;

What about decode? Only one sentence is required.

Select sum (decode (sex, 'male',), sum (decode (sex, 'female ',) from ffm83

In the ffm83 table, there is a sex field that directly contains the male attributes of Chinese characters;

4. Use Decode in Order by to sort specified fields

Sort by computer and mathematics majors

Example:

Select * from ffm83 order by decode (subject, 'computer ', 1, 'mat', 2, 10 );

The running result is:

Female 1 Computer

MALE 2 mathematics

Female 3 mathematics

Note: Do not use this method in sorting large amounts of data. The database overhead is very large;

5. convert a vertical table to a horizontal table

We want to count the number of people each month in the first half of 2013. We 'd better pull a similar report directly. If you use the conventional method, it will be more troublesome, and DECODE will be relatively simple.

Select sum (DECODE (TO_CHAR (DT, 'mm'), '01',) AS "January"

, SUM (DECODE (TO_CHAR (DT, 'mm'), '02',) AS "February"

, SUM (DECODE (TO_CHAR (DT, 'mm'), '03',) AS "March"

, SUM (DECODE (TO_CHAR (DT, 'mm'), '04 ',) AS "April"

, SUM (DECODE (TO_CHAR (DT, 'mm'), '05 ',) AS "May"

, SUM (DECODE (TO_CHAR (DT, 'mm'), '06',) AS "June"

FROM FFM83;

The running result is as follows:

1 0 2 0 0 0

Note:

Structure of table FF83:

Create table FFM83

(

SEX VARCHAR2 (2 ),

Id integer,

SUBJECT VARCHAR2 (32 ),

DT DATE

);

The data in the table is:

Female 1 Computer

MALE 2 mathematics

Female 3 mathematics