Decode function __ function

The Decode function is an Oracle Pl/sql is one of the powerful functions, and currently only Oracle company SQL provides this function, other database vendors do not have the SQL implementation. What is the use of decode? To construct an example, suppose we want to add a salary to the intelligence Star staff, the standard is: the salary at 8000 yuan will add 20%, wages in 8000 yuan plus 15%, the usual practice is to first select the record of the wage field value? Select salary into 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 you use the Decode function, then we can omit these flow control statements, through the SQL statement can be directly completed. As follows: Select Decode (sign (salary-8000), 1,salary*1.15,-1,salary*1.2,salary from employee is not very concise. DECODE syntax: DECODE (value,if1,then1,if2,then2,if3,then3,..., else), indicating that if value equals IF1, the result of the DECODE function returns to Then1,..., Returns else if it is not equal to any one if value. At first glance, DECODE can only do equals test, but just also saw that we use some function or calculate substitution value, is can make DECODE function have greater than, less than or equal to function.
--------------------------------------------------------------------------------------------------------------- -------------------
　　 about decode
Decode 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.
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:
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
A table in a database is a two-dimensional table consisting of columns and rows. 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:
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.