Skills for using decode () functions in Oracle (refer to here)

Author: network collection, source: IT expert network, responsible editor: ttdb,

The decode () function is one of the powerful functions of Oracle PL/SQL. Currently, only oracle SQL provides this function, this function is not available for SQL implementations of other database vendors.

The decode function is one of the powerful functions of Oracle PL/SQL. Currently, only oracle SQL provides this function, and other database vendors do not yet implement this function. What is the purpose of decode? First, let's construct an example. If we want to add a salary to a staff member of zhixing, the standard is: the salary of less than 8000 yuan will be increased by 20%; the salary of more than 8000 yuan will be increased by 15%, usually, select the salary field value in the record first? Select salary into var-salary from employee, and then use if-then-else or choose case to judge the variable VAR-salary. If the decode function is used, we can omit these flow control statements and directly complete them through SQL statements. Select decode (sign (salary-8000), 1, salary * 1.15,-1, salary * 1.2, salary from employee is very concise? Decode Syntax: Decode (value, if1, then1, if2, then2, if3, then3 ,..., else), indicating that if the value is equal to if1, the result of the decode function returns then1 ,..., if it is not equal to any if value, else is returned. At first glance, decode can only perform equals tests, but as we have seen just now, we can use some functions or computing to replace value to enable the decode function to have the functions greater than, less than, or equal.

Decode () function usage tips

· Software environment:

1. Windows NT4.0 + Oracle 8.0.4

2. Oracle installation path: C:/orant

· Meaning:

Decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation 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)

Else

Return (default)

End if

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

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. Table and View Structure Conversion

There is a sales table sale with the following structure:

Month char (6) -- month

Billing number () -- monthly sales amount

The existing data is:

200001 1000

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

Data to be converted to the following structure:

Year char (4) -- year

Month1 number () -- sales amount in

Mon2number (February) -- sales amount in December

Month3 number (March) -- sales amount in December

Month4 number (April) -- sales amount in December

Month5 number (May) -- sales amount in December

Month6 number (June) -- sales amount in December

Month7 number (July) -- sales amount in December

Month8 number (August) -- sales amount in December

Month9 number (September) -- sales amount in December

Month10 number (October) -- sales amount in December

Month11 number (November) -- sales amount in December

Month12 number (December) -- sales amount in December

The SQL statement for structure conversion is:

Create or replace View

V_sale (year, month1, mon2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12)

As

Select

Substrb (month, 1, 4 ),

Sum (decode (substrb (month, 5, 2), '01', substring, 0 )),

Sum (decode (substrb (month, 5, 2), '02', substring, 0 )),

Sum (decode (substrb (month, 5, 2), '03', substring, 0 )),

Sum (decode (substrb (month, 5, 2), '04 ', hour, 0 )),