目录
1. 符号使用
- 1.1 :->@
- 1.2 mod()->%
- 1.3 ||->+
1.4 off 等表别名
- 1.5 columnnum=1->top 1
- 1.6 minus->except
- 1.7 number->decimal
- 1.8 date -> datetime
2. 函数转换
- 2.1 nvl->isnull
- 2.2 substr->substring
- 2.3 decode->case when end
2.4 数据类型转换
2.5 sq_executesql 第一个参数必须是 nvarchar 类型
2.6 instr -> charindex
2.7 分组并合并列
3. 语法规则
3.1 子查询别名
3.2 sql server 树形查询
3.3 sql server 使用 merge info
3.4 inserted 表与 deleted 表
3.5 insert\update\delete 中使用 output
3.6 sql 语句或存储过程数据库执行很快, 程序中执行很慢
3.6.1 程序传递参数类型与数据库不一致
3.6.2 存储过程执行计划过期
最近项目升级, 需要把原来的 oracle 版本改为 sql server 版本. 由于项目的分层设计, 主要的修改内容也就是存储过程, sql 语句. 如今改的七七八八, 整理一下踩过的坑, 备忘!
1. 符号使用
1.1 :->@
带参数的 sql 语句, oracle 的参数标识使用前缀 ":",sql server 前缀 "@".
oracle 版
SELECT * FROM table1 WHERE column1=:column1
sql server 版
SELECT * FROM table1 WHERE column1=@column1
带参数的 sql 语句, 在代码中添加参数时, oracle 同一个参数可以添加多次, sql server 添加多次会报异常.
oracle 版本
- ...
- db.AddInParameter(cmd, "column1", DbType.String,column1);
- db.AddInParameter(cmd, "column2", DbType.String,column2);
- ...
- db.AddInParameter(cmd, "column1", DbType.String,column1);
- ...
oracle 可以像上面这样, 再次添加同名的参数, 但是连接 sql server 数据库时不允许.
1.2 mod()->%
求余运算
1.3 ||->+
字符串连接符
oracle 的 || 专门用来做字符串拼接, 因此类似下面这样的字符串和数字的拼接是可以成功的:
select 'abc'||123 from dual
但是在 sql server 中类似下面的字符串和数字拼接是无法直接进行的:
select 'abc'+123
sql server 倾向于将字符串转为数字, 因此报异常. 如果要实现字符串拼接的效果, 需要做数据类型转换
select 'abc'+convert(varchar(10),123)
这种情况在存储过程等复杂的操作中, 做动态 sql 拼接时, 最容易忽略.
1.4 off 等表别名
oracle 中的表别名 off 在 sql server 中是关键字, 导致 sql 语句错误. 因此, 在使用表别名的时候我们还是尽量避免掉这种类似关键字的命名.
1.5 columnnum=1->top 1
oracle 在排序时, 可以使用 columnnum=1 获取第一条记录, sql server 排序时可以使用 top 1 来获取.
1.6 minus->except
oracle 的 minus 对应 sql server 的 except, 直接替换
1.7 number->decimal
oracle 中的 number 类型, 即使设置了小数位数, 比如 decimal(18,2), 但是数据库不会对插入的数值补零, 插入整数就是整数. 但是 sql server 中会根据设置的数据类型, 在数字末尾补零以补足小数位. 这个差异会影响前端数字的格式化展示.
1.8 date -> datetime
oracle 中 date 数据类型包括日期和时间两部分.
sql server 中 date 只包括日期部分, datetime 包括日期和时间两部分, timestamp 递增数字, 与时间无关.
oracle 中 timestamp 也包括日期和时间两部分, 与 date 的区别主要是相减时, timestamp 相减结果单位是秒, date 相减结果是天. 两个类型可以相互转换.
C# 中向 sql server 中插入日期类型时, C# 默认日期是 01/01/01 00:00:00, 直接插入数据库会导致数据一出, 因为 C# 向 sql server 中插入 datetime 数值类型时 (DbType.SqlDateTime), 有效日期范围为 1/1/1753 12:00:00 ~ 12/31/9999 11:59:59 PM. 数据库直接导入日期字段, 或者直接修改数据库时, 是可以设置为 1/1/1 00:00:00 的.
2. 函数转换
2.1 nvl->isnull
这两个函数完全等价, 可以直接替换使用.
2.2 substr->substring
substr(str,startIndex,[length]) oracle 中 length 参数可以省略, 默认取到结尾;
substring(str,startIndex,length) sql server 中 length 参数不能省略.
因此做替换时, 要根据业务确定截取长度.
2.3 decode->case when end
oracle 中的 decode 函数使用 case when end 语句替换, 对于嵌套的 decode 函数我们可以通过组合 when 的逻辑表达式实现.
oracle 版
- select decode(column1,0,'状态 1',1,'状态 2','其他') from table1;
- select decode(column1,0,decode(column2,0,'状态 1','状态 2'),'其他') from table1;
sql server 版
- select case column1
- when 0 then '状态 1'
- when 1 then '状态 2'
- else '其他' end
- from table1;
- select case
- when column1 = 0 and column2 = 0 then '状态 1'
- when column1 = 0 then '状态 2'
- else '其他' end
- from table1;
2.4 数据类型转换
- to_number(str)->convert(int,str)
- to_char(str)->convert(nvarchar(n),str)
- to_char(date,'yyyy-MM-dd')->convert(varchar(100),date,23)
- to_char(date,'yyyy-MM-dd hh24@mi@ss')->convert(varchar(100),date,20)
- to_char(date,'yyyyMMdd')->convert(varchar(100),date,112)
2.5 sq_executesql 第一个参数必须是 nvarchar 类型
2.6 instr -> charindex
两个函数的实现功能一样, 但是参数顺序却不同, 一定要注意.
oracle 版本
select instr('abcde','c') from dual
sql server 版本
select charindex('c','abcde')
2.7 分组并合并列
原始数据结构
学号 | 名字 | 爱好
---|---|---
201012 | 张三 | 篮球
201012 | 张三 | 乒乓球
201013 | 李四 | 唱歌
201012 | 张三 | 羽毛球
201013 | 李四 | 羽毛球
201013 | 李四 | 绘画
目标视图
学号 | 名字 | 爱好
---|---|---
201012 | 张三 | 篮球, 乒乓球, 羽毛球
201013 | 李四 | 唱歌, 羽毛球, 绘画
oracle 11g
select 学号, 名字, listagg(爱好,',') within group(order by 爱好) from table1 group by 学号, 名字
oracle 其他版本
select 学号, 名字, wm_concat(爱好) from table1 group by 学号, 名字
sql server
select 学号, 名字,
stuff((select ','+ 爱好 from table1 for xml path('')),1,1,'') 爱好
from table1
group by 学号, 名字
3. 语法规则
3.1 子查询别名
oracle 中子查询作为 from 中查询目标可以不使用别名, 但是 sql server 中必须命名别名.
oracle 版本
- select column1,column2,column3,column4
- from (select column1,column2,column3,column4 from table1);
sql server 版本
- select column1,column2,column3,column4
- from (select column1,column2,column3,column4 from table1) a;
3.2 sql server 树形查询
with 定义的 sql 片段作为查询对象, 实现树形查询.
- with cte(id,parentId,column1,column2,column3) as
- (
- select column1,column2,column3 from table1
-- 这里可以接收查询参数
- --where column1 = ''
- union all
- select column1,column2,column3 from table1 inner join cte t1
on t1.id = t2.parentId -- 自下而上查询
--on t1.parentId = t2.id -- 自上而下查询
- )
- select * from cte;
3.3 sql server 使用 merge info
项目中作为更新或插入场景使用.
- merge into table1 t1
- using (select @id id,@para1 column1,@para2 column2,@para3 column3) t2
- on t1.id = t2.id
- when matched then
- update set t1.column1 = t2.column1,t1.column2 = t2.column2,t1.column3 = t2.column3
- when not matched then
- insert(id,column1,column2,column3) values(t2.column1,t2.column2,t2.column3)
其中 oracle 在一些没有 from 目标的操作时, 使用 dual 作为操作目标. sql server 在这种情况下, 直接不写 from 部分即可.
3.4 inserted 表与 deleted 表
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
deleted 表存储受 Delete 和 Update 语句操作影响的行的副本. 执行 delete 或 update 语句期间, 受影响的行从触发器所在表转移到 deleted 表. deleted 表和触发器所在表通常没有共同的行.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
inserted 表存储受 insert 和 update 语句操作影响的行的副本. 执行 insert 或 update 事务时, 新行被添加到 inserted 表和触发器所在表. inserted 表中的行是触发器所在表新行的副本.
An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
一个 update 事务类似于一个 delete 操作后跟一个 insert 操作. 旧行先拷贝到 deleted 表, 然后新行拷贝到触发器所在表和 inserted 表.
Use the inserted and deleted Tables
3.5 insert\update\delete 中使用 output
利用 inserted 表和 deleted 表, 输出插入后自动生成的 id 等字段, 避免 insert 后, 在执行 select 查询的繁琐.
- insert into table1(column1,column2,column3)
- output inserted.id,inserted.time into @para1,@para2
- values(@column1,@column2,@column3)
3.6 sql 语句或存储过程数据库执行很快, 程序中执行很慢
3.6.1 程序传递参数类型与数据库不一致
- ...
- db.AddInParameter(cmd, "column1", DbType.Decimal,column1);
- db.AddInParameter(cmd, "column2", DbType.String,column2);
- db.AddInParameter(cmd, "column3", DbType.String,column3);
- ...
这里的 DbType 指定的类型一定要与数据库的数据类型保持一致, 否则在执行大量数据查询时, 会存在数据类型转换, 严重影响 sql 语句执行效率. 其中比较以忽略的类型
- varchar -> DbType.AnsiString
- nvarchar -> DbType.String
这一对是比较容易不一致的, 我们一般给 varchar 字段和 nvarchar 字段都传递的 DbType.String. 这样在数据量小时没有问题, 数据量大时会非常慢.
3.6.2 存储过程执行计划过期
由于存储过程是预编译的, 在第一次执行的时候, 会生成执行计划, 以后执行的时候, 会使用这个执行计划 (除非显示指定重新编译), 而不是每次执行时都生成执行计划. 当存储过程涉及的对象结构调整, 或者相关的数据产生了很大的变化, 这可能导致原来的计划不适合当前的现状 (执行计划过期), 这种情况下应该重新编译存储过程.
exec sp_recompile @objname='存储过程名'
来源: https://www.cnblogs.com/zhangdk/p/oracle_sqlserver.html