Oracle Foundation (VII): null operation and null function

Time:2022-6-22

1、 Operation of null value

1. Prepare a data sheet

CREATE TABLE student(
        id NUMBER(4),
        name CHAR(20),
        gender CHAR(1)
);
Insert into student values (1000, 'Li Mochou', 'F');
Insert into student values (1001, 'Lin Pingzhi', null)-- Explicitly insert null values
Insert into student (ID, name) values (1002, 'Zhang Wuji')-- Implicit insertion of null values
commit;
select * from student;

(1) Update field toNULL(if the field has a non empty constraint, it cannot be updated to a null value, or an error will be reported.)

update student set gender=null where id=1000;

(2) Judge whether the field value isNULL, to useIS NULLorIS NOT NULL

delete from student where gender is null;

2. Operation of null value

NULLWith any number, the result isNULLNULLConnecting to a string is doing nothing.

--Null value connection string
select ename||null from emp;-- Return to the original value of the field

--Null value and number operation
select sal+null from emp;-- Return null

--View each employee's income, salary + performance
select ename,sal,comm,sal+comm from emp;-- Sal+comm returns null

2、 Null function

1、NVL(arg1,arg2)

When arg1 is null, the function returns the value of arg2. If it is not null, it returns arg1 itself (the parameter types must be consistent),

So the function is to replace the null value with a non null value.

--View each employee's income (salary + performance)
select ename,sal,comm,sal+nvl(comm,0) from emp;

2、NVL2(arg1,arg2,arg3)

This function returns two different results based on whether a value is null or not.

Arg1 is the judgment condition. When arg1 is not null, the function returns arg2. If arg1 is null, the function returns ARG3

--Check whether each employee has performance, that is, if there is performance, it will display "performance", if it is null, it will display "no performance"
Select ename, comm, nvl2 (comm, 'with performance' and 'without performance') from EMP;

--View the income (salary + performance) of each employee. If it is not null, it returns salary + performance. If it is null, it returns ARG3
select ename,sal,comm,sal+NVL2(comm,comm,0) from emp;
select ename,sal,comm,NVL2(comm,sal+comm,sal) from emp;