Oracle With As 查詢

標籤：

WITH AS

WITH AS短語，也叫做子查詢部分(subquery factoring)，可以讓你做很多事情，定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供資料的部分。

特別對於UNION ALL比較有用。因為UNION ALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，所以可以使用WITH AS短語，則只要執行一遍即可。如果WITH AS短語所定義的表名被調用兩次以上，則最佳化器會自動將WITH AS短語所擷取的資料放入一個TEMP表裡，如果只是被調用一次，則不會。而提示materialize則是強制將WITH AS短語裡的資料放入一個全域暫存資料表裡。很多查詢通過這種方法都可以提高速度。

with

    sql1 as (select to_char(a) s_name from test_tempa),

    sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))

select * from sql1

union all

select * from sql2

union all

select ‘no records‘ from dual

       where not exists (select s_name from sql1 where rownum=1)

       and not exists (select s_name from sql2 where rownum=1);

另外，WMSYS.WM_CONCAT 這個函數可以將查詢結果某列多行拼接成一個結果，非常實用。

With語句的文法(AS後面的括弧是不可以空缺的)

WITH <alias_name> AS (subquery_sql_statement)

SELECT <column_name_list> FROM <alias>;

 

注意，定義了WITH語句必須在後邊的查詢中使用，否則提示錯誤。

 

WITH子句相關總結:

 

1、在同級select前有多個查詢定義的時候，第1個用with，後面的不用with，並且用逗號隔開。

2、最後一個with 子句與下面的查詢之間不能有逗號，只通過右括弧分割,with 子句的查詢必須用括弧括起來

 

3、如果定義了with子句，而在查詢中不使用，那麼會報ora-32035 錯誤：未引用在with子句中定義的查詢名。（至少一個with查詢的name未被引用，解決方案是移除未被引用的with查詢），注意：只要後面有引用的就可以，不一定非要在主查詢中引用，比如後面的with查詢也引用了，也是可以的。

 

4、前面的with子句定義的查詢在後面的with子句中可以使用。但是一個with子句內部不能嵌套with子句。

5、with查詢的結果列有別名，引用的時候必須使用別名或*。

WITH語句的優點:

(1). SQL可讀性增強。比如對於特定with子查詢取個有意義的名字等。

 

(2)、with子查詢只執行一次，將結果儲存在使用者暫存資料表空間中，可以引用多次，增強效能。

 

舉例:在進行匯入EXCEL的過程中，有時候，需要將資料存放區在暫存資料表中，當下一次在進行匯入的時候，進行清除暫存資料表的資料，但是這時候，有時候發生並發問題的話，兩個使用者可能會分別操作對方的資料，所以，可能造成混亂，但是可以使用WITH函數和UNION語句拼接一個SQL語句，儲存在SESSION中，當需要匯出錯誤資訊的時候，可以使用該語句構造資料。

Oracle With As 查詢