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;
======================================================
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.