Detailed usage of nvl, nvl2, nullif, coalesce, and decode functions in Oracle

Nvl (expr1, expr2)
Nvl2 (expr1, expr2, expr3)
Nullif (expr1, expr2)
Coalesce (expr1,..., exprn)
Decode

-------- Nvl
Nvl (commission_pct, 0)
If the first parameter is null, the second parameter is returned.
If the first parameter is not null, the first parameter is returned.

Typical Example: Calculate the annual salary (salary + Commission)
Select last_name, salary, nvl (commission_pct, 0 ),
(Salary * 12) + (salary * 12 * nvl (commission_pct, 0) annual_salary
From employees
Where last_name = 'matos ';

-------- Nvl2
Nvl2 (commission_pct, 'sal _ comm ', 'sal ')
If the first parameter is null, the third parameter is returned.
If the first parameter is not null, the second parameter is returned.
Classic example:
Select last_name, salary, commission_pct,
Nvl2 (commission_pct, 'sal + comm ', 'sal') Income
From employees
Where department_id in (50, 80 );

--------- Nullif
Nullif (length (first_name), length (last_name ))
If the two parameters are equal, null is returned.
If the two parameters are different, the first expression value is returned.

Select first_name, length (first_name) "expr1 ",
Last_name, length (last_name) "expr2 ",
Nullif (length (first_name), length (last_name) Result
From employees;

--------- Coalesce
Coalesce (expr1, expr2, expr3.. exprn)
From left to right. If the first non-null value is returned, the non-null value is returned.
Multi-layer judgment

Select last_name, employee_id,
Coalesce (to_char (commission_pct), to_char (manager_id ),
'No Commission and no manager ')
From employees;

---------------------------------
----------- Conditional expression
If-then-Else

Case Statement (SQL standard, complicated compilation)
Decode Function
Decode (COL | expression, serach1, result1
[, Search2, result2,...,]
[, Default])
If search1 = expression, result1 is returned.
If search2 = expression, result2 is returned.
--------- Case
Example:
Select last_name, job_id, salary,
Case job_id when 'It _ prog' then 1.10 * salary
When 'st _ cler' then 1.15 * salary
When 'sa _ rep 'Then 1.20 * salary
Else salary end "revised salary"
From employees;

--------- Decode
Example:
Decode function: Compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value.

Unlike other SQL functions, the decode function can recognize and operate null values.

Syntax:

Decode (control_value, value1, result1 [, value2, result2…] [, Default_result]);

Control _ value

The value to be processed. The decode function compares the value with a series of even orders to determine the return value.

Value1

Is the value of an ordinal pair. If the input value matches the value, the corresponding result is returned. Corresponds to an empty return value. You can use the keyword null

Result1

Is the result value of an ordinal pair.

If default_result does not match any value, the default value returned by the function.

Example:

Select decode (x, 1, 'x is 1', 2, 'x is 2', 'others ') from dual

Please indicate the source and original link for reprinting; otherwise, the reprinting will be rejected.

Http://blog.csdn.net/xiangsir/article/details/8601513