《Oracle Database SQL Expert Exam Guide (Exam 1Z0-047) 》
use the WitH Clause
You can use the keyword WITH to assign a name to a subquery block. Once the
name is assigned, you can reference the name from elsewhere in the query.
WITH is considered a clause of the SELECT statement.
Let’s look at an example—we’ll use WITH to declare two different subqueries.
We’ll name one PORT_BOOKINGS and the other DENSEST_PORT (see the
following listing, lines 2 and 8), and then invoke both of them by name in a
SELECT statement (lines 12 through 14). Here’s the code (line numbers added):
01 WITH
02 PORT_BOOKINGS AS (
03 SELECT P.PORT_ID, P.PORT_NAME, COUNT(S.SHIP_ID) CT
04 FROM PORTS P, SHIPS S
05 WHERE P.PORT_ID = S.HOME_PORT_ID
06 GROUP BY P.PORT_ID, P.PORT_NAME
07 ),
08 DENSEST_PORT AS (
09 SELECT MAX MAX_CT
10 FROM PORT_BOOKINGS
11 )
12 SELECT PORT_NAME
13 FROM PORT_BOOKINGS
14 WHERE CT = (SELECT MAX_CT FROM DENSEST_PORT);
Note that neither PORT_BOOKINGS nor DENSEST_PORT is a database
object. They are the names of queries that exist
solely within this WITH/SELECT statement.
Also note the subqueries on lines 3 through
6, and on lines 9 through 10. Also note that
the only semicolon is at the end of the entire
statement, not at the end of any individual SQL
statement within the overall WITH statement.
Internally, Oracle SQL treats a named query
within the WITH clause as a temporary table or
as an inline view. (We examine inline views in
Chapter 10.)
The WITH clause can be used in the toplevel query of a SELECT statement and in many
(but not all) subqueries of the SELECT statement, such as shown in line 14. If you
use WITH to name a subquery, that name isn’t recognized within the subquery itself
but is recognized in most every other location in the overall query. In other words,
consider line 8, where we name and specify the subquery DENSEST_PORT. We
couldn’t reference the name DENSEST_PORT in lines 9 through 10, but we can
reference the name everywhere else.