OCP 複習筆記之PL/SQL (4)

最近打算把9i 的OCP教程看一遍，作一些簡單的筆記，作為備忘。

PL/SQL看似簡單，但實際使用起來還是有很多技巧和誤區的。以下記錄的是複習過程中想到的、和容易出現錯誤的地方。

這些東西在教程大部分是沒有記載的。

第四章：多表查詢

4.1 笛卡兒乘積（CROSS JOIN）

1) 當多表關聯查詢中沒有關聯條件、或者關聯條件是恆等的時候（如1=1），就會產出笛卡兒乘積。

2) T1有M行、T2有N行，則笛卡兒乘積返回M*N行。

4.2 連線型別

1) 如果有N個表連線，則連線條件至少應該有N-1個；如果用到複合主鍵進行連線，則可能需要到更多的連線條件。

2) 表的別名不能大於30個字元，如果定義了表的別名，則在select子句中，如果需要用到表名字首，則必須要用別名，而不是用原表名。且表的別名只能用在同一層的SELECT語句中。

3) 等值連線

Ø 等值連線也稱SIMPLE JOIN或INNER JOIN，形式是進行關聯連線時用的是”=”

Ø 如果返回集合有重複列名，則應在重複列名前加上表名。

4) 不等連線

連線詞為between...and 、”>”、“>”等。

5) 外連線

Ø 外連線分為左外連線、右外連線、全外連線；一個左外連線 + UNION + 右外連線等於全外連線。

Ø 左外連線和右外連線都可以用(+)和left(right) outer join兩種方法表示，但全外連線只能用full outer join表示，而不能用兩個“+”代替。

Ø 需要返回某表的所有記錄，就把“+”寫在等值關聯中另一個表對應的欄位上。

6) 自連線

自己與自己關聯。自連線中必須要給表置別名。

7) Cross join

相當於不寫where條件，得到的就是笛卡爾乘積。

8) Natural join

自然連線，不需要在where中寫等值連線表示式。有幾點需要注意：

Ø 自然連線中，連線的兩個表必須有列名完全一樣的列；否則返回的是笛卡爾乘積。

Ø 兩個表有多少個相同的列名，就會進行多少個進行等值連線。

Ø 在SELECT子句中，如果是兩個表都存在的列，則不能在這些列名前加表名字首；否則可以加；如果需要在where子句中用兩個表都存在的列作為條件，則也不能在這些列名前加上表名字首。

如：t1和t2都有id列，以下SQL是錯誤的：

錯誤SQL：

Select t1.id from t1 natural join t2;

正確應該為：

Select id from t1 natural join t2;

錯誤SQL：

Select * from t1 join natural t2 where t1.id=1;

正確應該為：

Select * from t1 natural join t2 where id=1;

Ø 如果在select子句中用*,則返回結果相當於是對兩個表的列名執行distinct後的結果。

如：

普通連線：SQL> select * from t1,t2 where t1.id=t2.id;

ID ID NAME

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

1 1

自然連線：SQL> select * from t1 natural join t2;

ID NAME

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

1

Ø 兩個表的相同列對應的資料型別必須相同；或者這些列的所有資料可以隱式轉換為另一個表對應的欄位的資料型別。

9) JOIN...Using

Ø 如果兩個表有多個列名相同的列，而你又不想用所有的列作關聯的時候，可以用using，如：

select * from t1 join t2 using(id);

Ø 在SELECT子句中，如果是兩個表都存在的列，則不能在這些列名前加表名字首；否則可以加；如果需要在where子句中用兩個表都存在的列作為條件，則也不能在這些列名前加上表名字首。

Ø 在using中可以用一個或多個共有的列進行關聯；如果是多個，則不同的列之間用“，”分隔

select * from t1 join t2 using(id,name);

10) JOIN...On

當需要用列名不相同的列進行關聯時，可以用JOIN...ON：

select * from t1 join t2 on t1.id=t2.tid;

也可以用多個JOIN...ON

select * from t1 join t2 on t1.id=t2.tid join t3 on t1.name=t3.tname;