Common oracle functions-decode

Source: Internet
Author: User

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

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: info-contact@alibabacloud.com 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.