The string processing, date functions, mathematical functions, and conversion functions previously introduced in Oracle are also common functions. Mainly include NVL, NVL2, NULLIF, and COALESCE. These functions can be used in various types.
The following describes the usage of several functions.
Before introducing this, you must understand what is null in oracle?
1. NVL Function
The NVL function format is as follows: NVL (expr1, expr2)
Meaning: if the first oracle parameter is null, the value of the second parameter is displayed. If the value of the first parameter is not empty, the original value of the first parameter is displayed.
For example:
SQL> select ename, NVL (comm,-1) from emp;
Ename nvl (COMM,-1)
-----------
SMITH-1
ALLEN 300
WARD 500
JONES-1
MARTIN 1400
BLAKE-1
FORD-1
MILLER-1
The original values of-1 are all null values.
2 NVL2 Functions
The format of the NVL2 function is as follows: NVL2 (expr1, expr2, expr3)
Meaning: if the first parameter of the function is null, the value of the second parameter is displayed. If the value of the first parameter is not null, the value of the third parameter is displayed.
SQL> select ename, NVL2 (comm,-1, 1) from emp;
ENAME NVL2 (COMM,-1, 1)
------------
SMITH 1
ALLEN-1
WARD-1
JONES 1
MARTIN-1
BLAKE 1
CLARK 1
SCOTT 1
In the above example. If the result is 1, it is not null, but the original value of-1 is null.
3. NULLIF Function
The NULLIF (exp1, expr2) function returns NULL if exp1 and exp2 are equal. Otherwise, the first value is returned.
The following is an example. The HR schema in oracle is used. If HR is locked, enable
The role here is to show the original work of those who have changed their jobs.
SQL> SELECT e. last_name, e. job_id, j. job_id, NULLIF (e. job_id, j. job_id) "Old Job ID"
FROM employees e, job_history j
WHERE e. employee_id = j. employee_id
Order by last_name;
LAST_NAME JOB_ID Old Job ID
--------------------------------------
De Haan AD_VP IT_PROG AD_VP
Hartstein MK_MAN MK_REP MK_MAN
Kaufling ST_MAN ST_CLERK ST_MAN
Kochhar AD_VP AC_MGR AD_VP
Kochhar AD_VP AC_ACCOUNT AD_VP
Raphaely PU_MAN ST_CLERK PU_MAN
Taylor SA_REP SA_MAN SA_REP
Taylor SA_REP
Whalen AD_ASST AC_ACCOUNT AD_ASST
Whalen AD_ASST
We can see all the employees. If job_id and job_histroy.job_id are equal, NULL is output in the result, otherwise the result is displayed as employee. Job_id
4. Coalesce Function
The role of the Coalese function is that the NVL function is somewhat similar. Its advantage is that there are more options.
The format is as follows:
Coalesce (expr1, expr2, expr3 ..... Exprn)
Coalesce is used to process these parameters. If the first parameter is null, check whether the second parameter is null. Otherwise, the first parameter is displayed. If the second parameter is null, check whether the third parameter is null, otherwise, the second parameter is displayed, and so on.
This function is actually used cyclically by NVL. We will not give an example here.