Oracle decode function usage

1. The DECODE function is one of the powerful functions of oracle pl/SQL. Currently, only oracle SQL provides this function, and other database vendors do not yet implement this function. What is the purpose of DECODE? First, let's construct an example. If we want to add a salary to a staff member of zhixing, the standard is: the salary of less than 8000 yuan will be increased by 20%; the salary of more than 8000 yuan will be increased by 15%, usually, select the salary field value in the record first? Select salary into var-salary from employee, and then use if-then-else or choose case to judge the variable var-salary. If the DECODE function is used, we can omit these flow control statements and directly complete them through SQL statements. Select decode (sign (salary-8000), 1, salary * 1.15,-1, salary * 1.2, salary from employee is very concise?
   

2. DECODE Syntax: DECODE (value, if1, then1, if2, then2, if3, then3 ,..., else), indicating that if the value is equal to if1, the result of the DECODE function returns then1 ,..., if it is not equal to any if value, else is returned. At first glance, DECODE can only perform equals tests, but as we have seen just now, we can use some functions or computing to replace value to enable the DECODE function to have the functions greater than, less than, or equal.
   

3. 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 (translation value n)
   ELSE
   RETURN (default)
   END IF
   

4. 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 (translation value n)
   ELSE
   RETURN (default)
   END IF
   

5. 1. Compare the size

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

   The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.

   For example:

   Variable 1 = 10, variable 2 = 20

   Then sign (variable 1-variable 2) returns-1, and the decode decoding result is "variable 1", achieving the goal of getting a smaller value.

   
   

6. 2. Table and View Structure Conversion

   There is a sales table sale with the following structure:

   Month char (6) -- month

   Billing number () -- 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 to be converted to the following structure:

   Year char (4) -- year

   Month1 number () -- sales amount in

   Mon2number (February) -- sales amount in December

   Month3 number (March) -- sales amount in December

   Month4 number (April) -- sales amount in December

   Month5 number (May) -- sales amount in December

   Month6 number (June) -- sales amount in December

   Month7 number (July) -- sales amount in December

   Month8 number (August) -- sales amount in December

   Month9 number (September) -- sales amount in December

   Month10 number (October) -- sales amount in December

   Month11 number (November) -- sales amount in December

   Month12 number (December) -- sales amount in December

   
   

7. The SQL statement for structure conversion is:

   Create or replace view

   V_sale (year, month1, mon2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12)

   As

   Select

   Substrb (month, 1, 4 ),

   Sum (decode (substrb (month, 5, 2), '01', substring, 0 )),

   Sum (decode (substrb (month, 5, 2), '02', substring, 0 )),

   Sum (decode (substrb (month, 5, 2), '03', substring, 0 )),

   Sum (decode (substrb (month, 5, 2), '04 ', hour, 0 )),

   
   

8. Supplement 1:

   Students whose student ID table is student must use the decode function to implement the following functions: score> 85, excellent;> 70: Good;> 60: pass; otherwise, fail.

   Suppose student is id and score is score:
   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, 'failed ')))
   From student;

   
   

9. 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 comparison expression and search word of the decode function. If the expression matches, the return result is returned. If the expression does not match, the default value is returned. If the default value is not defined, the return value is null.

   
   

10. The following is a simple test to Decode the function usage:
    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
    OUTLN SYSTEM
    CSMIG SYSTEM
    SCOTT SYSTEM
    EYGLE USERS
    DBSNMP SYSTEM
    Wmsys system 20--0-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.