oracle:
lock t able your_table in share row exclusive mode;
db2 :
lock table your_table in exclusive mode;
-------------------------------------------------- -----------------------------------
Oracle中rownum的 转换 :
Oracle SQL﹕
SQL>select rownum,* from BSEMPMS where rownum >=5 and rownum <=100;
DB2 SQL﹕
select * from (select ROW_NUMBER() over() as a, db2admin.bsempms.* from
db2admin.bsempms) as temp where a>=5 and a<=100 ;
Oracle SQL﹕
SQL>select col1, col2, col3,rownum from your_table where where rownum<3;
DB2 SQL﹕
select col1, col2, col3,
rownumber () OVER (ORDER BY col1 DESC) AS rownum
from your_table
where rownum<3
-------------------------------------------------- -----------------------------------
ORACLE 中的to_date()函数
ORACLE SQL:
to_date('2008-04-16','yyyy-mm-dd')
DB2 SQL::
TO_CHAR(START_DATE,'YYYYMMDD')
substr(CHAR('2008-04-16'),1,4)||substr(CHAR('2008- 04-16'),6,2)||substr(CHAR('2008-04-16'),9,2)
ORACLE SQL:
select m.*
from dj_mcdj m
where m.mcqc || ' ' like '%$P{QYMC}%'
and m.xzqhdm || ' ' like '%$P{XZQH}%'
and m.hylbdm || ' ' like '%$P{HYLB}%'
and m.blqsrq >= to_date('$P{QSRQ}', 'yyyy-mm-dd')
and m.blqsrq < to_date('$P{JZRQ}', 'yyyy-mm-dd')+1
DB2 SQL:
select m.*
from dj_mcdj m
where m.mcqc || ' ' like '%%'
and m.xzqhdm || ' ' like '%%%'
and m.hylbdm || ' ' like '%%%'
and date(m.blqsrq) >= date('1900-01-01')
and date(m.blqsrq) < date('2050-01-01')+1 day
-------------------------------------------------- -----------------------------------
ORACLE 中的nvl函数
ORACLE SQL:
select distinct nvl(
(select max(
to_number(
substr(levelcode,length(levelcode)-4,length(levelc ode))))+1
from client_clientInfo a,client_corporationinfo b
where a.id=b.clientId and a.levelid=1),'10001') levelCode
from client_clientinfo
DB2 SQL:
select distinct coalesce(
(select max(dec(substr(levelcode,length(levelcode)-4,lengt h(levelcode))))+1
from client_clientInfo as a,client_corporationinfo as b
where a.id=b.clientId and a.levelid=1)
,10001)
as levelCode
from client_clientinfo
比较函数nvl(a1,a2)改为coalesce(a1,a2)
转化 数字 函数to_number(string)改为:dec(string)
-------------------------------------------------- -----------------------------------
ORACLE 中的DECODE函数
ORACLE SQL:
DECODE (A.INVOICE_ITEM_ID, -1, '07其他费用', MIN(A.INVOICE_ITEM_NAME))
DB2 SQL:
CASE A.INVOICE_ITEM_ID
WHEN -1 THEN '07其他费用'
ELSE MIN(A.INVOICE_ITEM_NAME)
END
ORACLE SQL:
decode((ss.mbalance - ss.muncheckpaymentamount),0,'0.00',CHAR(ss.mbalanc e - ss.muncheckpaymentamount) )
DB2 SQL:
CASE(ss.mbalance - ss.muncheckpaymentamount)
WHEN 0 THEN '0.00'
ELSE CHAR(ss.mbalance - ss.muncheckpaymentamount)
END
-------------------------------------------------- -----------------------------------
ORACLE 中的lpad函数
ORACLE SQL:
select lpad(ecode,2,'0') ecode from your_table where id=2;
DB2 SQL:
select repeat('0',2-length(rtrim(char(ecode)))) || rtrim(char(ecode)) ecode from your_table where id=2;
ORACLE SQL:
LPAD(slno,20,'0')
DB2 SQL:
repeat('0',20-length(rtrim(char(slno)))) || rtrim(char(slno))
-------------------------------------------------- -----------------------------------
ORACLE 中的序列
ORACLE SQL:
select Seq_a.Nextval from dual
DB2 SQL:
select nextval for Seq_a from dual
-------------------------------------------------- -----------------------------------
ORACLE 中的to_number
ORACLE SQL:
select to_number(S.sname) from your_table
DB2 SQL:
select dec(S.sname) from your_table
-------------------------------------------------- -----------------------------------
ORACLE 中的外连接 SELECT a.lastname, a_id, b.name
FROM emp A, customer B
WHERE A.id(+) = b.sales_rep_id;
SELECT a.lastname, a_id, b.name
FROM emp A
RIGHT OUTER JOIN, customer B
ON A.id = b.sales_rep_id;
SELECT a.lastname, a_id, b.name
FROM emp A, customer B
WHERE A.id = b.sales_rep_id(+);
SELECT a.lastname, a_id, b.name
FROM emp A
LEFT OUTER JOIN, customer B
ON A.id = b.sales_rep_id;
SELECT a.lastname, a_id, b.name
FROM emp A, customer B
WHERE A.id(+) = b.sales_rep_id(+);
SELECT a.lastname, a_id, b.name
FROM emp A
FULL OUTER JOIN, customer B
ON A.id = b.sales_rep_id;
-------------------------------------------------- ----------------------------------- ORACLE 中的round函数
ORACLE SQL:
SELECT round(a.number,2) as rount from your_table
DB2 SQL:
和oracle相同,对于只有一个变量的情况,现在发现的功能是返回比a.number小的最大的整数
ORACLE SQL:SELECT round(a.number) as round from your_table
DB2 SQL:select FLOOR(a.number) as round from your_table
-------------------------------------------------- -----------------------------------
ORACLE 中的to_char(dtExecute,'IW')
ORACLE SQL:
select to_char(dtExecute,'IW') from your_table
DB2 SQL:
select week(dtExecute,'IW') from your_table
功能是返回dtExecute这个时间是第几周。
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;
l 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')。
l 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')
l 目前 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方法
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:日期时间
lock t able your_table in share row exclusive mode;
db2 :
lock table your_table in exclusive mode;
-------------------------------------------------- -----------------------------------
Oracle中rownum的 转换 :
Oracle SQL﹕
SQL>select rownum,* from BSEMPMS where rownum >=5 and rownum <=100;
DB2 SQL﹕
select * from (select ROW_NUMBER() over() as a, db2admin.bsempms.* from
db2admin.bsempms) as temp where a>=5 and a<=100 ;
Oracle SQL﹕
SQL>select col1, col2, col3,rownum from your_table where where rownum<3;
DB2 SQL﹕
select col1, col2, col3,
rownumber () OVER (ORDER BY col1 DESC) AS rownum
from your_table
where rownum<3
-------------------------------------------------- -----------------------------------
ORACLE 中的to_date()函数
ORACLE SQL:
to_date('2008-04-16','yyyy-mm-dd')
DB2 SQL::
TO_CHAR(START_DATE,'YYYYMMDD')
substr(CHAR('2008-04-16'),1,4)||substr(CHAR('2008- 04-16'),6,2)||substr(CHAR('2008-04-16'),9,2)
ORACLE SQL:
select m.*
from dj_mcdj m
where m.mcqc || ' ' like '%$P{QYMC}%'
and m.xzqhdm || ' ' like '%$P{XZQH}%'
and m.hylbdm || ' ' like '%$P{HYLB}%'
and m.blqsrq >= to_date('$P{QSRQ}', 'yyyy-mm-dd')
and m.blqsrq < to_date('$P{JZRQ}', 'yyyy-mm-dd')+1
DB2 SQL:
select m.*
from dj_mcdj m
where m.mcqc || ' ' like '%%'
and m.xzqhdm || ' ' like '%%%'
and m.hylbdm || ' ' like '%%%'
and date(m.blqsrq) >= date('1900-01-01')
and date(m.blqsrq) < date('2050-01-01')+1 day
-------------------------------------------------- -----------------------------------
ORACLE 中的nvl函数
ORACLE SQL:
select distinct nvl(
(select max(
to_number(
substr(levelcode,length(levelcode)-4,length(levelc ode))))+1
from client_clientInfo a,client_corporationinfo b
where a.id=b.clientId and a.levelid=1),'10001') levelCode
from client_clientinfo
DB2 SQL:
select distinct coalesce(
(select max(dec(substr(levelcode,length(levelcode)-4,lengt h(levelcode))))+1
from client_clientInfo as a,client_corporationinfo as b
where a.id=b.clientId and a.levelid=1)
,10001)
as levelCode
from client_clientinfo
比较函数nvl(a1,a2)改为coalesce(a1,a2)
转化 数字 函数to_number(string)改为:dec(string)
-------------------------------------------------- -----------------------------------
ORACLE 中的DECODE函数
ORACLE SQL:
DECODE (A.INVOICE_ITEM_ID, -1, '07其他费用', MIN(A.INVOICE_ITEM_NAME))
DB2 SQL:
CASE A.INVOICE_ITEM_ID
WHEN -1 THEN '07其他费用'
ELSE MIN(A.INVOICE_ITEM_NAME)
END
ORACLE SQL:
decode((ss.mbalance - ss.muncheckpaymentamount),0,'0.00',CHAR(ss.mbalanc e - ss.muncheckpaymentamount) )
DB2 SQL:
CASE(ss.mbalance - ss.muncheckpaymentamount)
WHEN 0 THEN '0.00'
ELSE CHAR(ss.mbalance - ss.muncheckpaymentamount)
END
-------------------------------------------------- -----------------------------------
ORACLE 中的lpad函数
ORACLE SQL:
select lpad(ecode,2,'0') ecode from your_table where id=2;
DB2 SQL:
select repeat('0',2-length(rtrim(char(ecode)))) || rtrim(char(ecode)) ecode from your_table where id=2;
ORACLE SQL:
LPAD(slno,20,'0')
DB2 SQL:
repeat('0',20-length(rtrim(char(slno)))) || rtrim(char(slno))
-------------------------------------------------- -----------------------------------
ORACLE 中的序列
ORACLE SQL:
select Seq_a.Nextval from dual
DB2 SQL:
select nextval for Seq_a from dual
-------------------------------------------------- -----------------------------------
ORACLE 中的to_number
ORACLE SQL:
select to_number(S.sname) from your_table
DB2 SQL:
select dec(S.sname) from your_table
-------------------------------------------------- -----------------------------------
ORACLE 中的外连接 SELECT a.lastname, a_id, b.name
FROM emp A, customer B
WHERE A.id(+) = b.sales_rep_id;
SELECT a.lastname, a_id, b.name
FROM emp A
RIGHT OUTER JOIN, customer B
ON A.id = b.sales_rep_id;
SELECT a.lastname, a_id, b.name
FROM emp A, customer B
WHERE A.id = b.sales_rep_id(+);
SELECT a.lastname, a_id, b.name
FROM emp A
LEFT OUTER JOIN, customer B
ON A.id = b.sales_rep_id;
SELECT a.lastname, a_id, b.name
FROM emp A, customer B
WHERE A.id(+) = b.sales_rep_id(+);
SELECT a.lastname, a_id, b.name
FROM emp A
FULL OUTER JOIN, customer B
ON A.id = b.sales_rep_id;
-------------------------------------------------- ----------------------------------- ORACLE 中的round函数
ORACLE SQL:
SELECT round(a.number,2) as rount from your_table
DB2 SQL:
和oracle相同,对于只有一个变量的情况,现在发现的功能是返回比a.number小的最大的整数
ORACLE SQL:SELECT round(a.number) as round from your_table
DB2 SQL:select FLOOR(a.number) as round from your_table
-------------------------------------------------- -----------------------------------
ORACLE 中的to_char(dtExecute,'IW')
ORACLE SQL:
select to_char(dtExecute,'IW') from your_table
DB2 SQL:
select week(dtExecute,'IW') from your_table
功能是返回dtExecute这个时间是第几周。
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;
l 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')。
l 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')
l 目前 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方法
本文转载于http://blog.sina.com.cn/s/blog_4b7809800100xo59.html
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:日期时间