Oracle function usage

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