Oracle decode functions are very powerful, and here's a detailed description of the use of the Oracle decode function, hopefully giving you a better understanding of Oracle decode functions.
Oracle decode functions
The Oracle decode function is an exclusive feature provided by Oracle, and it is a powerful function. Although it is not a standard for SQL, it is useful for performance. To date, other database vendors have not been able to provide functionality similar to decode, and even some database vendors have criticized Oracle's SQL for not being standard. In fact, this criticism is somewhat one-sided or not level enough. It's like some wagon makers complaining about Henry. Ford's "Wagon" is not standard.
the If-then-else logic in the 1 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)
Value represents any column of any type in a table, or any result that is computed. When each value is tested, if value is the result of the If1,decode function is then1, and if value equals If2,decode function The result is then2; In fact, multiple if/then pairs can be given. If the value result is not equal to any pairing given, the Decode result returns else.
Note that the IF, then, and else here can all be functions or calculation expressions.
Meaning explanation:
DECODE (condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation 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)
ELSE
return (default value)
End IF
2 simple examples of DECODE
There are many data dictionaries in Oracle systems that are designed using decode ideas, such as the v$session data dictionary view that records session information. We learned from the "oracle8i/9i Reference" material that when the user login successfully in V$session there is a corresponding record of the user, but the user's command operation in this view only record the command code (0-no operation, 2-insert ... Instead of the specific command keyword. So we need to know the names of the current users and what they are doing, and use the following command to get detailed results:
Copy Code code 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 are not null;
3 decode to realize table transpose
Tables in a database are composed of columns and rows
A two-dimensional table. A general column is a limited number in any database, and the row changes a lot, and if the table is large, the number of rows can be tens of millions of rows. Different rows of the same column may have different values, and are not predefined.
Example: Housing Provident Fund Report replacement Example:
1. Each unit in the local handling bank to open an account, the account is the unit's basic information and staff information for registration;
2. The monthly accounting of each unit to the handling bank to pay the units of all employees of the Housing Provident Fund, the system records the payment of each employee details and records on each record has the handling line code;
3. Monthly, quarterly, Half-year and year-end requirements will be the handling of the line into the "column" to give a detailed report of the month:
Handling line: Chengxi District Dongqu
Month:
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
。 。 。 。 。 。
The original data order is:
Chengxi District 2001.01 xxxxx1.xx
Dongqu 2001.01 xxxxx2.xx
Chengxi District 2001.02 xxxxx3.xx
Dongqu 2001.02 xxxxx4.xx
Housing Provident Fund System records the monthly payment of the employee's PAY_LST table structure is:
Bank_code VARCHAR2 (6) Not NULL,--handling line code
Acc_no varchar2 NOT NULL,--Unit Code (unit account number)
Emp_acc_no varchar2 NOT NULL,--Employee account number
Tran_date date NOT NULL,--Payment dates
Tran_val number (7,2) not NULL,--payment amount
Sys_date Date Default Sysdate,--system dates
oper_id VARCHAR2 (10)--Operator code
Such a table structure, typically by counting the rows as rows (row), is easy to do, but it is difficult to export the handling line to a format such as column. If you use the Decode function to handle it, it becomes simple:
We create a view to query the current PAY_LST table. The handling line code into a number of specific handling line name can be:
Copy Code code as follows:
CREATE OR REPLACE VIEW Bank_date_lst as
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)) The Southern District of the city,
SUM (DECODE (Bank_code, ' 003 ', tran_val,0)) Dongqu
From Pay_lst
GROUP by To_char (tran_date, ' yyyy.mm ');
Once you have established a view, you can query the view directly to show the results in columns.
