oracle SQL with 子句

with 子句是在oracle 9i release 2 中引入的。with 子句又叫做子查詢構造語句。可以用來給一個子查詢塊命名。該查詢塊的檢索結果會被oracle 儲存在使用者的臨時表空間中，該命名塊就像虛表或者內聯檢視一樣。使用SQL with 子句可以達到如下兩個目的，一使複雜的sql 語句的可讀性可理解性更高。二當在一個sql 語句中需要多次使用到某個子查詢的結果的時候，可以提升sql 語句的效能。

下面這個例子只是用來說明with 語句的語法和注意事項：

SQL> with emp as

2 ( select employee_id,first_name,last_name

3 from employees)

4 select employee_id,first_name,last_name

5 from emp

6 where employee_id = 100;

EMPLOYEE_ID FIRST_NAME LAST_NAME

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

100 Steven King

with 子句只在同一個語句內有效。在另外一個語句中使用剛才用with子句建立的

查詢命名塊無效。

SQL> select employee_id,first_name,last_name

2 from emp

3 where employee_id = 100;

from emp

*

ERROR at line 2:

ORA-00942: table or view does not exist

下面是一個關於使用with 帶來可讀性提高的例子。

這個查詢的目的是找到employees 表中employee_id 編號存在的間隔。因為employee_id 是從100 開始編的，所有ids >99.

SQL> with all_ids as

2 (select level ids from (select max(employee_id) max_id

3 from employees)

4 connect by level <= max_id)

5 select ids

6 from all_ids

7 where ids not in (select employee_id from employees)

8 and ids > 99

9 order by ids;

IDS

----------

207

208

209

employees 表中employee_id 存在以上3個編號是空缺的。

SQL> select * from employees

2 where employee_id = 207;

no rows selected

至於效能上提高的例子暫時舉不出來。

oracle SQL with 子句

相關文章