跨越Oracle和MSSQL关系数据库开发 -- 04 空字符串处理

1. 概述

经历过不同数据库开发的人经常会对莫名其妙的空串处理逻辑搞的晕头转向。

本人在此对Oracle，MSSQL，Sybase三种不同数据库的空字符串处理逻辑作一点分析。

本文假设读者正在使用PL/SQL或者T-SQL。

 

2. 三种数据库中的不同行为

2.1 ORACLE——PL/SQL

 

在PL/SQL中，空串与NULL被视为等价值。

 

以下是测试例子：

 

CREATE TABLE test_empty(name VARCHAR2(10) NOT NULL);

INSERT INTO test_empty VALUES('');

 

ORA-01400: cannot insert NULL into ("MY_DB"."TEST_EMPTY"."NAME")

 

因此，在PL/SQL中语句

 

IF v_name = '' THEN

 

实际上错误的。因为众所周知，判断是否为NULL应该用IS NULL。

 

那么，ORACLE中空串的长度是什么呢？

 

执行以下语句你将得到答案：

 

SELECT LENGTH('') from DUAL;

 

结果是0吗？不，是空值NULL！

 

这说明在Oracle不存在长度为0的字符串！

 

让我们来验证一下LENGTH(‘’)是否为空值。运行以下语句：

 

select nvl(length(''), 'null') from dual;

 

该语句中nvl系统函数判断前一个值是否为空，如果空的话就用第二个参数返回。

但是运行的结果却令人失望：

ORA-01722: 无效数字

这到底是怎么回事呢？

不要着急，试运行下面这个语句：

select nvl(length(''), '0') from dual;

结果就出来了，呵呵。

看起来只不过是将字符串’0’取代了’null’，但是一个语句成功，一个语句失败，奥秘在哪里？

秘密就在NULL值本身。NULL值是区分数据类型的。Length函数返回的就是一个数字型的NULL，第二个参数是’null’时，无法隐式转换成数字，因此就报错了。事实上，正确的语句应该是：

select nvl(length(''), 0) from dual;

2.2 MSSQL——T-SQL

 

MSSQL真正将空串视为一个有意义的标识，即不是一个空值。

 

你可以顺利将空串插入一个NOT NULL的列中：

 

CREATE TABLE test_empty(name varchar(10) NOT NULL)

go

INSERT INTO test_empty VALUES('')

go

 

MSSQL将空串视作一个长度为0的字符串，与其他字符串没有本质区别。执行

 

SELECT LEN('')

 

其结果为0。

 

既然空串不是NULL，那么自然可以直接用=号进行判断。

同Oracle的空值一样，MSSQL的NULL也是区分数据类型的。例如下面的语句就无法运行：

select isnull(len(null), 'null')

 

 

2.3 Sybase

 

从直觉上讲，MSSQL脱胎于Sybase，因为两者的空串处理逻辑应该一样。但是实际并非如此。

 

与Oracle一样，Sybase中同样不存在长度为0的字符串，但是与Oracle不同的是，Sybase并不将空串视作NULL，而是视作与只包含一个空格的字符串等价。

 

因此，执行

 

SELECT LEN('')

 

其结果居然是1！

 

这个逻辑引起的最大麻烦是在字符串相加时，引起头或尾部不必要的空格，导致相关逻辑失败！

 

3. 迁移数据库时的注意点

3.1 表结构

将数据库从MSSQL或者Sybase迁移到Oracle时，如果列的类型为字符串型且被定义为not null，那么就可能引起错误。

如果MSSQL/Sybase的列中含有空串，那么数据不能直接传输，而是要首先进行变换。这就是所谓的“默认空串规则”。

解决的方案一是改变not null的限制为nullable，二是将空串转变为一个特定的串，此时，要求应用程序代码能够辨认该特定串。

3.2 数据操作代码

3.2.1 陷阱一

考虑以下T-SQL代码：

if trim(@name) = ‘’

begin

  …

end

如果转换成PL/SQL代码，不能写成：

IF ltrim(v_name) = ‘’ THEN

  …

END IF;

这是因为如果v_name是由空格组成，使用ltrim()或者rtrim()后的结果是null，因此必须使用is null来判断：

IF ltrim(v_name) is null THEN

  …

END IF;

3.2.2 陷阱二

 

考虑以MSSQL代码：

select @aStr=’’

select @aStr2 = ‘abc’ + @aStr + ‘123’

if @aStr = ‘abc123’

begin

  …

end

这段代码在MSSQL中运行没有问题，但是在Sybase中运行却不对，因为此时的@aStr的值为’abc 123’。有趣的是，在Oracle中，相应代码却完全正确，因为任何字符串加上NULL后不变。

3.2.2 陷阱三

本文所述三种数据库对空字符串处理的不同还包括字符串函数的不同上。

下面这段在MSSQL中正常运行代码在Sybase中将引起死循环：

select @testCode=’a|b|c|’

while @testCode != ‘’

begin

  select @i=charindex(‘|’, @testCode)

  select @testCode=substring(@testCode, @i+1, len(@test_code)-@i)

end

读者朋友，请问你能看出其中的问题吗？

问题的原因在于substring函数。

在MSSQL中，substring(‘123’, 4, 1)返回’’，而在Sybase中却返回NULL。在Oracle中substr(‘123’, 4, 1)返回NULL。

为了避免死循环，上述循环判断语句可以简单的修改为：

while(isnull(@testCode, ‘’) != ’’)