跨越Oracle和MSSQL关系数据库开发 -- 04 空字符串处理
来源:互联网 发布:linux导入dmp文件命令 编辑:程序博客网 时间:2024/04/30 07:56
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, ‘’) != ’’)
- 跨越Oracle和MSSQL关系数据库开发 -- 04 空字符串处理
- 跨越Oracle和MSSQL关系数据库开发——绪论
- 跨越Oracle和MSSQL关系数据库开发 -- 05 流水号
- 跨越Oracle和MSSQL关系数据库开发 -- 06 临时表
- 跨越Oracle和MSSQL关系数据库开发 -- 03不同数据库的位运算
- 跨越Oracle和MSSQL关系数据库开发 -- 02 Oracle和MSSQL返回结果集区间的方法
- 跨越Oracle和MSSQL关系数据库开发——01开关语句
- 74.Oracle数据库SQL开发之 高级查询——处理空值和缺失值
- oracle null和空字符串
- Oracle 空字符串和NULL
- oracle null 和空字符串
- MsSql 和 Oracle 跨数据库查询
- 空字符串(''),NULL和0的关系
- MSSQL清空数据库日志
- iOS开发- 处理空字符串
- Oracle数据库里面查询字符串类型的字段不为空和为空的SQL语句:
- Oracle数据库里面查询字符串类型的字段不为空和为空的SQL语句:
- oracle 的 null 和 空字符串('')
- 〔转〕ffmpeg命令使用详解
- GridView编辑删除方式2
- 手机AT指令集合
- XML之DTD
- CentOS系统及服务器环境部署
- 跨越Oracle和MSSQL关系数据库开发 -- 04 空字符串处理
- 【转】一个IT老总对新人的建议
- HG522的拨号设置
- C# 处理图片百分比缩放-未测试
- Ubuntu server 安装桌面环境
- POI 读取word (word 2003 和 word 2007)
- jsp 实现播放列表
- Python 处理 XML
- 诺基亚6220C UCWEB 6.7正式版下载,免签名。