Oracle Common Functions-decode

Decode function is very useful in the actual development, and the function is more powerful, with other functions, can make a lot of work easier;

DECODE ( field , condition 1, result value 1, condition 2, Result Values 2, ... Default Value  );

The function has the following meanings:

IF conditions = value 1 Then

RETURN ( translation value 1)

elsif conditions = value 2 Then

RETURN ( translation value 2)

......

elsif conditions = value N Then

RETURN ( translated value N)

ELSE

RETURN ( default value )

END IF

Some of the more common uses:

1 , compare size

Select Decode (sign ( variable 1- variable 2), -1, variable 1, variable 2) from dual; -- take a smaller value

Sign () The function is based on a value 0 , positive, or negative, respectively, to return 0 , 1 , -1

For example:

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

the Sign ( variable 1- variable 2) return -1 , Decode The decoded result is "variable 1 "To achieve the purpose of taking a smaller value.

2 , generating a fixed number of digits

we look at our various lists of hydro-coal, and often have similar 2013000000000004 with this fixed-length sequence, we can combine Lpad function, which automatically adds the value of the primary key 1 and fill in the front 0 ;

select ' 2013 ' | | Lpad (Decode (count (ID), 0,1,max (To_number (ID) +1)), 0 ') record number from FFM83

FFM83 It's a table for testing, ID it's the water. ID , there are 3 data;

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

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

select ' 2013 ' | | Lpad (Seq_ FFM83. nextval,12, ' 0 ') record number from FFM83

seq_ FFM83 is and FFM83 table corresponds to a SEQUENCE

3 , simplifying queries for multiple conditions

For example, I want to query the number of male and female, how many ?

we usually write this . :

Select COUNT (*) from Table where Sex = male;

Select COUNT (*) from Table where Sex = female;

this will at least query 2 times, if there are more choices, then the database will have more traffic;

with Decode , I just need a word.

Select sum (Decode (sex, ' male ', 1,0)), sum (Decode (sex, ' female ', 1,0)) from Ffm83

Ffm83 The table has a Sex field, directly put the Chinese character's gender attributes;

4 , in Order by used in Decode sort the specified fields

Sort by computer, math majors

Cases:

SELECT * from Ffm83 ORDER by decode (subject, ' computer ', 1, ' math ', 2,10);

run the result as :

female 1 Computer 2013/1/4

male 2 Mathematics 2013/3/13

female 3 Mathematics 2013/3/18

Note: In the sort of large data volume, try not to use this method, the database overhead is very large;

5 , turn vertical table to horizontal table

we want to count - How many people each month in the first half of the year, it is best to directly pull out a similar report appearance. If you use the usual method, it will be more troublesome, the use of DECODE will be relatively simple

Select SUM (DECODE (DT, ' MM '), ' To_char ', 1,0) as "1 Month " 

, SUM (DECODE (DT, ' MM '), ' To_char ', 1,0) as "2 Month " 

, SUM (DECODE (DT, ' MM '), ' To_char ', 1,0) as "3 Month " 

, SUM (DECODE (DT, ' MM '), ' To_char ', 1,0) as "4 Month " 

, SUM (DECODE (DT, ' MM '), ' To_char ', 1,0) as "5 Month " 

, SUM (DECODE (DT, ' MM '), ' To_char ', 1,0) as "6 Month " 

From FFM83;

The results of the operation are as follows:

1 0 2 0 0 0

Note:

Table FF83 Structure of:

CREATE TABLE FFM83

(

SEX VARCHAR2 (2),

ID INTEGER,

SUBJECT VARCHAR2 (32),

DT DATE

);

The data in the table is:

female 1 Computer 2013/1/4

male 2 Mathematics 2013/3/13

female 3 Mathematics 2013/3/18

Oracle Common Functions-decode