如果你工作中用到了Oracle,你必须要留意NULL和空值的处理与SQL Server上的不同.现在让我们看些例子。
建立这张数据库表并插入记录
1 | CREATE TABLE TestNull(Col2 VARCHAR (100)); |
2 | INSERT INTO TestNull VALUES ( NULL ); |
3 | INSERT INTO TestNull VALUES ( 'Bla' ); |
4 | INSERT INTO TestNull VALUES ( '' ); |
5 | INSERT INTO TestNull VALUES ( ' ' ); |
如你所见我们插入了4条记录,一条记录是null,一条记录是空值,一条记录是空格,一条记录是"Bla".
让我们执行下面的查询语句:
1 | SELECT Col2, |
2 | NVL(Col2, 'EmptyOrNull' ) a, |
3 | COALESCE (Col2, 'EmptyOrNull' ) b, |
4 | ASCII(col2) c |
5 | FROM TestNull; |
下面是执行结果的截图
看看发生了什么,Oracle把空值转变成了 NULL。
我们能很容易的验证这个猜想,建立一个有非NULL约束的单独一列的表。
1 | CREATE TABLE TestNull2(Col2 VARCHAR (100) NOT NULL ); |
1 | INSERT INTO TestNull2 VALUES ( NULL ); |
这是错误信息
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TESTNULL2"."COL2")
01400. 00000 - "cannot insert NULL into (%s)"
插入"Bla"字符串没有任何问题
1 | INSERT INTO TestNull2 VALUES ( 'Bla' ); |
1 | 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很不一样。
插入一个空格会成功么?
1 | INSERT INTO TestNull2 VALUES ( ' ' ); |
插入空格没有问题。
Coalesce函数的差别
要知道coalesce 函数工作原理不一样. Oracle没有isnull函数但有nvl函数来取代。
运行下面2句sql。
1 | SELECT NVL( '' , 'No' ) AS a FROM dual; |
2 | SELECT COALESCE ( '' , 'No' ) AS a FROM dual; |
在这2种情况下你得到“No”返回值, 如你所见空值被当做null处理.
知道了Oralce和SQL Server的这些差别,如果你认为他们工作原理相同你会得到些奇怪的查询结果。