DB2与Oracle命令区别
1、取前N条记录
Oracle:select * from tablename where rownum <= n;
DB2:select * from tablename fetch first n rows only;
2、取得系统日期
Oracle: select sysdate from dual;
DB2: select current timestamp from sysibm.sysdummy1;
3、空值转换
Oracle: select productid,loginname,nvl(cur_rate,'0') from tablename;
DB2: select productid,loginname,value(cur_rate,'0') from tablename;
coalesce(cur_rate,'0')
4、类型转换(8版有了to_char,to_date,9版新增了to_number)
Oracle: select to_char(sysdate,'YYYY-MM-DD') from dual;
DB2: select varchar(current timestamp) from sysibm,sysdummy1;
Oracle数据类型改变函数:to_char()、to_date()、to_number()等
如果仅仅取年、月、日等,可以用to_char(sysdate,'YYYY'),to_char(sysdate,'MM'),to_char(sysdate,'DD')
只取年月日 trunc(sysdate) 取时分秒 to_char(sysdate,'HH24:MI:SS')
DB2数据类型改变函数:char()、varchar()、int()、date()、time()等
取得年、月、日等写法:year(current timestamp),month(current timestamp),day(current timestamp),
hour(current timestamp),minute(current timestamp),second(current timestamp),microsecond(current timestamp)
只取年月日可以用date(current timestamp),取时分秒time(current timestamp)
char() 是定长字符串1-255 varchar()是非定常字符串1-36272
日期、时间形态变为字符形态:char(current date),char(current time)
5、快速清空大表
Oracle: truncate table tablename;
DB2: alter table tablename active not logged initially with empty table;
6、ROWID
Oracle:它是由数据库唯一产生的,在程序里可以获得
DB2: V8也有此功能
7、to_number
Oracle: select to_number('123') from dual;
DB2: select cast('123' as integer) from sysibm,sysdummy1;
select cast(current time as char(8)) from sysibm,sysdummy1;
8、创建类似表
Oracle: create table a as select * from b;
DB2: create table a like b;
create table tab_new as select col1,col2,…… from tab_old definition only;(8版有效,9版无效)
9、decode方法
Oracle:decode(条件,值1,翻译值1,值2,翻译值2,…… 值n,翻译值n,缺省值)
或case语句
DB2:只有case语句
select id,name,
case
end
from test
或者
select id,name,
case
end
from test
10、子查询
Oracle: 直接使用子查询
DB2:With语句
with a1 as
(select max(id) as aa1 from test)
select id,aa1 from test,al
11、数据类型
比较大的差别
Oracle: char 2000
DB2:char 254
12、数据类型转换函数
整型转字符型
字符串转整型
字符串转浮点型
浮点型转字符串
字符串转日期
字符串转时间戳
日期转字符串
Oracle
to_char(1)
to_number('1')
to_number('1.1')
to_char(1.1)
to_date('2011-04-20','yyyy-mm-dd')
to_date('2011-04-20 19:28:30','YYYY-MM-DD HH24:MI:SS')
to_char(to_date('2011-04-20','yyyy-mm-dd'),'yyyy-mm-dd')
DB2
char(1)
int('1')
double('1.1')
char(1.1)
date('2011-04-20')
to_date('2011-04-20 19:28:30','YYYY-MM-DD HH24:MI:SS')
char(date('2011-04-20'))
兼容写法
cast(1 as char)
cast('1' as int)
无
无
无
兼容
无
13、where条件弱类型判断
Oracle: where 字符型字段 in(整型) 是允许
DB2: 不允许
select 'abc' from dual where '1' in (1); 在Oracle下可通过
select 'abc' from sysibm.sysdummy1 where '1' in (1);在DB2下报错
Oracle:where 字符型字段 = 数字型字段 允许
DB2: 不允许
select 'abc' from dual where '1' = 1; 在Oracle下可通过
select 'abc' from sysibm.sysdummy1 where '1' = 1;在DB2下报错
14、replace关键字
Oracle支持,DB2不支持,create or replace 语句在DB2中非法
15、子查询别名
Oracle:select * from (select 1 from dual);或select * from (select 1 from dual) t;
DB2: select * from (select 1 from sysibm.sysdummy1) t 或 select * from (select 1 from sysibm.sysdummy1) as t;
故兼容的写法
select * from (子查询) t;
16、date数据类型的区别
Oracle中date类型是带有时分秒的,但DB2中date只是年月日,如'2011-04-20',且可作为字符串直接操作,DB2中要
记录时分秒必须采用timestamp型
17、substr不同
DB2:
masa_group表的f_groupcode字段定义成varchar(100),所以下面这个语句不会出错,如果是
是substr(f_groupcode,1,101)就报错了
select * from masa_group where substr(f_groupcode,1,50) = '001006' order by f_groupcode;
在DB2下无错,但是
select * from masa_group where substr('001006',1,50) = '001006' order by f_groupcode;
就报错,说第三个参数超限,这是因为‘001006’已经定义成一个长度为6的character了。
这点和Oracle有很大不同,请大家注意
如果是要从第一位取到最后一位,稳妥的办法是不要加第三个参数
Oracle: select substr('123456',1) from dual;
DB2: select substr('123456',1) from sysibm.sysdummy1;
转自:http://blog.sina.com.cn/s/blog_683b7a8401014djm.html