Oracle With 語法 示例
以下相當於一句獨立的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部落格 ” ,連結:http://blog.itpub.net/9099175/viewspace-904552/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 操作表結構基本語法及示例Oracle
- YAML檔案語法及示例YAML
- lisp 利器 format 語法示例 控制字元LispORM字元
- SELinux策略語法以及示例策略Linux
- GaussDB SQL基礎語法示例-迴圈語句SQL
- oracle sequence語法Oracle
- oracle partition by 語法Oracle
- python爬蟲:XPath語法和使用示例Python爬蟲
- Robot Framework(8)——指令碼語法示例記錄Framework指令碼
- Java正規表示式的語法與示例Java
- Oracle Merge語法Oracle
- 13 個示例快速入門 JS 抽象語法樹JS抽象語法樹
- Oracle sql with 語句語法與例子OracleSQL
- Oracle merge into delete語法Oracledelete
- 【轉載】oracle更新語法Oracle
- [SQL] Oracle基礎語法SQLOracle
- 建立Oracle包的語法Oracle
- Oracle常用sql語法集合OracleSQL
- ORACLE的基本語法(轉)Oracle
- Oracle物化檢視語法Oracle
- ORACLE UPDATE 語句語法與效能分析Oracle
- 微信小程式WXML頁面常用語法(講解+示例)微信小程式XML
- 透過這些示例快速學習Java lambda語法Java
- Oracle 10g基本語法Oracle 10g
- ORACLE的基本語法集錦Oracle
- ORACLE DML語句鎖機制導致事務等待示例Oracle
- Oracle儲存過程基本語法Oracle儲存過程
- Oracle多表關聯更新的語法Oracle
- oracle之merge語法(轉載)Oracle
- Oracle SQL的一些語法OracleSQL
- C++ 預設引數與引用傳遞:語法、用法及示例C++
- Oracle 建表時LOB欄位語法Oracle
- oracle trigger語法小記(一)Oracle
- ORACLE SQL Loader的詳細語法OracleSQL
- oracle update語句的幾點寫法Oracle
- Oracle和SQL對比語法總結OracleSQL
- Oracle10g新增CONVERT語法Oracle
- oracle PL/SQL示例OracleSQL