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