Oracle function usage

Source: Internet
Author: User

1. decode function usage

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 of less than 8000 yuan will be increased by 20%; the salary of more than 8000 yuan will be increased by 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), 1, salary * 1.15,-1, 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.

 

2. sign

The sign () function returns 0, 1, and-1 based on a value of 0, positive, or negative. For example, if a = 10 is referenced and B = 20 is used, sign (a-B) is returned) -1 is returned.

3. wmsys. wm_concat ()

Merge multiple rows into one row

4. NVL, NVL2, NULLIF, and decode functions in oracle:

NVL determines whether it is null: NVL (exp1, exp2) If exp1 is not null, exp2 is returned; otherwise, exp1 is returned;

NVL2 (exp1, exp2, exp3) returns exp2 if exp1 is not null; otherwise, returns exp3;

NULLIF (exp1, exp2) returns null if exp1 = exp2; otherwise, returns exp1;

Decode (exp1, exp2, exp3, exp4, exp5); returns exp3 if exp1 = exp2 if exp4 is equal to exp5

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: info-contact@alibabacloud.com 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.