Oracle Decode () function uses the detailed __c language

Source: Internet
Author: User
The decode () function is one of the powerful functions of Oracle Pl/sql, and it is currently available only in Oracle company SQL, and not in the SQL implementations of other database vendors.

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 is 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, translate 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)
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




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)),


Supplement 1: Have 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 '))
The syntax structure of the ====================================================== supplemental 2:decode function is as follows: student;
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
------------------------------ ------------------------------ ---------
System system
Wmsys SYSTEM 20-oct-04

8 rows selected.

Sql> Select Username,decode (lock_date,null, ' unlocked ', ' locked ') status from T;

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

------------------------------ --------
SYS Unlocked
SYSTEM Unlocked
Outln Unlocked
Csmig Unlocked
SCOTT Unlocked
Eygle Unlocked
DBSNMP Unlocked
Wmsys 8 rows selected.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.