Friday, February 09, 2007 - 文章

ORACLE的JOIN大驚奇!

無意中發現了ORACLE JOIN語法的獨到之處!! 讓一向對ORACLE很感冒的我再次肅然起敬!

為了示範,我建了三個TABLE,各INSERT一筆資料。

CREATE TABLE JFSoftware (
Code VARCHAR2(8), Name VARCHAR2(32), VendorCode VARCHAR2(4), CatgCode VARCHAR2(4)
);
CREATE TABLE JFVendor (Code VARCHAR2(4), Name VARCHAR2(16));
CREATE TABLE JFCategory (Code VARCHAR2(4), Name VARCHAR2(16));
INSERT INTO JFVendor VALUES ('MS','Microsoft');
INSERT INTO JFCategory VALUES ('DEV','Developer Tools');
INSERT INTO JFSoftware VALUES ('VS2005','Visual Studio 2005','MS','DEV');

好,我們現在有三個資料表,一個存軟體資料,一個放廠商資料,一個則是分類,接著我想列出軟體、分類名稱與廠商名稱,所以組出了以下的JOIN寫法:

SELECT S.CODE, C.NAME, NAME
FROM JFSoftware S, JFVendor V, JFCategory C
WHERE S.VendorCode=V.Code AND S.CatgCode=C.Code

這裡故意犯了一個錯,第二個NAME應該是V.NAME才對,依我多年的SQL經驗,此語法會被資料庫抓出錯誤而無法執行。Yes,ORACLE傳回了ORA-00918: column ambiguously defined! 錯誤。

有些人習慣用ANSI JOIN,如果上述的錯誤是發生在以下的寫法呢?

SELECT S.CODE, C.NAME, NAME
FROM JFSoftware S
JOIN JFVendor V ON S.VendorCode=V.Code
JOIN JFCategory C ON S.CatgCode=C.Code

ORACLE被太多的JOIN搞昏頭了(實驗結果是JOIN兩個TABLE時ORACLE會認定語法錯誤,但JOIN三個TABLE時就...),居然讓這段SQL放行,並傳回以下的結果。

CODE     NAME             NAME 
-------- ---------------- ---------------- 
VS2005   Developer Tools  Developer Tools
1 row(s)

公司的DBA協助查出,這是個Bug(ORACLE的官方文件要登入才能看,但大家可以查這篇文章中的Bug 3584515,看看標題,心領神會一番),據說應會在9.2.0.7及10.2.0.1更正。

SQL Collation: Width-Sensitive, What's that?

遇到SQL Server中因為欄位Collation分別設為Width-Sensitive與Width-Insensitive而衍生問題,之前約略知道它與全型半型有關,今天才認真地用實驗驗證一下WS與WI的區別。建一個TABLE,ColWS設為Width-Sensitive,ColWI則設為Width-Insensitive,並分別塞入半型123及全型123。

CREATE TABLE [dbo].[WSTest](
[ColWS] [nchar](10) COLLATE Chinese_Taiwan_Stroke_CI_AS_WS NULL,
[ColWI] [nchar](10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
INSERT INTO WSTest VALUES ('123','123')
INSERT INTO WSTest VALUES ('123','123')

接著試試以下的兩個SELECT,就可看出二者有什麼不同了。

SELECT * FROM WSTest WHERE ColWS='123'

ColWS      ColWI
---------- ----------
123        123

(1 row(s) affected)

SELECT * FROM WSTest WHERE ColWI='123'

ColWS      ColWI
---------- ----------
123        123
123      123

(2 row(s) affected)

搜尋

Go

<February 2007>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
25262728123
45678910
 
RSS
【工商服務】
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


BlogLook Score and Rank

Syndication