sqlserver数据库迁移至oracle数据库

1、表名或列名过长：
在oracle中长度不能超过30。

2、自增长需要使用序列，修改映射文件中id增长：
为了规范，我们这里统一在表名后面加’_seq’，当然，表名的长度没有超过26。关于创建序列，我们是这样处理的：
首先将表名及表中id的最大值放在临时表中（这里是tempwcj），执行select语句，将结果执行。
create table tempwcj
(
TABLENAME varchar2(30) not null,
MAXID number
);
select ‘insert into tempwcj select ’ || ”” || table_name || ”” ||
’ ,(case when max(id) is null then 1 else max(id)+1 end) maxid from ’ ||
table_name || ‘;’
from user_tab_columns
where column_name = ‘ID’;
然后根据临时表创建序列：

select ‘create sequence ’ || tablename ||
‘_SEQ minvalue 1 maxvalue 999999999999999999999999 start with ‘||maxid||’ increment by 1 cache 10;’
from tempwcj;

3、注意视图、函数、存储过程等等的迁移，实现方法会有不同：
这里不用多说，需要的东西肯定都要迁移，只是迁移过来的东西很多都需要修改。比如一个表值函数，在sqlserver中是这样的
复制代码
ALTER FUNCTION [dbo].[getDateList] ( @StartTime datetime, @EndTime datetime)
RETURNS @retable TABLE(dDate varchar(10))
AS
BEGIN
while @StartTime <= @EndTime
begin
insert into @retable select convert(varchar(10),@StartTime,23)
set @StartTime=Dateadd(day,1,@StartTime)
end
return
end
复制代码
而在oracle中实现，你需要做的更多

复制代码
create or replace type getdatelist_Retable as object(
dDate varchar2(120)
);
———————–使用嵌套表——————————-

create or replace type getdatelist_Retable_table as table of getdatelist_Retable;

CREATE OR REPLACE Function getDateList
(
StartTime In VARCHAR2,
EndTime In VARCHAR2
)
RETURN getdatelist_Retable_table
AS
retable getdatelist_Retable_table := getdatelist_Retable_table();
BEGIN
FOR i IN 1 .. (to_date(EndTime,’yyyy-mm-dd’)-to_date(StartTime,’yyyy-mm-dd’)+1) LOOP
BEGIN
retable.EXTEND;
retable(i) := getdatelist_Retable(to_char(to_date(StartTime,’yyyy-mm-dd’)+i-1,’yyyy-MM-dd’));
END;
END LOOP;
RETURN retable;
END;
复制代码

4、字段不能使用[]中括号：
有些字段在sqlserver中是关键字，在sql语句中需要使用[]，不只是为什么有些很普通的字段也加了[]，但是不管怎样在oracle中必须去掉。
select * from uu_user_function uuf where uuf.[function] = 12;

5、case 条件 when ‘字符串’ then ….,如果条件为字段，需是varchar2类型：
这一点在代码中很难看的出来，在数据库迁移时，注意别搞错了字段类型。
select ht.itcode,(case ht.accyear when ‘2013’ then ‘当前年份’ else ‘其他年份’ end) as accyear from HR_TOTALASSESS ht;
如果accyear为NVARCHAR2类型，执行sql时会提示“字符集不匹配”。

6、大数据使用clob类型时，查询时使用to_char(clob)即可：
在oracle中，varchar2的最大长度为4000，从oracle8开始，建议使用CLOB类型存储大批量字符，建议使用BLOB类型存储大批量二进制数据。
当然用select 查询时，返回的是定位符，如果需要显示数据，可以使用to_char(clob类型字段)。
复制代码
select t.skillname,to_char(t.skilldesc) from skill t;
–下面奉上更改列类型的sql，我这里直接更改为clob时提示“数据类型的更改无效”–
————–创建临时列———————-
alter table SKILL add TEMPCOL clob;
——将需要更改类型的列更新到临时列———-
update skill set TEMPCOL = SKILLDESC ;
———–删除需要更改类型的列—————
alter table SKILL drop column SKILLDESC;
————–重命名临时列——————–
alter table SKILL rename column TEMPCOL to SKILLDESC;
复制代码
7、部分函数在oracle中不存在，或使用方法不同：
　　比较常见的是这些函数top、convert、getdate()

————top返回确定数目的数据（例如前10条记录）————–
select top 10 * from uu_user_function uuf where uuf.[function] = 12;
————下面显示的是总数目10%条的记录———————-
select top 10 percent * from uu_user_function uuf where uuf.[function] = 12;
top函数在oracle中不存在，如果返回确定数目的时候，可以使用rownum替代。

convert()函数用于处理日期使用不同的格式显示

——在sqlserver中——
SELECT CONVERT(varchar,GETDATE(),120);
——在oracle中———
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;

8、表别名 不使用as，列别名，as可有可无；使用别名时注意不要使用单引号：
在sqlserver中表和列使用别名时 as 可有可无，但在oracle中，表使用表名时，不能使用as；

别名可以使用双引号或者不使用引号，但是如果别名是多个字符串，必须使用双引号。

select s.id skillid,
s.skillname “技能名称”,
to_char(s.skilldesc) as “skill desc”,
s.skilltype 技能类型
from skill /as / s;

9、如果选择列有时间字段需要显示时分秒，需要to_char：
这个本不是问题，如果原来使用的是convert时间显示是’2012-12-31 23:59:59’，目前oracle数据库中该字段存储的数据就是这样的格式

    sql.append("select bdr.id,to_char(bdr.createDate,'yyyy-MM-dd') as createDate, ");
    sql.append("to_char(bdr.createTime,'yyyy-MM-dd hh24:mi:ss') as createTime,to_char(bdr.planOfDay),to_char(bdr.nodule), ");

代码中的createDate和createTime在oracle中的格式都是date，包含年月日时分秒。

Query query = getSession().createSQLQuery(sql);
//如果去掉上面代码中bdr.createDate和bdr.createTime的to_char()，在这里得到的list里面的时间格式就已经没有了时分秒

10、oracle中区分大小写，新增或查询数据时需做相应调整：
关于oralce区分大小写，应该大家都很清楚。sql关键字不区分大小写，对象名和列名不区分大小写，但是字符值和日期值区分大小写：

—-user_tables 显示当前用户所有表 的视图—-
select * from user_tables ut where ut.tablespace_name = upper(‘users’);
特别是检索信息的时候，如果输入的字符串未做处理，而且数据库中刚好存储的是大写，这样就会导致检索不出想要的数据。当然你可以在前台控制输入大小写，或者后台toLowerCase()

或toUpperCase()，或者在sql中upper()或lower()，或者更改数据库数据。也许你会有更好的办法。

11、如需修改表名，请确认是否有其它系统使用此表，并使用旧表名新建新表名的同义词：
这里有一个教训，迁移数据库后，更改了两个表名，当然忘记考虑了一下，是不是有其他系统使用这两个表，结果导致了另外一个项目的一个模块几近瘫痪。还好当时立刻新建了两个同义词，算是搞定了。其实后来才知道，在数据库中，更改表名等同于删除表，很严重的。

12、oralce的分页sql，如果排序字段不唯一，导致分页数据重复：
这种情况用户一般从前台使用时，很难发现，因为只有在所有的排序字段都不能唯一确定数据的时候，才会出现。

没有什么好办法，在排序字段里面增加一个唯一数据的字段就可以了，比如主键，因为oracle的排序算法不具稳定性。