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)有关的函数。--1SELECT NULL, '', ' ', 0 FROM DUAL;--2SELECT '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'; --3SELECT 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; --4SELECT NVL(NULL, 1), NVL(NULL, 'a'), NVL(NULL, SYSDATE), NVL('a', 1) FROM DUAL;--5SELECT '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;--6SELECT COUNT(*), COUNT(1), COUNT(NULL), COUNT(NVL(NULL, 0)) FROM DUAL; --7SELECT COUNT(*), SUM(1), SUM(LEVEL), SUM(NVL(NULL, 0)) FROM DUAL CONNECT BY LEVEL <= 4;--8SELECT * FROM B ORDER BY NAME NULLS FIRST;SELECT * FROM B ORDER BY NAME NULLS LAST;--9SELECT '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;
?
