35 | How to use GENERAL FUNCTION in ORACLE SQL | Oracle PL/SQL Programming

The general functions work with any data type and are mainly used to handle null values. The Oracle general functions are

The NVL function takes two arguments as its input. If the first argument is NULL, then it returns the second argument otherwise it returns the first argument.

SELECT NVL(10,2) FROM DUAL;
SELECT NVL(NULL,'Oracle') FROM DUAL;
SELECT NVL(NULL,NULL) FROM DUAL;

Oracle NVL2 Function

The syntax of NVL2 function is

NVL2(expr1,expr2,expr3)

The NVL2 function takes three arguments as its input. If the expr1 is NOT NULL, NVL2 function returns expr2. If expr1 is NULL, then NVL2 returns expr3.

SELECT NVL2('Ora','SID','TNS') FROM DUAL;
SELECT NVL2(NULL,'SID','TNS') FROM DUAL;

Oracle NULLIF Function

The Syntax of NULLIF function is

NULLIF(expr1, expr2)

The NULLIF function compares the two expressions and returns NULL if they are equal otherwise it returns the first expression.

SELECT NULLIF('Oracle','MYSQL') FROM DUAL;
SELECT NULLIF('MYSQL','MYSQL') FROM DUAL;

Oracle COALESCE Function

The Syntax of COALESCE function is

COALESCE(expr1,expr2,expr3,...)

The COALESCE function takes N number of arguments as its input and returns the first NON-NULL argument.

SELECT COALESCE('DB Backup','Oracle') FROM DUAL;
SELECT COALESCE(NULL,'MYSQL',NULL) FROM DUAL;

35 | How to use GENERAL FUNCTION in ORACLE SQL | Oracle PL/SQL Programming

#NVL #NVL2 #COALESCE #GENERAL_FUNCTION