ORACLE学习笔记系列（8）ORACLE中的空值(NULL)和与空值(NULL)有关的函数

 ORACLE学习笔记系列（8）ORACLE中的空值(NULL)和与空值(NULL)有关的函数

 ORACLE中在我们不知道具体有什么数据的时候，也即未知，可以用NULL，称之为空。
 ORACLE中，含有空值的表列长度为零。

 ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：
    a、主键字段（primary key）
    b、定义时已经加了NOT NULL限制条件的字段

与空值(NULL)有关的情况：
1、NULL等价于没有任何值、是未知数。
2、ORACLE中NULL与0，空字符串，空格，包括NULL与NULL也是不等的。
3、对NULL值做加、减、乘、除等运算操作，结果仍为NULL。
4、NULL的处理使用 NVL(EXPR1,EXPR2)函数。
5、比较时使用关键字用“IS NULL”和“IS NOT NULL”。
6、空值不能被索引，当指定列来统计数据行数时，NULL值行将被忽略不统计，COUNT(COLUMN1)中不包括COLUMN1为NULL的行。
7、求和SUM(COLUMN1)函数，将忽略COLUMN1为NULL的行，但影响统计结果。
8、排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后。可以使用“NULLS FIRST”和“NULLS LAST”改变NUUL值的排序。
9、“IN”、“NOT IN”和 子查询中出现空值。
10、使用LIKE模糊查询时，NULL不能被%匹配到。
11、ORACLE中与空值(NULL)有关的函数。


--1
SELECT NULL, '', '  ', 0 FROM DUAL;

--2
SELECT 'NULL=NULL' AS T, COUNT(*) AS C FROM DUAL WHERE NULL = NULL;
SELECT 'NULL=''''' AS T, COUNT(*) AS C FROM DUAL WHERE NULL = '';
SELECT 'NULL=0' AS T, COUNT(*) AS C FROM DUAL WHERE NULL = 0;
SELECT '''''=''''' AS T, COUNT(*) AS C FROM DUAL WHERE '' = '';
SELECT 'a!=null' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' != NULL;
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' = 'a';
 
--3
SELECT 1 + NULL, 1 - NULL, 1 * NULL, 1 / NULL FROM DUAL;
SELECT '1 + NULL IS NULL' AS T, COUNT(*) AS C FROM DUAL WHERE 1 + NULL IS NULL;
SELECT '1 - NULL IS NULL' AS T, COUNT(*) AS C FROM DUAL WHERE 1 - NULL IS NULL;
SELECT '1 * NULL IS NULL' AS T, COUNT(*) AS C FROM DUAL WHERE 1 * NULL IS NULL;
SELECT '1 / NULL IS NULL' AS T, COUNT(*) AS C FROM DUAL WHERE 1 / NULL IS NULL;
  
--4
SELECT NVL(NULL, 1), NVL(NULL, 'a'), NVL(NULL, SYSDATE), NVL('a', 1) FROM DUAL;

--5
SELECT 'NULL IS NULL' AS T, COUNT(*) AS C FROM DUAL WHERE NULL IS NULL;
SELECT ''''' IS NULL' AS T, COUNT(*) AS C FROM DUAL WHERE '' IS NULL;
SELECT 'NVL IS NOT NULL' AS T, COUNT(*) AS C FROM DUAL WHERE NVL(NULL, 0) IS NOT NULL;

--6
SELECT COUNT(*), COUNT(1), COUNT(NULL), COUNT(NVL(NULL, 0)) FROM DUAL;
 
--7
SELECT COUNT(*), SUM(1), SUM(LEVEL), SUM(NVL(NULL, 0)) FROM DUAL CONNECT BY LEVEL <= 4;

--8
SELECT * FROM B ORDER BY NAME NULLS FIRST;
SELECT * FROM B ORDER BY NAME NULLS LAST;

--9
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' = (NULL);
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' IN (NULL);
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' IN ('a', NULL);

SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'b' = (NULL);
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'b' NOT IN (NULL);
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'b' NOT IN ('a', NULL);

SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' = (SELECT NULL FROM DUAL);
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' IN (SELECT NULL FROM DUAL);
SELECT 'a=a' AS T, COUNT(*) AS C FROM DUAL WHERE 'a' NOT IN (SELECT NULL FROM DUAL);

--10 
SELECT 'NULL LIKE %' AS T, COUNT(*) AS C FROM DUAL WHERE NULL LIKE '%';
 

--11 ORACLE中与空值(NULL)有关的函数

（1）NVL
格式：NVL(EXPR1,EXPR2) 
等价于： (CASE WHEN EXPR1 IS NULL THEN EXPR2 ELSE EXPR1 END)
说明：NVL(EXPR1,EXPR2) EXPR1为NULL时，取EXPR2的值，否则取EXPR1的值；
使用限制：可以用于SELECT子句和WHERE子句中；
          EXPR1,EXPR2 数据类型可以不相同；

（2）NVL2 
格式：NVL2(EXPR1,EXPR2,EXPR3) 
等价于： (CASE WHEN EXPR1 IS NULL THEN EXPR3 ELSE EXPR2 END) 
说明：NVL2(EXPR1,EXPR2,EXPR3) EXPR1为null时，取EXPR3的值，否则取EXPR1的值；        
使用限制：expr1可以是任意类型，expr2,expr3不能是long类型。
          如果 expr2是字符类型，那么expr3转为字符型再比较（null除外）。
          如果 expr2是数值类型，那么expr3也转为对应的数值类型。
          各个参数都不能是逻辑表达式。

（3)NULLIF 
格式： NULLIF(EXPR1,EXPR2) 
等价于：(CASE WHEN EXPR1=EXPR2 THEN NULL ELSE EXPR1 END) 
说明：NULLIF(EXPR1,EXPR2) 如果EXPR1和EXPR2相等，返回 NULL，否则返回EXPR1；如果EXPR1和EXPR2有一个为NULL时，返回NULL；
使用限制：expr1不能是标识符null，录入nullif(null,expr2)那么会提示错误。
          expr1,expr2 都必须是一个变量或者是一个常量表达式，不能是逻辑表达式。
       
（4）LNNVL
格式： LNNVL(EXPR1)  EXPR1是一个表达式
等价于：假设a number(10)；lnnvl(a>10) 等价于 nvl(a,0)<=10；等价于  a<=10 or a is null；
说明：如果EXPR1的结果是false或者是unknown,那么lnnvl返回true；如果EXPR1的结果是true,返回false；
使用限制：表达式的操作符号不能包含 AND, OR,  BETWEEN；

（5）COALESCE
格式：COALESCE(EXPR_1,EXPR_2, ……,EXPR_n) 
等价于：(CASE WHEN EXPR_1 IS NULL THEN (CASE WHEN EXPR_2 IS NULL THEN (……,EXPR_n) ELSE EXPR_2 END) ELSE EXPR_1 END)
说明：从左到右返回第一个不为NULL的值；
使用限制：EXPR_1至EXPR_n的数据类型必须一致；
  

SELECT NVL('G', 5),
       NVL(NULL, 5),
       NVL(NULL, SYSDATE),
       
       NVL2(NULL, 2, '3'),
       NVL2(0, 'a', 'b'),
       
       --NULLIF(NULL, 1),
       NULLIF(1, 1),
       NULLIF(1, 2),
       NULLIF('a', 'b'),
       
       (CASE
         WHEN LNNVL('a' = 'x1') THEN
          'a=x1:true'
         ELSE
          'a=x1:false'
       END),
       
       COALESCE(NULL, '', '3', 'a')

  FROM DUAL;