以下相当于一句独立的sql，可以在sql window 中独立运行：
With
ab As (Select * From scott.dept),
ba As (Select * From scott.emp)
Select ab.*,ba.*
From ab,ba
Where ab.deptno = ba.deptno
Begin
For iLoop In (
With
ab As (Select * From scott.dept),
ba As (Select * From scott.emp)
Select ab.*
From ab,ba
Where ab.deptno = ba.deptno
) Loop
dbms_output.put_line('>' || iLoop.Deptno || iLoop.Dname);
End Loop;
End;
Declare
Cursor cur_main Is
With
ab As (Select * From scott.dept),
ba As (Select * From scott.emp)
Select ab.*
From ab,ba
Where ab.deptno = ba.deptno;
Begin
For iLoop In cur_main Loop
dbms_output.put_line('>' || iLoop.Deptno || iLoop.Dname);
End Loop;
End;
---------------------------------------------------------
About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.
The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:
• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
• Formally, the “WITH clause” is called subquery factoring
• The SQL “WITH clause” is used when a subquery is executed multiple times
• 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.
The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:
• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
• Formally, the “WITH clause” is called subquery factoring
• The SQL “WITH clause” is used when a subquery is executed multiple times
• 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
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9099175/viewspace-904552/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
Oracle专家调优秘密
请登录后发表评论
登录
全部评论
