Oracle Decode () function use tips to share _oracle

Today, the top request to do a similar function, the initial thought of column change, but how to achieve there is no good way, this function is good

The Decode function is an Oracle Pl/sql is one of the powerful functions, and currently only Oracle company SQL provides this function, other database vendors do not have the SQL implementation. What's the use of decode?

To construct an example, suppose we want to add a salary to the intelligence Star staff, the standard is: the salary at 8000 yuan will add 20%, wages in 8000 yuan plus 15%, the usual practice is to first select the record of the wage field value? Select salary into Var-salary from employee, and then the variable var-salary is judged by a flow control statement such as If-then-else or choose case.

If you use the Decode function, then we can omit these flow control statements, through the SQL statement can be directly completed. As follows: Select Decode (sign (salary-8000), 1,salary*1.15,-1,salary*1.2,salary from employee is not very concise?
DECODE syntax: DECODE (value,if1,then1,if2,then2,if3,then3,..., else), indicating that if value equals IF1, the result of the DECODE function returns to Then1,..., Returns else if it is not equal to any one if value. At first glance, DECODE can only do equals test, but just also saw that we use some function or calculate substitution value, is can make DECODE function have greater than, less than or equal to function.

DECODE (condition, value 1, translation value 1, value 2, translation value 2, ...) Value n, translation value n, default value)

DECODE(field, compare 1, value 1, compare 2, value 2, ..., compare n, value n default value)

The meaning of the function is as follows:
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 value)
End IF

　　Decode () function use tips

• Software Environment:

1. Windows nt4.0+oracle 8.0.4

2, the Oracle installation path is: c:/orant

· How to use:

1. Compare size

Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; --Take a smaller value

The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.

For example:

Variable 1=10, variable 2=20

Then sign (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1", which achieves the goal of taking a smaller value.

2, table, view structure transformation

There is an existing sales table sale, the table structure is:

Month char (6)--month

Sell number (10,2)--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 that you want to translate into the following structure:

Year char (4)--years

Month1 Number (10,2)--January Sales Amount

Month2 Number (10,2)--February Sales Amount

Month3 Number (10,2)--March Sales Amount

Month4 Number (10,2)--April Sales Amount

Month5 Number (10,2)--May Sales Amount

Month6 Number (10,2)--June Sales Amount

Month7 Number (10,2)--July Sales Amount

Month8 Number (10,2)--August Sales Amount

Month9 Number (10,2)--September Sales Amount

Month10 Number (10,2)--October Sales Amount

Month11 Number (10,2)--November Sales Amount

Month12 Number (10,2)--December Sales Amount

The SQL statement for structural transformation is:

Create or Replace view

V_sale (YEAR,MONTH1,MONTH2,MONTH3,MONTH4,MONTH5,MONTH6,MONTH7,MONTH8,MONTH9,MONTH10,MONTH11,MONTH12)

As

Select

SUBSTRB (month,1,4),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

Sum (SUBSTRB (month,5,2), ' decode ', sell,0)),

======================================================

Add 1:

Has the Student Achievement table student, now must use the Decode function to realize the following several functions: The result >85, the display is excellent, the >70 displays is good, the >60 passes, otherwise is failed.

Assuming the student number is ID and the result is score, then:
Select ID, decode (sign (score-85), 1, ' excellent ', 0, ' excellent ',-1,
Decode (sign (score-70), 1, ' good ', 0, ' good ',-1,
Decode (sign (score-60), 1, ' Pass ', 0, ' Pass ',-1, ' fail '))
from student;

======================================================

Add 2:

The syntax structure of the DECODE function is as follows:
Decode (expression, search_1, result_1)
Decode (expression, search_1, Result_1, search_2, result_2)
Decode (expression, search_1, Result_1, search_2, Result_2, ..., Search_n, result_n)

Decode (expression, search_1, result_1, default)
Decode (expression, search_1, Result_1, search_2, result_2, default)
Decode (expression, search_1, Result_1, search_2, Result_2, ..., search_n, result_n, default)

The decode function compares expressions and search words, returns the result if it matches, returns a default value if it does not match, and returns a null value if no default value is defined.
The following is a simple test to illustrate the use of the Decode function:
Sql> CREATE table T as select Username,default_tablespace,lock_date from Dba_users;

Table created.

Sql> select * from T;

USERNAME Default_tablespace Lock_date
------------------------------ ------------------------------ ---------
SYS SYSTEM
System system
Outln SYSTEM
Csmig SYSTEM
SCOTT SYSTEM
Eygle USERS
Dbsnmp SYSTEM
Wmsys SYSTEM 20-oct-04

8 rows selected.

Sql> Select Username,decode (lock_date,null, ' unlocked ', ' locked ') status from T;

USERNAME STATUS
------------------------------ --------
SYS Unlocked
SYSTEM Unlocked
Outln Unlocked
Csmig Unlocked
SCOTT Unlocked
Eygle Unlocked
DBSNMP Unlocked
Wmsys locked

8 rows selected.

Sql> Select Username,decode (lock_date,null, ' unlocked ') status from T;

USERNAME STATUS
------------------------------ --------
SYS Unlocked
SYSTEM Unlocked
Outln Unlocked
Csmig Unlocked
SCOTT Unlocked
Eygle Unlocked
DBSNMP Unlocked
Wmsys

8 rows selected.