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的排序算法不具稳定性。