Introduction to Oracle DECODE function syntax

The Oracle DECODE function is very powerful. The following describes the usage of the Oracle DECODE function in detail. We hope you can learn more about the Oracle DECODE function.

Oracle DECODE Function

Oracle DECODE function is an exclusive function provided by Oracle. It is a powerful function. Although it is not the standard of SQL, it is very useful for performance. Up to now, other database vendors have not yet provided DECODE-like functions, and some database vendors have even criticized Oracle's SQL standards. In fact, such criticism is somewhat one-sided or inadequate. Just as some carriage manufacturers complained about Henry. Ford's carriage is not standard.

1. if-then-else logic in DECODE

In logical programming, If-Then-Else is often used for logical judgment. In DECODE syntax, this is actually the logic processing process. Its syntax is as follows:
DECODE (value, if1, then1, if2, then2, if3, then3,... else)
Value indicates any column of any type in a table or any result obtained by calculation. When each value is tested, if the value is if1, the result of the Decode function is then1; if the value is if2, the result of the Decode function is then2; and so on. In fact, multiple if/then pairs can be provided. If the value result is not equal to any given pairing, the Decode result returns else.
Note that if, then, and else can both be functions or computing expressions.
Description:
DECODE (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)

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

2 simple DECODE example

In the Oracle system, many data dictionaries are designed using decode, such as the V $ SESSION data dictionary view that records SESSION information. We learned from Oracle8i/9i Reference that after a user logs on successfully, the corresponding records of the user are displayed in V $ SESSION, however, the command operations performed by the user only record the code of the command in this view (0-no operation, 2-Insert ...), Instead of the specific command keyword. Therefore, we need to know the name of each user and their operations, and use the following command to get detailed results:Copy codeThe Code is as follows: 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 is not null;

3 DECODE: transpose a table

Tables in the database are composed of columns and rows.
. The number of columns is usually limited in any database, and the number of rows changes greatly. If the table is large, the number of rows may be larger than 10 million rows. Different rows in the same column may have different values, and they are not pre-defined.
Example: housing provident fund report replacement instance:
1. Each organization opens an account at the local management office. An account is used to register the basic information and employee information of the Organization;
2. every month, the accountant of each organization submits the housing provident fund of all employees of the Unit to the handling bank. The system records the payment details of each employee and records the codes of the handling bank on each record;
3. The statement of the month, quarter, half year, and year-end must be changed to "column:
Handling bank: chengxi district Chengdong District
Month:
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
. . . . . .
The original data sequence is:
Chengxi district 2001.01 xxxxx1.xx
Chengdong district 2001.01 xxxxx2.xx
Chengxi district 2001.02 xxxxx3.xx
Chengdong district 2001.02 xxxxx4.xx
The housing provident fund system records the employee's pay-as-you-go every month. The detailed pay_lst table structure is:

Bank_code varchar2 (6) not null, -- handle line code
Acc_no varchar2 (15) not null, -- unit code (unit account)
Emp_acc_no varchar2 (20) not null, -- employee account
Tran_date date not null, -- Payment date
Tran_val Number () not null, -- pay
Sys_date date default sysdate, -- system date
Oper_id varchar2 (10) -- operator code

In this table structure, it is easy to count rows as rows, but if you want to change rows to columns) it is difficult to output such a format. If the DECODE function is used for processing, it becomes very simple:
We create a view to query the current pay_lst table. You can change the management line code to some specific management line names:Copy codeThe Code is AS follows: create or replace view bank_date_lst
Select to_char (tran_date, 'yyyy. mm '),
SUM (DECODE (bank_code, '001', tran_val, 0) chengxi district,
SUM (DECODE (bank_code, '002', tran_val, 0) south of the city,
SUM (DECODE (bank_code, '003 ', tran_val, 0) Chengdong District
FROM pay_lst
Group by to_char (tran_date, 'yyyy. mm ');

After creating a view, you can directly query the view to display the results by column.