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

Опубликовано: 04 Декабрь 2020
на канале: 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;

Смотрите видео Handling Null values in Oracle | Oracle interview question difference between null functions |Tamil онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь NIC IT ACADEMY 04 Декабрь 2020, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 5,606 раз и оно понравилось like людям.

2,305