The Decode function is one of the powerful functions of Oracle PL/SQL. Only Oracle's SQL provides this function at the moment, and the SQL implementations of other database vendors do not yet have this capability.
What is the use of decode? Let's start with a sample, if we want to raise a salary 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 select the record of the wage field value?
Select Salary to Var-salary from employee, and then the variable var-salary is inferred from flow control statements such as If-then-else or choose case. Assume that the Decode function is used. Then we can omit these flow control statements, and the SQL statements will be able to complete them directly.
For example, 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), which assumes that value equals IF1. The result of the Decode function returns the THEN1,..., assuming that it is not equal to an if value, or else.
At first glance, DECODE can only do equals test, but just as we see, we replace value with some function or calculation. is the ability to make the decode function more than, less than, or equal to the function.
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 (translated value N)
ELSE
RETURN (default value)
END IF
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 (translated value N)
ELSE
RETURN (default value)
END IF
1, the comparison size
Select decode (sign (variable 1-variable 2), 1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function depends on whether a value is 0, positive, or negative. Returns 0, 1,-1, respectively
Like what:
Variable 1=10, variable 2=20
SIGN (variable 1-variable 2) returns-1. The decode decoding result is "Variable 1". Achieve the purpose of taking a smaller value.
-
2, table, view structure conversion
Existing commodity Sales table sale, table structure:
Month char (6)--month
Sell number (10,2)-monthly sales Amount
The existing data is:
200001
200002 1100
200003
200004 1300
200005 1400
200006
200007.
200101 1100
200202
200301 1300
The data 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 student scores table student, now to use the Decode function to achieve the following several functions: Performance >85, show excellent, >70 show good, >60 pass;
If 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 is compared to the expression and the search word, assuming a match. Returns the result. The default value is returned, assuming no default value is defined, and a null value is returned.
Here is a simple test to illustrate how the Decode function is used:
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.