应用从Oracle迁移至SQLServer时SQL转换

一、关于SQL标准和扩展

 

目前主流关系数据库管理系统（包括Oracle和SQLServer）均支持遵守ANSI SQL89标准。

基于通用SQL标准(ANSI SQL89标准)，大部分数据库厂商对SQL语言进行扩展，

具有较大影响力的SQL扩展有Transact-SQL和PL/SQL

 

Transact-SQL（T-SQL）

SQLServer和Sybase Adaptive Server（ASE）系列数据库的SQL语言引擎属于T-SQL；

但自SQLServer version 4.2后，SQLServer和Sybase对T-SQL支持出现分歧，

目前已形成Microsoft T-SQL和 Sybase T-SQL两种标准。

 

PL/SQL（Procedural Language/SQL，过程化SQL语言）

Oracle的SQL语言引擎属于PL/SQL，MySQL目前不支持 PL/SQL

 

关于Transact-SQL语言和PL/SQL语言

同JAVA，C/C++一样，两者属于编程语言；

可在tiobe编程语言排行榜单查询到（Transact-SQL在第10位，PL/SQL在第23位，2014年10月）

 

 

二、应用从Oracle迁移至SQLServer

目前情况是，该应用（某应用）同数据库的所有交互均集中在SQL层面；

因此，应用的数据库迁移的主要工作是将已存在的PL/SQ标准的SQL转换成Microsoft T-SQL标准的SQL；

完成这个转换的前提是要清楚上述两种SQL标准的差异。

 

总结一下，该应用中涉及的两种SQL标准的差异有：

 

特征项              Microsoft T-SQL                         PL/SQL

字符连接            使用加号+作为连接符                 使用||符号作为连接符

外连接                left join和right join                       left join和right join，或者(+)方式                                             

系统时间            getdate()                                       关键字SYSDATE 

时间格式化        convert()结合replace()               TO_CHAR()

根据null取值      isnull()                                          NVL()

根据条件取值     CASE WHEN语句                       CASE WHEN语句，或者DECODE()

自动字符填充     REPLICATE()结合right()或left()  RPAD()或LPAD()

去除前后空字符 ltrim()和rtrim()                               trim()

字符子串             substring()                                     substr()

 

两种SQL标准还有其他很多差异，这里只列出该应用涉及的部分。

 

应用迁移时，sql转换的典型例子

 

1.取系统当前日期时间，并格式化成yyyymmddhh24miss的字符串

 

PL/SQL：TO_CHAR(SYSDATE,'yyyymmddhh24miss')

Microsoft T-SQL：replace(replace(replace(convert(varchar,getdate(),120),'-',''),':',''),' ','')

 

 

2.对于数值类型的SEQ_NO字段，其值不会超过6位数

若SEQ_NO为null，则赋值为0，否则取其值

将SEQ_NO转化为6位数字的字符串，不足6位左边补0

 

PL/SQL：LPAD(NVL(SEQ_NO, 0), 6, '0')

Microsoft T-SQL：right(REPLICATE('0', 5)+CONVERT(VARCHAR, isnull(SEQ_NO, 0)), 6)

 

 

3.对于字符类型的flag字段

若flag='y',则表达式取值为1

若flag='n',则表达式取值为0

其他情况，表达式取值为-1

 

PL/SQL：DECODE(flag, 'y', 1, 'n',0,0) value

Microsoft T-SQL(或PL/SQL)：(CASE WHEN flag='y' THEN 1 WHEN flag='n' THEN 1 ELSE -1 END) value

 

 

该应用中，Microsoft T-SQL和PL/SQL都支持的特征项：

字母小写转换:  LOWER()

字母大写转换:  UPPER()

取最小值:  MIN()

取最大值:  MAX()

is null条件判断:  select * from tableA where cloumnA is null