Introduction to the use of Oracle DECODE function syntax
- 2021-07-22 11:48:11
- OfStack
The Oracle DECODE function is very powerful. Here is a detailed introduction to the usage of Oracle DECODE function, hoping to give you a better understanding of Oracle DECODE function.
Oracle DECODE function
Oracle DECODE function is an exclusive function provided by Oracle, it is a very powerful function. Although it is not the standard of SQL, it is very useful for performance. Up to now, other database vendors can't provide similar functions to DECODE, and even some database vendors criticize Oracle's SQL as nonstandard. In fact, this criticism is somewhat one-sided or inadequate. Just like some carriage manufacturers complain about Henry. Ford's "carriage" is not standard.
1 if-then-else Logic in DECODE
In logic programming, If Then Else is often used for logic judgment. In the syntax of DECODE, this is actually such a logical process. Its syntax is as follows:
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value stands for any column of any type of a table or any result of a computation. When each value value is tested, if the value of value is if1, the result of Decode function is then1; If value equals if2, the result of the Decode function is then2; Wait. In fact, multiple if/then pairs can be given. If the value result is not equal to any of the pairs given, the Decode result returns else.
Note that if, then, and else can all be functions or computed expressions.
Interpretation of meaning:
DECODE (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default)
The meaning of this 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)
END IF
2 Simple example of DECODE
Many data dictionaries in the Oracle system are designed using the decode idea, such as the V $SESSION data dictionary view that records session information. We learned from the "Oracle8i/9i Reference" data that when a user logs in successfully, the user is recorded in V $SESSION, but the command actions performed by the user are recorded in this view only the code of the command (0-nothing, 2-Insert...), not the specific command keywords. Therefore, when we need to know the names of current users and what they are doing, we need to use the following command to get detailed results:
3 DECODE Realizes the Transposition of Tables
Tables in a database are composed of columns and rows
A 2-dimensional table of the. 1 general columns in any database are limited in number, and rows change greatly. If the table is large, the number of rows may 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 opens an account in the local handling bank, which means registering the basic information and employee information of the unit;
2. Every month, the accountant of each unit pays the housing accumulation fund of all employees of the unit to the handling bank, and the system records the payment details of each employee and records the code of the handling bank on each record;
3. Every month, quarter, half a year and year-end, it is required to change the handling bank into a "column" and give a detailed report for each month:
Handling bank: Chengdong District, Chengxi District
Month:
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
. . . . . .
The original data order 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 structure of pay_lst table for recording the monthly payment of employees in the housing provident fund system is as follows:
bank_code varchar2 (6) NOT NULL,--Handling bank code
acc_no varchar2 (15) not null--Unit code (unit account number)
emp_acc_no varchar2 (20) not null--Employee Account Number
tran_date date not null,-Date of submission
tran_val Number (7, 2) not null
sys_date date default sysdate,--System date
oper_id varchar2 (10)--Operator code
In such a table structure, it is generally easy to count the handling rows as rows (row), but it is difficult to output the handling rows as columns (column). If you use the DECODE function to handle it, it becomes very simple:
We create a view to query the current pay_lst table. Change the code of the handling bank into one specific name of the handling bank:
After creating a view, you can query the view directly to display the results by column.
Oracle DECODE function
Oracle DECODE function is an exclusive function provided by Oracle, it is a very powerful function. Although it is not the standard of SQL, it is very useful for performance. Up to now, other database vendors can't provide similar functions to DECODE, and even some database vendors criticize Oracle's SQL as nonstandard. In fact, this criticism is somewhat one-sided or inadequate. Just like some carriage manufacturers complain about Henry. Ford's "carriage" is not standard.
1 if-then-else Logic in DECODE
In logic programming, If Then Else is often used for logic judgment. In the syntax of DECODE, this is actually such a logical process. Its syntax is as follows:
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value stands for any column of any type of a table or any result of a computation. When each value value is tested, if the value of value is if1, the result of Decode function is then1; If value equals if2, the result of the Decode function is then2; Wait. In fact, multiple if/then pairs can be given. If the value result is not equal to any of the pairs given, the Decode result returns else.
Note that if, then, and else can all be functions or computed expressions.
Interpretation of meaning:
DECODE (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default)
The meaning of this 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)
END IF
2 Simple example of DECODE
Many data dictionaries in the Oracle system are designed using the decode idea, such as the V $SESSION data dictionary view that records session information. We learned from the "Oracle8i/9i Reference" data that when a user logs in successfully, the user is recorded in V $SESSION, but the command actions performed by the user are recorded in this view only the code of the command (0-nothing, 2-Insert...), not the specific command keywords. Therefore, when we need to know the names of current users and what they are doing, we need to 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 Realizes the Transposition of Tables
Tables in a database are composed of columns and rows
A 2-dimensional table of the. 1 general columns in any database are limited in number, and rows change greatly. If the table is large, the number of rows may 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 opens an account in the local handling bank, which means registering the basic information and employee information of the unit;
2. Every month, the accountant of each unit pays the housing accumulation fund of all employees of the unit to the handling bank, and the system records the payment details of each employee and records the code of the handling bank on each record;
3. Every month, quarter, half a year and year-end, it is required to change the handling bank into a "column" and give a detailed report for each month:
Handling bank: Chengdong District, Chengxi District
Month:
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
. . . . . .
The original data order 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 structure of pay_lst table for recording the monthly payment of employees in the housing provident fund system is as follows:
bank_code varchar2 (6) NOT NULL,--Handling bank code
acc_no varchar2 (15) not null--Unit code (unit account number)
emp_acc_no varchar2 (20) not null--Employee Account Number
tran_date date not null,-Date of submission
tran_val Number (7, 2) not null
sys_date date default sysdate,--System date
oper_id varchar2 (10)--Operator code
In such a table structure, it is generally easy to count the handling rows as rows (row), but it is difficult to output the handling rows as columns (column). If you use the DECODE function to handle it, it becomes very simple:
We create a view to query the current pay_lst table. Change the code of the handling bank into one specific name of the handling bank:
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 )) Chengnan District,
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 query the view directly to display the results by column.