ORACLE WITH AS 用法
分类： Oracle 2009-12-02 13:51 950人阅读 评论(0) 收藏 举报
原文传送门：blog.csdn/wh62592855/archive/2009/11/06/4776631.aspx
记得以前在论坛里看到inthirties用到过WITH AS这个字眼，当时没特别在意。今天在一个帖子里又看到有人用这个，所以就去网上搜了搜相关内容，自己小试了一把，写下来，方便以后忘了的话学习。
===================================================================================
先举个例子吧：
有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果胆怯的近义词A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"no records”字符串。
view plaincopy to clipboardprint?
1. with
2. sql1 as (lect to_char(a) s_name from test_tempa),
3. sql2 as (lect to_char(b) s_name from test_tempb where not exists (lect s_name from sql1 where rownum=1))
4. lect * from sql1
5. union all
6. lect * from sql2
地道战观后感7. union all
8. lect 'no records' from dual
9. where not exists (lect s_name from sql1 where rownum=1)
10. 用恰似也似造句 and not exists (lect s_name from sql2 where rownum=1); 位置修改
再举个简单的例子
with a as (lect * from test)
lect * from a;
其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它
这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了
下面是搜索到的英文文档资料
About Oracle WITH clau
Starting in Oracle9i relea 2 we e an incorporation of the SQL-99 “WITH clau”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.
The SQL “WITH clau” is very similar to the u of Global temporary tables (GTT), a technique that is often ud to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clau”:
• The SQL “WITH clau” only works on Oracle 9i relea 2 and beyond.
• Formally, the “WITH clau” is called subquery factoring
• The SQL “WITH clau” is ud when a subquery is executed multiple times
• Also uful 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 clau” is normally ud when an aggregation is referenced multiple times in a query.
家庭聚会
We can also u the SQL-99 “WITH clau” instead of temporary tables. The Oracle SQL “WITH clau” 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 clau” is very confusing at first becau the SQL statement does not begin with the word SELECT. Instead, we u the “WITH clau” 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 clau”:
WITH
sum_sales AS
lect /*+ materialize */
sum(quantity) all_sales from stores
number_stores AS
lect /*+ materialize */
count(*) nbr_stores from stores
sales_by_store AS
lect /*+ 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 u of the Oracle undocumented “materialize” hint in the “WITH clau”. The Oracle materialize hint is ud to ensure that the Oracle cost-bad optimizer materializes the temporary tables that are created inside the “WITH” clau. 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 clau” does not yet fully-functional within Oracle SQL and it does not yet support the u of “WITH clau” replacement for “CONNECT BY” when performing recursive queries.
To e how the “WITH clau” is ud in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clau” showing the u of the SQL-99 “WITH clau” to traver a recursive bill-of-materials hierarchy
The SQL-99 “WITH clau” is very confusing at first becau the SQL statement does not begin with the word SELECT. Instead, we u the “WITH clau” 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” clau”:
=================================================================================
下面自己小试一把，当然，一点都不复杂，很简单很简单的例子，呵呵。
