程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> Oracle SQL到DB2 SQL的移植

Oracle SQL到DB2 SQL的移植

編輯：DB2教程

Oracle SQL到DB2 SQL的移植：

1、Oracel中的"decode"

DB2的解決方案：用"case"條件表達式來完成。

"case"兩種語法的模式：

(1)CASE

WHEN 條件 THEN 結果1 

ELSE 結果2 

END

(2)CASE 表達式1

WHEN 表達式2 THEN 結果1 

ELSE 結果2 

END

上面的WHEN可以重復多次，就像C中的SWITCH ..CASE的表達.

例如：

SELECT ORDNO,CUSNO, 

CASE MONTH(SHIPDATE) 

WHEN ''01'' THEN ''Jan'' 

WHEN ''02'' THEN ''Feb'' 

WHEN ''03'' THEN ''Mar'' 

WHEN ''04'' THEN ''Apr'' 

WHEN ''05'' THEN ''May'' 

WHEN ''06'' THEN ''Jun'' 

WHEN ''07'' THEN ''Jul'' 

WHEN ''08'' THEN ''Aug'' 

WHEN ''09'' THEN ''Sep'' 

WHEN ''10'' THEN ''Oct'' 

WHEN ''11'' THEN ''Nov'' 

WHEN ''12'' THEN ''Dec'' 

END 

FROM FILE 

應用實例： 

Oracle SQL： 

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

select decode(t.organtypecode, ''D'', t.parent, ''S'', t.parent, t.id) 

from A_ORGAN t 

where t.parent = 35 

DB2 SQL： 

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

select case x.organtypecode 

when ''D'' then 

x.parent 

when ''S'' then 

x.parent 

else 

x.id 

end 

from a_Organ x 

where x.parent = 35;

2、Oracle中的Start with...Connect By遞歸查詢

DB2解決方案：用with公共遞歸表達式來解決。

DB2解決方案：用case條件表達式完成。

Oracle SQL： 

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

select t.id 

from a_organ t 

start with t.id in (select decode(t.organtypecode, 

''D'', 

t.parent, 

''S'', 

t.parent, 

t.id) 

from A_ORGAN 

where t.id = 35) 

connect by t.parent = prior t.id 

DB2 SQL： 

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

WITH FKK(id) as 

(select o.id from a_organ o 

where o.id=35 

UNION ALL 

select case x.organtypecode 

when ''D'' then x.parent 

when ''S'' then x.parent 

else x.id 

end 

from FKK fk, a_organ x 

where fk.id=x.parent) 

select distinct id from FKK;

3、Oracle中的dual表對應DB2中的SYSIBM.SYSDUMMY1表

DB2解決方案：對應於DB2中的 SYSIBM.SYSDUMMY1表

Oracle SQL： 

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

select 15 as ttt from dual 

結果： 

ttt 

------- 

15 



DB2 SQL： 

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

select 15 as ttt from SYSIBM.SYSDUMMY1 

結果： 

ttt 

------- 

15

4、日期轉換問題

DB2解決方案：有相應的函數

Oracle SQL： 

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

select m.* 

from dj_mcdj m 

where m.mcqc || '' '' like ''% $P%'' 

and m.xzqhdm || '' '' like ''% $P%'' 

and m.hylbdm || '' '' like ''% $P%'' 

and m.blqsrq >= to_date('' $P'', ''yyyy-mm-dd'') 

and m.blqsrq < to_date('' $P'', ''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

5、nvl 問題

DB2解決方案：利用coalesce(,) 或 value(,)方法。

oracle中的nvl對應db2中的value ，只是Oracle中的語法更有寬松一些，在db2中，value要求兩個參數必須是同一種類型的，nvl要求則不是很嚴格，nvl(A,‘’)，如果A是數字類型或者日期類型的這個表達式也沒有問題，但是在db2中，若是也這麼寫的話， value(A,''),那肯定就有問題了，總的來說，基本上是一致的。

6、左右外連接問題

db2的左右外連接的語法和標准sql語法一樣，只是沒有Oracle中的(+)這個簡單符號來標記左右外連接，left (right) outer join on

(1).內連接INNER JOIN的Oracle和DB2的寫法

Oracle可以這樣實現? Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no; DB2 可以這樣實現? Select * from db2admin.bsempms inner join db2admin.bsdptms on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

(2).外連接的Oracle和DB2的寫法(右外連接,左外連接,完全外連接,組合外連接)

Oracle可以這樣實現:

Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+); 
Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;

DB2 可以這樣實現:

Select * from db2admin.bsempms right outer join db2admin.bsdptms 
on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no; 

Select * from db2admin.bsempms left outer join db2admin.bsdptms 
on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no; 

Select * from db2admin.bsempms full outer join db2admin.bsdptms 
on db2admin.bsempms.dpt_no=db2admin.bsdptms.dpt_no;

7、LIKE問題

db2中謂詞LIKE後邊的表達式不支持字段。只支持一下類型：

A constant 
A special register 
A host variable 
A scalar function whose Operands are any of the above 
An expression concatenating any of the above 

（附DB2文檔： 
使用格式： match-expression LIKE pattern-expression 
match-expression 
An expression that specifies the string that 
is to be examined to see if it conforms to 
a certain pattern of characters. 
The expression can be specified by: 

A constant 
A special register 
A host variable (including a locator 
variable or a file reference variable) 
A scalar function 
A large object locator 
A column name 
An expression concatenating any of the above 

pattern-expression 
An expression that specifies the string that is to be matched. 
The expression can be specifIEd by: 

A constant 
A special register 
A host variable 
A scalar function whose Operands are any of the above 
An expression concatenating any of the above 
with the following restrictions: 

No element in the expression can be of 
type LONG VARCHAR, CLOB, LONG VARGRAPHIC, 
or DBCLOB. In addition it cannot be a 
BLOB file reference variable. 
The actual length of pattern-expression 
cannot be more than 32 672 bytes. 
）

DB2中幾個隔離級別select..for update with ** 的行鎖

有關DB2中隔離級別和鎖的各種用法和機制的試驗，

在db2 9中我做了以下的試驗， 
Create table RRTest (pkID VARCHAR(20) NOT NULL ,
unID1 varchar(20) Not NULL,
UnID2 varchar(20) ,"CUSTOMER_ID" VARCHAR(6) , 
"ORDER_TYPE" DECIMAL(2,0) , 
"EXECUTION_TYPE" DECIMAL(2,0) , 
"ORDER_DATE" VARCHAR(8) , 
"ORDER_TIME" VARCHAR(6) , 
"ORDER_DATETIME" TIMESTAMP , 
"SIDE" DECIMAL(1,0) , 
"TRADE_TYPE" DECIMAL(1,0) , 
"ORDER_AMOUNT" DECIMAL(15,2) , 
"ORDER_PRICE" DECIMAL(8,4), 
TSID varchar(20) ) 

insert into RRTest 
SELECT Order_ID, Order_ID, Order_ID, 
CUSTOMER_ID, ORDER_TYPE, EXECUTION_TYPE, 

ORDER_DATE, ORDER_TIME, ORDER_DATETIME, 
SIDE, TRADE_TYPE, ORDER_AMOUNT, ORDER_PRICE ,ORDER_ID 
FROM DB2INST1.Fx_Order where ORDER_DATE >'20070401' 
GO 
select count(*) From RRTEST 
72239 

ALTER TABLE "DB2INST1".RRTest 
ADD PRIMARY KEY 
(pkID); 

CREATE UNIQUE INDEX UNIQINDX ON RRTest(unID1) 
CREATE INDEX INDX002 ON RRTest(unID2) 
db2 "RUNSTATS ON TABLE DB2INST1.RRTest 
ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE Access" 


db2 connect to db2TT 
db2 +c 

select * From RRTEST where TSID='20070223ORD01267732' for update with RR 
select * From RRTEST where TSID='20070222ORD01266302' for update with RR 

select * From RRTEST where TSID='20070223ORD01267732' for update with RS 
select * From RRTEST where TSID='20070222ORD01266302' for update with RS 

select * From RRTEST where unID1='20070223ORD01267732' for update with RR 
select * From RRTEST where unID1='20070222ORD01266302' for update with RR 
select * From RRTEST where unID1='20070223ORD01267732' for update with RS 
select * From RRTEST where unID1='20070222ORD01266302' for update with RS 

select * From RRTEST where unID2='20070223ORD01267732' for update with RR 
select * From RRTEST where unID2='20070222ORD01266302' for update with RR 
select * From RRTEST where unID2='20070223ORD01267732' for update with RS 
select * From RRTEST where unID2='20070222ORD01266302' for update with RS 

select * From RRTEST where pkID='20070223ORD01267732' for update with RR 
select * From RRTEST where pkID='20070222ORD01266302' for update with RR 
select * From RRTEST where pkID='20070223ORD01267732' for update with RS 
select * From RRTEST where pkID='20070222ORD01266302' for update with RS

按照以上字段　pkID 是主鍵，unID1 是唯一健索引，unID2 是普通健索引，TSID 是普通字段，沒有在上建立索引。

試驗的結論：

PK_INDEX UNIQ_INDEX NormalINDEX NO_INDEX 
WITH RR 鎖行,不鎖表 鎖行,不鎖表 不鎖行,不鎖表(1) 鎖行,鎖表 
WITH RS 鎖行,不鎖表 鎖行,不鎖表 鎖行,不鎖表 鎖行,鎖表(2)

鎖行是指在一個事務中用某種方式讀取並更改了改行數據並顯示得指明要修改後，這個事務將鎖住改行，直到它提交或者回滾了事務後，才釋放該鎖。

鎖表是指在用以上各種SQL在讀取並更改一行的同時鎖住了整個表。

對以上紅字部分(1)可能有不能理解的是:為什麼對普通索引和主鍵或者唯一健索引的不同結論?　

對PK和UNIQ的解釋是因為RR 是可重復的讀的級別，對這次檢索掃描到的有可能成為自己的潛在檢索對象的內容都會鎖住，而因為是主鍵或者唯一健，別的行不可能成為這次這個檢索的潛在讀的范圍，就是對別的數據此事務根本就沒有必要鎖，任何情況的更改都不可能出現幻讀的情況(此表上的約束限制），所以只鎖這一行。這麼理解對PK,UNIQ沒有問題。

但是NormalINDEX　我認為應該是鎖住這個表而不是不鎖。這點一直沒想明白。留待以後再加強理解。

對RS隔離級別是“鎖定檢索到的數據行”，是通過SQL檢索到的結果進行鎖定， PK,UNIQ,INDEX的結論完全都可以理解。　對 tableScan的檢索而出現的鎖表有些象RR隔離級別的所為。

詳細過程:select * From TTT where ****= ? for update with RR(RS)，這裡的　***並不是隨便定義的。

隔離級別分為RR/RS/CS/UR四個級別：

1.RR隔離級別：在此隔離級別下， DB2會鎖住所有相關的紀錄。在一個SQL語句執行期間，所有執行此語句掃描過的紀錄都會被加上相應的鎖。具體的鎖的類型還是由操作的類型來決定，如果是讀取，則加共享鎖；如果是更新，則加獨占鎖。由於會鎖定所有為獲得SQL語句的結果而掃描的紀錄，所以鎖的數量可能會很龐大，這個時候，索引的增加可能會對SQL語句的執行有很大的影響，因為索引會影響SQL語句掃描的紀錄數量。

2.RS隔離級別：此隔離級別的要求比RR隔離級別稍弱，此隔離級別下會鎖定所有符合條件的紀錄。不論是讀取，還是更新，如果SQL語句中包含查詢條件，則會對所有符合條件的紀錄加相應的鎖。如果沒有條件語句，也就是對表中的所有記錄進行處理，則會對所有的紀錄加鎖。

3.CS隔離級別：此隔離級別僅鎖住當前處理的紀錄。

4.UR隔離級別：此隔離級別下，如果是讀取操作，不會出現任何的行級鎖。對於非只讀的操作，它的鎖處理和CS相同。

DB2默認的隔離級別是CS。即游標穩定性。

DB2分頁查詢：

SELECT * FROM (Select 字段1,字段2,字段3,rownumber() 
over(ORDER BY 排序用的列名 ASC) AS rn from 表名) 
AS a1 WHERE a1.rn BETWEEN 10 AND 20 
SELECT * FROM (Select T.*,rownumber() 
over(ORDER BY T.ID ASC) AS rn from 
ADMINISTRATOR.TEST_BY_HUFENG T) AS a1 
WHERE a1.rn BETWEEN 1 AND 3