WITH引出的子查询扇出子句

官方文档：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2077142

 

语法：

subquery_factoring_clause::=

Description of the illustrationsubquery_factoring_clause.gif

 

subquery_factoring_clause

 

The WITH query_name clause lets youassign a name to a subquery block. You can then reference thesubquery block multiple places in the query by specifying the queryname. Oracle Database optimizes the query by treating the queryname as either an inline view or as a temporary table.

 

You can specify this clause in any top-levelSELECT statement and in most types ofsubqueries.

---你可以在顶层SELECT语句中和在大多数子查询中指定这个子句。注：这个WITH子句只能用于SELECT语句中，其他用不了。

 

The query name is visible to the main query and to allsubsequent subqueries except the subquery that defines the queryname itself.

--这个查询名字能够在主查询和所有随后的子查询中可以用，除了定义它字句的子查询中不行。

 

1、定义的子查询块，即是子查询扇出字句，在随后的查询块的任何位置都可以使用，但是在此子查询扇出子句之前的查询是不能使用这个查询名字的。否则会报错：ORA-32031with字句中一个查询名字的非法引用。

2、所有的子查询扇出子句必须在主查询块之前定义，那么主查询块才能引用。如果主查询块引用了一个在其后定义的子查询扇出子句的名字，会报错：ORA-00903：无效的表名。

综上所述：要使用子查询扇出子句查询块，就先定义，然后使用。

 

Restrictions on SubqueryFactoring This clause is subject to the followingrestrictions:

• You can specify only one subquery_factoring_clause in asingle SQL statement. You cannot specify a query_name in its ownsubquery. However, anyquery_name defined in thesubquery_factoring_clause can beused in any subsequent named query block in thesubquery_factoring_clause.---见上的描述。

• In a compound query with set operators, you cannot usethe query_name for any of thecomponent queries, but you can use the query_name in theFROM clause of any of the componentqueries.