Oracle with as

WITH AS短语，也叫做子查询部分（subquery factoring），可以做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。

一是提高了SQL语句的可读性，二是對于重複用到的SQL語句放到with as裡面，後面多次引用以提高效率。

如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里；如果只是被调用一次，则不会創建TEMP表，相當于一個虛擬視圖。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。

观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

一、with as 语法

1、单个语法：

with   tempName as (select ....)

select ...

 

2、多个语法：

with   tempName1 as (select ....)，

tempName2 as (select ....)，

tempName3 as (select ....) ...

select ... 

 

3、嵌套语法：

如果with as 有嵌套的情况， 多个with as，后面的as内部可以直接调用先声明的临时对象。

with   tempName1 as (select ....)，

tempName2 as (select ....)，

tempName3 as (select ....from tempName2.... ) ...

select ... 

 

4、与insert结合使用

如下的with as语句，不能放在insert前，而是放在紧接着要调用的地方前要求将同一个单据编号对应的借款单和核销单中，借款金额不相等的单据，对应的借款单删除，并将对应的核销单插入到借款单表中 （借款单和核销单表结构完全一样）

insert into tableName1

  (field1,

   Field2,

   ...,

   fieldn)

 

with   tempName1 as (select ....)，

tempName2 as (select ....)，

tempName3 as (select ....from tempName2.... ) ...

 

select field1,

            field2,

            ...,

        Fieldn

From    tempName1  ...

        tempName2   ...

tempName3   ...

where   ...

 

5、与delete删除结合使用

Delete from tableName1

Where exitsts

    (

with   tempName1 as (select ....)，

tempName2 as (select ....)，

tempName3 as (select ....from tempName2.... ) ...

 

select ...

From    tempName1  ...

        tempName2   ...

tempName3   ...

where   ...

)

 

6、与update删除结合使用

update tableName1 b

   set b.field1 =

   (

with tempName1  as (select ... from ...)

    select a.NAME from tableName1 a where a. field1 = b. field1

)

 

二、注意事項

1、with必须紧跟引用的select语句

2、with创建的临时表必须被引用，否则报错

3、由于with as是内存中的table所以还是比较快的。如果数据比较大的时候建议不要用with as这样的话变得很慢。

 

 

 

 

1、The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.

2、Formally, the “WITH clause” is called subquery factoring

3、The SQL “WITH clause” is used when a subquery is executed multiple times

4、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