ORACLE WITH AS 用法

原文傳送門：http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx

 

記得以前在論壇裡看到inthirties用到過WITH AS這個字眼，當時沒特別在意。今天在一個文章裡又看到有人用這個，所以就去網上搜了搜相關內容，自己小試了一把，寫下來，方便以後忘了的話學習。

===================================================================================

先舉個例子吧：

有兩張表，分別為A、B，求得一個欄位的值先在表A中尋找，如果A表中存在資料，則輸出A表的值；如果A表中不存在，則在B表中尋找，若B表中有相應記錄，則輸出B表的值；如果B表中也不存在，則輸出"no records”字串。

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);

再舉個簡單的例子

with a as (select * from test)

select * from a;

其實就是把一大堆重複用到的SQL語句放在with as 裡面，取一個別名，後面的查詢就可以用它

這樣對於大批量的SQL語句起到一個最佳化的作用，而且清楚明了

下面是搜尋到的英文文檔資料

About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

   • The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
   • Formally, the “WITH clause” is called subquery factoring
   • The SQL “WITH clause” is used when a subquery is executed multiple times
   • Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.
We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH  clause”:

WITH
sum_sales AS
  select /*+ materialize */
    sum(quantity) all_sales from stores
number_stores AS
  select /*+ materialize */
    count(*) nbr_stores from stores
sales_by_store AS
  select /*+ materialize */
  store_name, sum(quantity) store_sales from
  store natural join sales
SELECT
   store_name
FROM
   store,
   sum_sales,
   number_stores,
   sales_by_store
where
   store_sales > (all_sales / nbr_stores)
;

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

=================================================================================

下面自己小試一把，當然，一點都不複雜，很簡單很簡單的例子，呵呵。

SQL> create table t2(id int); Table created. SQL> create table t3(id int); Table created. SQL> insert into t2 values(1); 1 row created. SQL> insert into t2 values(2); 1 row created. SQL> insert into t3 values(3); 1 row created. SQL> commit; Commit complete. SQL> select * from t2; ID ---------- 1 2 SQL> select * from t3; ID ---------- 3 SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from t2 5 union 6 select * from t3; sql2 as (select * from t3) * ERROR at line 3: ORA-32035: unreferenced query name defined in WITH clause --從這裡可以看到，你定義了sql1和sql2，就得用它們哦，不然會報錯的。 SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from sql1 5 union 6 select * from sql2; ID ---------- 1 2 3 --下面加個WHERE條件試試 SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from sql1 5 union 6 select * from sql2 7 where id in(2,3); ID ---------- 1 2 3 --奇怪。為什麼加了WHERE條件還是輸出ID=1的記錄了，繼續往下看： SQL> with 2 sql1 as (select * from t2), 3 sql2 as (select * from t3) 4 select * from sql1 5 where id=3 6 union 7 select * from sql2 8 where id=3; ID ---------- 3 --可以看到，每個條件是要針對每個SELECT語句的。

好了就先記這些吧，以後看到了新的用法再補充。