Decode usage of Process control functions in Oracle

function Introduction　　

The Decode function is one of the most powerful functions of Oracle PL/SQL and is currently available only to Oracle Corporation, where SQL implementations of other database vendors do not yet have this function.

What is the use of decode? Let us first construct an example, assuming that we want to raise wages for the smart-star staff, the standard is: The salary is below 8000 yuan plus 20%, the salary is 8000 yuan or above the plus 15%.

The usual practice is to first select the wage field value in the record? Select Salary to 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 we use the Decode function, then we can omit these flow control statements, which can be done directly through the SQL statement.

As follows: Select Decode (sign (salary-8000), >=0,salary*1.15,<0,salary*1.2,salary) is the from employee very concise? Syntax for decode:

DECODE (Value,if1,then1,if2,then2,if3,then3,..., else),

Indicates that if value equals IF1, the result of the Decode function returns THEN1,..., if it is not equal to any one if value, else is returned. At first glance, DECODE can only do equals test, but just see, we can use some function or calculation instead of value, it is possible to make the DECODE function more than, less than or equal to the function.

About Decode

Decode is an exclusive feature provided by Oracle, a powerful function. Although it is not a standard for SQL, it is very useful for performance. Until now, other database vendors have not been able to provide similar decode capabilities, and even some database vendors have criticized Oracle for its SQL nonstandard. In fact, this criticism is somewhat partial or not level enough. Like some wagon makers complaining about Henry. Ford's "Wagon" is not the same standard.

1. If-then-else Logic in DECODE

In logic programming, If–then–else is often used to make logical judgments. In Decode's syntax, this is actually the logical process. Its syntax is as follows:

DECODE (value, IF1, Then1, If2,then2, If3,then3, ... else)

Where Value represents any column of any type of a table or any result that is computed. It is important to note that if, then, and else here can be functions or evaluation expressions.

When each value is tested, if value is the result of the If1,decode function is then1, and if value equals If2,decode The result of the function is then2; In fact, multiple if/then pairs can be given. If the value result is not equal to any given pairing, the Decode result returns else.

2. A simple example of DECODE

Many of the data dictionaries in the Oracle system are designed using decode ideas, such as the v$session data dictionary view that records session information.

We learned from the oracle8i/9i Reference that when a user logs in successfully, there is a corresponding record for that user in v$session, but the command action that the user takes in the view only records the command code (0-No action, 2-insert ... Instead of the specific command keyword.

Therefore, we need to know the names of the current individual users and their actions, the following command to get detailed results:

Select Sid,serial#,username,

DECODE (Command,

0, ' None ',

2, ' Insert ',

3, ' Select ',

6, ' Update ',

7, ' Delete ',

8, ' Drop ',

' Other ') Cmmand

From V$session where username are not null;

3, the specific use of DECODE method

(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, 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 -1,decode decoding result as "Variable 1", which achieves the purpose of taking a smaller value.

(2) Conversion of table and view structure

A. longitudinal table to horizontal table

longitudinal structure: test_z2h   FNAME       FTYPE             fvalue   Employees        zaocan                 Employees       zhongcan                Staff        Wancan              5  

The converted table structure:

FNAME Zaocan_value Zhongcan_value Wancan_value

Staff 5

1 --vertical table to table SQL example:2   SELECTFNAME,3        SUM(DECODE (FTYPE,'Zaocan', Fvalue,0)) asZaocan_value,4        SUM(DECODE (FTYPE,'Zhongcan', Fvalue,0)) asZhongcan_value,5        SUM(DECODE (FTYPE,'Wancan', Fvalue,0)) asWancan_value6    fromtest_z2h7   GROUP  byFNAME;

B. Horizontal table to the longitudinal table

Horizontal Table structure: test_h2z         ID      name     Chinese       Math        English                1       Zhang San                     2       John Doe         85                   3       Harry                          The converted table structure:           ID      name     account     score            1       Zhang San     language           2       Zhang San     math           3       Zhang San     english           4       John Doe     Chinese           5       John Doe     math             6       John Doe     English           7       Harry     language           8       Harry     Math           9       Harry     English     88   

1 --Table of horizontal Tables SQL example:2 SELECTName'language'    asSubjects, Languages asResults fromtest_h2zUNION    All    3 SELECTName'Mathematics'    asSubjects, Mathematics asResults fromtest_h2zUNION    All    4 SELECTName'English'    asSubjects, English asResults fromtest_h2z5 ORDER  byName, subjectDESC;

Decode usage of Process control functions in Oracle