Oracle å DB2çåºå«å¦ä¸ï¼
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 HH24:MI:SS') from dual;
DB2ï¼select varchar(current timestamp) from sysibm.sysdummy1;
## Oracleæ°æ®ç±»åæ¹åå½æ°ï¼to_char()ãto_date()ãto_number()çï¼
å¦æä»
ä»
åå¹´ï¼æï¼æ¥çï¼å¯ä»¥ç¨to_char(sysdate, 'YYYY'),to_char('MM') ,to_char('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-32672ï¼
æ¥æ,æ¶é´å½¢æå为å符形æ: char(current date)ï¼char(current time)
å°å符串转æ¢ææ¥æææ¶é´å½¢æ:TIMESTAMP('2002-10-2012:00:00'),DATE('2002-10-20'),
DATE('10/20/2002'),TIME('12:00:00')
## ç®åDB2 V8ä¹æ¯æto_charåto_date
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)) FROMsysibm.sysdummy1
8ãå建类似表
Oracleï¼create table a as select * from b ;
DB2ï¼create table a like b ;
CREATE TABLE tab_newAS select col1,col2â¦FROMtab_old DEFINITION ONLY (8çææï¼9çæ æ)
9ãdecodeæ¹æ³
Oracleï¼decodeæ¹æ³ï¼DECODE(æ¡ä»¶,å¼1,ç¿»è¯å¼1,å¼2,ç¿»è¯å¼2,...å¼n,ç¿»è¯å¼n,缺çå¼)ï¼æè
caseè¯å¥
DB2ä¸åªæCASE表达å¼
SELECT id ,name ,
CASE
WHEN integer(flag)=0 THEN âåâ
WHEN integer(flag)=1 THEN âçâ
ELSE âå¼å¸¸â
END
FROM TEST
æè
SELECT id ,name ,
CASE integer(flag)
WHEN 0 THEN âåâ
WHEN 1 THEN âçâ
ELSE âå¼å¸¸â
END
FROM TEST
10ãåæ¥è¯¢ï¼8çï¼9çä¹æ¯æåæ¥è¯¢ï¼
Oracle:ç´æ¥ç¨åæ¥è¯¢
Db2:withè¯å¥
WITH a1 AS
(select max(id) as aa1 from test )
select id ,aa1 from test ,a1
11ãæ°æ®ç±»å
æ¯è¾å¤§çå·®å«ï¼
Oracleï¼char 2000
DB2: char 254
Oracle: date datetime
Db2: DATEï¼æ¥æTIMEï¼æ¶é´TIMESTAMPï¼æ¥ææ¶é´
1ãæ°æ®ç±»å转æ¢å½æ°
æ´å转å符å
å符串转æ´å½¢
å符串转浮ç¹å
æµ®ç¹å转å符串
å符串转æ¥æ
å符串转æ¶é´æ³
æ¥æ转å符串
ORACLE
to_char(1)
to_number('1')
to_number('1.1')
to_char(1.1)
to_date('2007-04-26','yyyy-mm-dd')
to_date('2007-04-26 08:08:08','YYYY-MM-DD HH24:MI:SS')
to_char(to_date('2007-04-29','yyyy-mm-dd'),'yyyy-mm-dd')
DB2
char(1)
int('1')
double('1.1')
char(1.1)
date('2007-04-26')
to_date('2007-04-26 08:08:08','YYYY-MM-DD HH24:MI:SS')
char(date('2007-04-29'))
å
¼å®¹åæ³
cast(1 as char)
cast('1' as int)
æ
æ
æ
å
¼å®¹
æ
2ã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 whre '1'=1 å¨DB2ä¸æ¥é
3ãreplaceå
³é®å
oracleæ¯æï¼DB2ä¸æ¯æ create or replaceè¯å¥å¨DB2ä¸æ¯éæ³ç
4ãåæ¥è¯¢å«å
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
5ãDATEæ°æ®ç±»åçåºå«
ORACLEä¸DATEåä¹æ¯å¸¦ææ¶åç§çï¼ä½DB2ä¸DATEåªæ¯å¹´ææ¥ï¼å¦'2007-04-28'ï¼ä¸å¯ä½ä¸ºå符串ç´æ¥æä½ï¼DB2ä¸è¦è®°å½æ¶åç§å¿
é¡»éç¨TIMESTAMPå
ä¸ä¸ªéç¨hibernateå常è§çå
¼å®¹é®é¢æ¯ï¼
å¦æå¨æ å°æ件ä¸å®ä¹äºæ个å段为Dateå
<property name="createTime" type="java.util.Date" >
<column name="CREATE_TIME" length="7" />
</property>
åå¨DB2ä¸ï¼æ¤å段å¿
é¡»å®ä¹ä¸ºtimestampï¼èä¸è½å®ä¹æDATEï¼ä¸ç¶ä¼æ¥åºå符串å³æªæçé误
对äºDB2æ¥è¯´ï¼å¨æ¥è¯¢æ¡ä»¶ä¸å¯ä»¥ç´æ¥ç¨å符串æå®æ¥æææ¶é´æ³ç±»åå段çå¼ï¼ä¾å¦ where create_date = '2007-04-26' ãwhere create_timestamp = '2007-04-26 08:08:08' ï¼æ 须使ç¨å符串转æ¥æå½æ°
6ãå页çå¤ç
å¦æéç¨JDBCå页çè¯ï¼æ³¨ærownumå¨DB2ä¸ä¸åæ¯æï¼æ¯å¦ä»masa_area表ä¸åå¾area_idæå°ç10æ¡è®°å½ï¼è¯å¥åå«å¦ä¸ï¼æ³¨æè¿éçå«åt书åæ¹æ³
ORACLE: select t.* from (select rownum as r1 ,masa_area.* from masa_area order by area_id) t where t.r1<=10
DB2: select t.* from (select rownumber() over() as r1 ,masa_area.* from masa_area order by area_id) t where t.r1<=10
7ãdecodeå½æ°
decodeå½æ°å¨DB2ä¸è¢«æ¯æï¼å
¼å®¹çåæ³æ¯éç¨case when
8ãNVLå½æ°
nvlåæ³å¨DB2ä¸è¢«æ¯æï¼å
¼å®¹çåæ³æ¯éç¨coalesce
ORACLE: select NVL(f_areaid,'空') from masa_user çåäº select coalesce(f_areaid,'空',f_areaid) from masa_user
DB2: select coalesce(f_areaid,'空',f_areaid) from masa_user
9ãsubstrçä¸å
DB2 substr举ä¾å¦ä¸ï¼
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çcharateräº
温馨提示:答案为网友推荐,仅供参考