Sign and DECODE functions in Oracle

Source: Internet
Author: User
Compare size function sign Function Syntax: sign (n) Function Description: Take the number n symbol, return 1 if it is greater than 0, return-1 if it is less than 0, return 0 if it is equal to 0. Example: 1. selectsign (100), sign (-100), sign (0) fromdual; SIGN (100) SIGN (-100) SIGN (0) ------------ 1-10 II. a10, b20 si

Compare size function sign Function Syntax: sign (n) Function Description: Take the number n symbol, return 1 if it is greater than 0, return-1 if it is less than 0, return 0 if it is equal to 0. Example: 1. select sign (100), sign (-100), sign (0) from dual; SIGN (100) SIGN (-100) SIGN (0) ---- 1-1 0 2, a = 10, B = 20 then si

Comparison function sign

Function Syntax:
Sign (n)

Function Description:
Returns 1 if the number n is greater than 0,-1 if the value is less than 0, and 0 if the value is 0.

1. select sign (100), sign (-100), sign (0) from dual;

SIGN (100) SIGN (-100) SIGN (0)
1-1 0

2. a = 10, B = 20
Then sign (a-B) returns-1


Function Introduction

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.

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 is often used in logic programming 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 DECODE simple example there are many data dictionaries in the Oracle system designed using the decode idea, 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 implements table transpose. the table in the database is a two-dimensional table consisting 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


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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.