Handling Null values in Oracle | Oracle interview question difference between null functions |Tamil

Published: 04 December 2020
on channel: NIC IT ACADEMY

5,606

#Oracle#NVL#NVL2#NULLIF#COALESCE

========================================================
🌕Want to join WhatsApp Group? (Only technical discussion, no spam messages, no promotion msg)

📌Informatica PowerCenter
https://github.com/nicitacademy/Infor...

📌Oracle-SQL
https://github.com/nicitacademy/Oracl...

📌IICS
https://github.com/nicitacademy/IICS
========================================================

Handling Null values in Oracle:
================================

NVL - 2 arg
NVL2 - 3 arg
Nullif - 2 arg
Coalesce - n arg

NVL function will check whether the first input parameter is null, if first input parameter is null then the function returns the second parameter value as output.

NVL2 function will check the first parameter, and return second parameter if the first parameter is not null, otherwise returns third parameter.

NULLIF will compare the input parameters ( first and second parameter ), and returns NULL of both are same, otherwise returns the first parameter value.

COALESCE, returns the first not null expression in the given input parameters

nvl function in sql
what is nvl function in oracle
what is the use of nvl2 function in oracle
what is the use of nvl function in oracle
Oracle NVL() Function By Practical Examples - Oracle Tutorial
Oracle / PLSQL: NVL Function
How to use the nvl function in Oracle
Oracle NVL Function Explained with Examples
NVL(arg1,arg2)

if arg1 is null ==arg2
if arg1 is not null == arg1

select NVL(5,6) from dual; -- 5

select NVL(null,6) from dual; --6

select * from employees;

select employee_id,salary,commission_pct,salary+(salary*commission_pct) total_salary from employees;

select employee_id,salary,commission_pct,salary+(salary*nvL(commission_pct,0)) total_salary from employees;

NVL2(arg1,arg2,arg3)

if arg1 is null --- arg3
if arg1 is not null -- arg2

select nvl2(4,8,12) from dual; --8

select nvl2(null,8,12) from dual; -- 12

create table employee
(
employee_id number,
emp_name varchar2(30),
allocation_id number(10),
location varchar2(30)
);

SELECT employee_id,emp_name,
NVL2(allocation_id, 'Allocated', 'Waiting for project') allocation_status
FROM EMPLOYEE;

-----------------------------------------------------------

nullif(arg1,arg2)

if arg1=arg2 --- null
if arg1 != arg2 --- arg1

select nullif(5,8) from dual; --5

select nullif(8,8) from dual; -- null

CREATE OR REPLACE FUNCTION FUNCTION_DIV(A NUMBER, B NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN 8/0;
END;

select FUNCTION_DIV(4,0) from dual;

FUNCTION_DIV(5,0) will raise ORA-01476:divide by zero exception.

CREATE OR REPLACE FUNCTION F_DIV(A NUMBER, B NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (A/NULLIF(B,0));
END;

select FUNCTION_DIV(4,2) from dual;

FUNCTION_DIV(5,0) would return NULL, instead of raising exception.

-----------------------------------------------------------

coalesce(arg1,arg2,arg3.. ..arg_n)

-- It will return first not null value

COALESCE -- It will always return first not null value

select commission_pct,manager_id,department_id from employees;

select commission_pct,manager_id,department_id,
COALESCE(commission_pct,manager_id,department_id,0) from employees;

select coalesce(mobile_no,office_no,resi_no,'no_phone_number') from dual;

Watch video Handling Null values in Oracle | Oracle interview question difference between null functions |Tamil online without registration, duration hours minute second in high quality. This video was added by user NIC IT ACADEMY 04 December 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 5,606 once and liked it like people.

785