Reprint-oracle Oracle's sign function and decode function

Original address: http://www.cnblogs.com/BetterWF/archive/2012/06/12/2545829.html

Reproduced in reserve

Compare size function Sign

function Syntax:
Sign (n)

Function Description:
Take the sign of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0

Example:
First, select sign (0), sign (-+), sign (dual);

Sign ( -100) sign (0)
———- ———- ———-
1-1 0

Second, a=10,b=20
Sign (A-B) returns-1 Process Control function DECODE

function Introduction　　

The

Decode function is one of the powerful functions of Oracle PL/SQL and is currently available only to Oracle Corporation, where SQL implementations of other database vendors do not yet have this function. What is the use of decode? Let's first construct an example, assuming that we want to raise wages for the smart-star staff, the standard is: The salary is below 8000 yuan plus 20%, the salary is 8000 yuan or above the plus 15%, the usual practice is to select the record of the wage field value? Select Salary to 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 we use the Decode function, then we can omit these flow control statements, which can be done directly through the SQL statement. As follows: Select Decode (sign (salary-8000), >=0,salary*1.15,<0,salary*1.2,salary) is the from employee 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 is returned THEN1,..., Returns else if it is not equal to any of the if values. At first glance, DECODE can only do equals test, but just see, we can use some function or calculation instead of value, it is possible to make the DECODE function more than, less than or equal to the function.

About Decode Decode is an exclusive feature provided by Oracle, a powerful function. Although it is not a standard for SQL, it is very useful for performance. Until now, other database vendors have not been able to provide similar decode capabilities, and even some database vendors have criticized Oracle for its SQL nonstandard. In fact, this criticism is somewhat partial or not level enough. Like some wagon makers complaining about Henry. Ford's "Wagon" is not the same standard. In logic programming, the If-then-else logic in 1 DECODE is often used to if–then–else logic judgment. 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 of 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 The result of the function is then2; In fact, multiple if/then pairs can be given. If the value result is not equal to any given pairing, the Decode result returns else.   It's important to note that if, then, and else here can be functions or calculation expressions. 2 DECODE Simple Examples There are many data dictionaries in the Oracle system 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 that when a user logs in successfully, there is a corresponding record for that user in v$session, but the command action that the user takes in the view only records the command code (0-No action, 2-insert ... Instead of the specific command keyword. Therefore, we need to know the names of the current individual users and what they are doing, to use the following command to obtain detailed results:  select sid,serial#,username,  DECODE (command,  0, ' None ',  2, ' Insert ',  3, ' Select ',  6, ' Update ',  7, ' Delete ',  8, ' Drop ',  ' other ') Cmmand　 　The tables in the transpose database of the from V$session where username are not null;3 decode implementation tables are a two-dimensional table consisting of columns and rows. The General column is a limited number in any database, and the rows vary greatly, 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, account opening is the basic information of the Unit and staff information to register;  2. Each unit of the accounting to the office of the company to pay all employees of the Housing Provident Fund, The system records the payment details of each employee and records the code;  3. Monthly, quarterly, semi-annual and year-end requirements to change the business line to "column" for the monthly detail report:  Office: Chengxi District East   Month:  2001.01 x xxx1.xx xxxxx2.xx  2001.02 xxxx3.xx xxxxx4.xx  ...   Original data order is:  Chengxi District 2001.01 xxxxx1.xx  chengdong 2001.01 xxxxx2.xx  Chengxi District 2001.02 xxxxx3.xx  chengdong 2001.02 xx xxx4.xx  Housing Provident Fund System Records employees ' monthly payment name PAY_LST table structure is:  bank_code varchar2 (6) Not NULL,--handling line code   ACC_NO VARCHAR2 (n) OT null,--Unit Code (unit number)   Emp_acc_no varchar2 () not NULL,--employee account number   Tran_date date not null--date of payment   TRA　　N_val number (7,2) not NULL,--payment   sys_date date default Sysdate,--system date   oper_id VARCHAR2 (10)--Operator Code   Such a table structure is generally easy to count as rows (row), but if you want to format a row into columns (column) to outputThere are difficulties. If you use the Decode function to process it becomes very simple:  we create a view to query the current PAY_LST table. Change the handling line code to a specific line name:  CREATE OR REPLACE VIEW bank_date_lst as  Select to_char (tran_date, ' yyyy.mm '),  SU M (DECODE (Bank_code, ' 001 ', tran_val,0)) Chengxi District,  sum (DECODE (Bank_code, ' 002 ', tran_val,0)) city South,  sum (decod E (Bank_code, ' 003 ', tran_val,0)) east   from pay_lst  GROUP by To_char (tran_date, ' yyyy.mm ');  set up a view, you can directly The view is queried to show the results in columns.  

Reprint-oracle Oracle's sign function and decode function