Oracle Self-Supplement decode () function usage Introduction

The decode () function is one of the most powerful functions of Oracle PL/SQL and is currently available only to Oracle Corporation, where SQL implementations of other database vendors do not yet have this function.  

The Decode function is one of the most powerful functions of Oracle PL/SQL, and it is currently available only to Oracle companies, and the SQL implementations of other database vendors do not yet have this function. What is the use of decode? Let's first construct an example, assuming that we want to raise wages for the smart-star staff, the standard is: wages under 8000 yuan will be added 20%, wages in the 8000 yuan plus 15%, the usual practice is to first select the record of the wage field value? Select Salary to 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 we use the Decode function, then we can omit these flow control statements, which can be done directly through the SQL statement. The following: Select Decode (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 is returned THEN1,..., Returns else if it is not equal to any of the if values. At first glance, DECODE can only do equals test, but just see, we can use some function or calculation instead of value, it is possible to make the DECODE function more than, less than or equal to the function.

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

DECODE (field, Comparison 1, value 1, Comparison 2, value 2, ...), compare n, 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 (translated value N)
ELSE
RETURN (default value)
END IF

Decode () function usage 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, 1, depending on whether a value is 0, positive, or negative.

For example:

Variable 1=10, variable 2=20

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

2. Conversion of table and view structure

Existing a Commodity sales table sale, the table structure is:

Month char (6)--months

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 convert to 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 statements for structure conversions are:

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 (Decode (SUBSTRB (month,5,2), ' sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' sell,0 '),

SUM (Decode (SUBSTRB (month,5,2), ' sell,0 '),

 

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

Supplement 1:

There are students score table student, now to use the Decode function to achieve the following several functions: Performance >85, show excellent, >70 show good, >60 pass; Assuming the student number is ID and the score 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;====================================================== Supplement 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 the expression with the search word, returns the result if it matches, returns the default value if it does not match, or returns a null value if no default value is defined.
Here 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.  

Oracle Self-Supplement decode () function usage Introduction