Oracle while usage sample sharing _oracle

When a section is used more than once in a query, a public temporary table can be created with an Oracle with statement. Because the subquery avoids repeated parsing in the memory temp table, the execution efficiency improves a lot. Temporary tables are automatically cleared at the end of a query.

General syntax Format:

Copy Code code as follows:

With
Alias_name1 as (Subquery1),
Alias_name2 as (SubQuery2),
... alias_namen as (Subqueryn)
Select Col1,col2 ... col3
From Alias_name1,alias_name2......,alias_namen

Examples of Oracle with statements:

Copy Code code as follows:

With Q1 as (SELECT 3 + 5 S from DUAL),
Q2 AS (SELECT 3 * 5 M from DUAL),
Q3 as (SELECT S, M, S + M, S * m from Q1, Q2)
SELECT * from Q3;

Output results:

Copy Code code as follows:

1 8 15 23 120