Oracle Decode function usage

2. The Decode function is one of the most powerful functions of Oracle PL/SQL, which is now available only to Oracle Corporation, and the SQL implementations of other database vendors do not yet have this function. What is the use of decode? First of all, if we want to increase the salary of the smart star staff, the standard is: the salary under 8000 yuan will be added 20%, the salary is more than 8000 yuan plus 15%. The usual practice is. Select the Wage field value in the record first? 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. Assuming that the Decode function is used, then we are able to omit these flow control statements. The SQL statement can be completed directly. For example, the following: Select Decode (salary-8000), 1,salary*1.15,-1,salary*1.2,salary from employee is not very concise?
   

4. Syntax for DECODE: DECODE (value,if1,then1,if2,then2,if3,then3,..., else). When the value equals IF1 is assumed, the result of the Decode function returns THEN1,..., assuming that it does not equal whatever an if value. The else is returned. At first glance. DECODE can only do equals test, but just as we have seen, 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.
   

6. 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
   

8. 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
   

10. 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.

    
    

12. 2, table, view structure conversion

    Existing one commodity sales table sale. 　　Table structure is:

    Month char (6)--month

    Sell number (10,2)-monthly sales amount

    Existing data is:

    200001,

    200002 1100

    200003

    200004 1300

    200005 1400

    200006

    200007 1600

    200101 1100

    200202

    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

    Mo Nth7 Number (10,2)--July Sales Amount

    Month8 number (10,2)-August Sales Amount

    Month9 number (10,2)--September sales amount

    Mont H10 Number (10,2)--October Sales Amount

    Month11 number (10,2)-November Sales Amount

    Month12 number (10,2)--December Sales amount

14. 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 '),

    
    

16. 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.

    
    

18. 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 that the match is true, and the result is assumed to be mismatched. Returns the default value, or null if no default value is defined.

    
    

19. 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.

Oracle Decode function usage