Oracle with語句是經常可以見到的語句,下面就為您詳細介紹Oracle with語句的用法,如果您對Oracle with語句感興趣的話,不妨一看。
當查詢中多次用到某一部分時,可以用Oracle with語句創建一個公共臨時表。因為子查詢在內存臨時表中,避免了重復解析,所以執行效率會提高不少。臨時表在一次查詢結束自動清除。
一般語法格式:
- with
- alias_name1 as (subquery1),
- alias_name2 as (subQuery2),
- ……
- alias_nameN as (subQueryN)
- select col1,col2…… col3
- from alias_name1,alias_name2……,alias_nameN
Oracle with語句的例子:
- SQL> 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;
輸出結果:
- S M S+M S*M
- ---------- ---------- ---------- ----------
- 8 15 23 120