Detail NULL values in Oracle

1. What is NULL?
Null means Unknow (unknown), which does not mean whatever the value is.

For example, a column in a row does not have any value or null.

Oracle agrees that no matter what type of data the field is empty, in addition to the following two scenarios:
1) Primary key field (primary key),
2) A field with a NOT NULL constraint defined


2. What's the use of NULL?
1) null can be used for conditional inference:
SELECT * from EMP WHERE COMM is NULL;
Or
SELECT * from EMP WHERE COMM are not NULL;


3. What are the common precautions for using null?
1) Infer whether the column or variable is null. Can only use conditions is NULL or is not NULL
2) null value cannot be indexed
3) Null in the sort operation defaults to the maximum value processing:
SELECT * from EMP ORDER by Comm DESC;
4) Null in Oracle and no matter what value arithmetic operation result is null
#加减乘除操作:
SELECT 1+null from DUAL;
SELECT 1-null from DUAL;
SELECT 1*null from DUAL;
SELECT 1/null FORM DUAL;


5) null cannot be used with the comparison operator such as =, <>! =, consider how the following block of statements output?
DECLARE
N1 VARCHAR2 (20);
N2 VARCHAR2 (): = ' a ';
BEGIN
IF N1 <> N2 then--equivalent to NULL
Dbms_output.put_line (' <> ');
ELSE
Dbms_output.put_line (' = ');
END IF;
END;


Output: =


6) in the work. Especially in report development. In SQL statements involving quantity, amount statistics, and so on, the NVL function is used to ignore null values.


4, the related NULL function uses the method introduction:
1) NVL (P1,P2) function. If P1 is empty, it returns P2, otherwise P1 is returned.

The following example can be seen with regard to the processing of the previous statement block:
DECLARE
N1 VARCHAR2 (20);
N2 VARCHAR2 (): = ' a ';
BEGIN
IF NVL (N1, ' xx ') <> N2 Then
Dbms_output.put_line (' <> ');
ELSE
Dbms_output.put_line (' = ');
END IF;
END;


2) NVL2 (P1,P2,P3) function, assuming P1 non-null returns P2, otherwise returns P3:
SELECT NVL2 (null,1,2) from DUAL;
SELECT NVL2 (0,1,2) from DUAL;


3) Nullif (P1,P2) function. Suppose P1=P2 returns NULL. Otherwise, return P1:
SELECT Nullif from DUAL;
SELECT Nullif from DUAL;


4) COALESCE (P1,P2. Pn) function, return the first non-null value:

SELECT COALESCE (null,null,1,2,3) from DUAL;

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

Dylan presents.

Detail NULL values in Oracle