Oracle decode Functions

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 is less than 8000 RMB plus 20%; the salary is more than 8000 RMB plus 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),> = 0, salary * 1.15, <0, 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.

About DECODE
DECODE 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.
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:
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
A table in the database is a two-dimensional table 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:
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.