oracle与db2的数据库sql语句转换

-------------------------------------------------------------------------------------

表锁模式：
oracle：
lock table your_table in share row exclusive mode;
db2：
lock table your_table in exclusive mode;

-------------------------------------------------------------------------------------

Oracle中rownum的转换：

Oracle SQLs
      SQL>select rownum,* from BSEMPMS where rownum >=5 and rownum <=100;
DB2 SQLs
      select * from (select ROW_NUMBER() over() as a, db2admin.bsempms.* from
      db2admin.bsempms) as temp where a>=5 and a<=100 ;

Oracle SQLs
      SQL>select col1, col2, col3,rownum from your_table where where rownum<3;
DB2 SQLs
      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(levelcode))))+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,length(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.mbalance - 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这个时间是第几周。