Skills for using Decode () functions in Oracle

In Oracle, the Decode () function is a powerful function in oracle pl/SQL. Currently, only oracle SQL provides this function, this function is not available for SQL implementations of other database vendors. 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? 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. 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 with the default value n.) The meaning of this 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 usage tips · software environment: 1. Windows NT4.0 + ORACLE 8.0.4 2. ORACLE installation path: C:/ORANT. Usage: 1. Compare the select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- take a smaller value sign () the function returns 0, 1, and-1 based on a value of 0, positive, or negative. For example, if the variable 1 is 10, and the variable 2 is 20, then sign (variable 1-variable 2) is returned) -1 is returned, and the decode decoding result is "variable 1", so that a smaller value is obtained. 2. Table and View Structure Conversion

There is an existing product sales table sale. The table structure is month char (6) -- month sale number () -- monthly sales amount existing data is: 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 data to be converted to the following structure: year char (4) -- year month1 number (1300) -- sales amount for June: monthnumber () -- sales amount for June: month3 number () -- sales amount for June: month4 number () -- sales amount for June: month5 number) -- sales amount month6 number () -- sales amount month7 number () -- sales amount month8 number () -- sales amount month9 number) -- September sales amount month10 number () -- October sales amount month11 number () -- November sales amount month12 number () -- December sales amount Structure Conversion SQL statement: create or replace view v_sale (year, month1, month1, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12) as select substrb (month, 1, 4), sum (decode (substrb (month, 5, 2), '01 ', hour, 0), sum (decode (substrb (month, 5, 2), '02 ', values, 0), sum (decode (substrb (month, 5, 2), '03', hour, 0), sum (decode (substrb (month, 5, 2 ), '04 ', average, 0 )), ========================================================== ====================

 

Supplement 1: students whose student ID table is students 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;

 

========================================================== ================= 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. 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                             SYSTEMSYSTEM                          SYSTEMOUTLN                           SYSTEMCSMIG                           SYSTEMSCOTT                           SYSTEMEYGLE                           USERSDBSNMP                          SYSTEMWMSYS                           SYSTEM                          20-OCT-048 rows selected.SQL> select username,decode(lock_date,null,'unlocked','locked') status from t;USERNAME                        STATUS------------------------------ --------SYS                             unlockedSYSTEM                          unlockedOUTLN                           unlockedCSMIG                           unlockedSCOTT                           unlockedEYGLE                           unlockedDBSNMP                          unlockedWMSYS                           locked8 rows selected.SQL> select username,decode(lock_date,null,'unlocked') status from t;USERNAME                        STATUS------------------------------ --------SYS                             unlockedSYSTEM                          unlockedOUTLN                           unlockedCSMIG                           unlockedSCOTT                           unlockedEYGLE                           unlockedDBSNMP                          unlockedWMSYS8 rows selected.