Oracle NULL 和空值

如果你工作中用到了Oracle，你必须要留意NULL和空值的处理与SQL Server上的不同.现在让我们看些例子。

建立这张数据库表并插入记录

CREATE TABLE TestNull(Col2 VARCHAR(100));
INSERT INTO TestNull VALUES(NULL);
INSERT INTO TestNull VALUES('Bla');
INSERT INTO TestNull VALUES('');
INSERT INTO TestNull VALUES(' ');

如你所见我们插入了4条记录，一条记录是null，一条记录是空值，一条记录是空格，一条记录是"Bla".

让我们执行下面的查询语句：

SELECT Col2,
  NVL(Col2,'EmptyOrNull') a,
  COALESCE(Col2,'EmptyOrNull') b,
  ASCII(col2) c
FROM TestNull;

下面是执行结果以html table显示。

下面是执行结果的截图

 

看看发生了什么，Oracle把空值转变成了 NULL。

我们能很容易的验证这个猜想,建立一个有非NULL约束的单独一列的表。

CREATE TABLE TestNull2(Col2 VARCHAR(100) NOT NULL);

现在当你想插入一个 NULL，它会报错。

INSERT INTO TestNull2 VALUES(NULL);

这是错误信息
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TESTNULL2"."COL2")
01400. 00000 - "cannot insert NULL into (%s)"

插入"Bla"字符串没有任何问题

INSERT INTO TestNull2 VALUES('Bla');

那么插入一个空值, 会报错，会发生什么呢？

INSERT INTO TestNull2 VALUES('');

SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TESTNULL2"."COL2")
01400. 00000 - "cannot insert NULL into (%s)"

如你所见空值被转换成NULL，你得到了和插入空值一样的错误。这和SQL Server很不一样。

插入一个空格会成功么？

INSERT INTO TestNull2 VALUES(' ');

插入空格没有问题。

Coalesce函数的差别

要知道coalesce 函数工作原理不一样. Oracle没有isnull函数但有nvl函数来取代。

运行下面2句sql。

SELECT NVL('','No') AS a FROM dual;
SELECT COALESCE('','No') AS a FROM dual;

在这2种情况下你得到“No”返回值, 如你所见空值被当做null处理.

知道了Oralce和SQL Server的这些差别,如果你认为他们工作原理相同你会得到些奇怪的查询结果。

1. SELECT COUNT(*) FROM DUAL WHERE '' = NULL; -- count(*)=0

2. SELECT COUNT(*) FROM DUAL WHERE '' IS NULL; -- count(*)=1

3. SELECT COUNT(*) FROM DUAL WHERE '' = ''; --count(*)=0