DMSQL WITH FUNCTION子句

WITH FUNCTION子句
WITH FUNCTION子句用於在SQL語句中臨時宣告並定義儲存函式，這些儲存函式可以在其作用域內被引用。相比模式物件中的儲存函式，透過WITH FUNCTION定義的儲存函式在物件名解析時擁有更高的優先順序。和公用表表示式CTE類似，WITH FUNCTION定義的儲存函式物件也不會儲存到系統表中，且只在當前SQL語句內有效。

WITH FUNCTION子句適用於偶爾需要使用儲存過程的場景。和模式物件中的儲存函式相比，它可以清楚地看到函式定義並避免了DDL操作帶來的開銷。

語法格式
WITH < 函式> {< 函式>}
引數
1．< 函式> 語法遵照《DMSQL程式設計》中儲存過程的語法規則。
語句功能
供使用者定義同一語句內臨時使用的儲存函式。
使用說明
1．中定義的函式的作用域為所在的查詢表示式內；
2．同一中函式名不得重複；
3. 中定義的函式不能是外部函式。

該語句的使用者並不需要CREATE PROCEDURE資料庫許可權。

舉例說明
例如WITH FUNCTION中定義的函式優先順序高於模式物件的例子。

SQL> WITH FUNCTION f1(C INT) RETURN INT AS BEGIN RETURN C * 10; END;
2   SELECT f1(5236) FROM DUAL;
3   /
LINEID     F1(5236)   
---------- -----------
1          52360
used time: 1.352(ms). Execute id is 34061.

例如WITH FUNCTION和公用表表示式混合的例子。

SQL> WITH FUNCTION f21(C1 INT) RETURN INT AS BEGIN RETURN C1; END;
2   SELECT f21(1) FROM dual WHERE 100 IN
3   (
4   WITH FUNCTION f22(C1 INT) RETURN INT AS BEGIN RETURN C1 + 2; END;
5   FUNCTION f23(C1 INT) RETURN INT AS BEGIN RETURN C1 - 2; END;
6   v21(C) AS (SELECT 50 FROM dual)
7   SELECT f22(C) +f23(C) FROM v21
8   );
9   /
LINEID     F21(1)     
---------- -----------
1          1
used time: 12.313(ms). Execute id is 34092.

公用表表示式子句
巢狀SQL語句如果層次過多，會使SQL語句難以閱讀和維護。如果將子查詢放在臨時表中，會使SQL語句更容易維護，但同時也增加了額外的I/O開銷，因此，臨時表並不太適合資料量大且頻繁查詢的情況。為此，在DM7中引入了公用表表示式(CTE，COMMON TABLE EXPRESSION)，使用CTE可以提高SQL語句的可維護性，同時CTE要比臨時表的效率高很多。CTE 與派生表類似，具體表現在不儲存為物件，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。

WITH AS短語，也叫做子查詢部分（SUBQUERY FACTORING），它定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。它可以有效提高SQL語句的可讀性，也可以用在UNION ALL的不同部分，作為提供資料的部分。

公用表表示式的作用
公用表表示式（CTE）是一個在查詢中定義的臨時命名結果集，將在FROM子句中使用它。每個CTE僅被定義一次（但在其作用域內可以被引用任意次），並且在該查詢生存期間將一直生存，而且可以使用CTE來執行遞迴操作。

因為UNION ALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，所以可以使用WITH AS短語，則只要執行一遍即可。如果WITH AS短語所定義的表名被呼叫兩次以上，則最佳化器會自動將WITH AS短語所獲取的資料放入一個臨時表裡，如果只是被呼叫一次則不會，很多查詢透過這種方法都可以提高速度。

公用表表示式的使用
語法格式
WITH < 公用表表示式子句>{, < 公用表表示式子句>}
< 公用表表示式子句>：：=< 公用表表示式名[ ( <列名>{,< 列名>} ) ] AS ( 公用表表示式子查詢語句)>
引數
1．< 公用表表示式名> 公用表表示式的有效識別符號；
2．< 列名> 指明被建立的公用表表示式中列的名稱；
3．< 公用表表示式子查詢語句> 標識公用表表示式所基於的表的行和列，其語法遵照SELECT語句的語法規則。

語句功能
供使用者定義公用表表示式，也就是WITH AS語句。
使用說明
1．< 公用表表示式名>必須與在同一WITH子句中定義的任何其他公用表表示式的名稱不同，但公用表表示式名可以與基表或基檢視的名稱相同。在查詢中對公用表表示式名的任何引用都會使用公用表表示式，而不使用基物件；
2．< 列名>在一個CTE 定義中不允許出現重複的列名。指定的列名數必須與< 公用表表示式子查詢語句>結果集中列數匹配。只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的；
3．< 公用表表示式子查詢語句>指定一個結果集填充公用表表示式的SELECT 語句。除了CTE不能定義另一個CTE 以外，< 公用表表示式子查詢語句> 的SELECT 語句必須滿足與建立檢視時相同的要求；
4．公用表表示式後面必須直接跟使用CTE的SQL語句，否則無效。
該語句的使用者必須對< 查詢說明>中的每個表均具有SELECT許可權。

舉例說明
公用表表示式可以認為是在單個SELECT、INSERT、UPDATE、DELETE 或CREATE VIEW 語句的執行範圍內定義的臨時結果集。
例如建立一個表TEST1和表TEST2，並利用公用表表示式對它們進行連線運算。

SQL> CREATE TABLE TEST1(I INT);
executed successfully
used time: 17.257(ms). Execute id is 34224.
SQL> INSERT INTO TEST1 VALUES(1);
affect rows 1
used time: 1.008(ms). Execute id is 34226.
SQL> INSERT INTO TEST1 VALUES(2);
affect rows 1
used time: 0.712(ms). Execute id is 34227.
SQL> CREATE TABLE TEST2(J INT);
executed successfully
used time: 42.221(ms). Execute id is 34229.
SQL> INSERT INTO TEST2 VALUES(5);
affect rows 1
used time: 1.104(ms). Execute id is 34230.
SQL> INSERT INTO TEST2 VALUES(6);
affect rows 1
used time: 0.696(ms). Execute id is 34232.
SQL> INSERT INTO TEST2 VALUES(7);
affect rows 1
used time: 0.664(ms). Execute id is 34234.
SQL> WITH CTE1(K) AS(SELECT I FROM TEST1 WHERE I > 1),
2   CTE2(G) AS(SELECT J FROM TEST2 WHERE J > 5)
3   SELECT K, G FROM CTE1, CTE2;
LINEID     K           G          
---------- ----------- -----------
1          2           6
2          2           7
used time: 1.692(ms). Execute id is 34237.

例如利用公用表表示式將表TEST1中的記錄插入到TEST2表中。

SQL> INSERT INTO TEST2 WITH CTE1 AS(SELECT * FROM TEST1)
2   SELECT * FROM CTE1;
affect rows 2
used time: 1.048(ms). Execute id is 34247.
SQL> SELECT * FROM TEST2;
LINEID     J          
---------- -----------
1          5
2          6
3          7
4          1
5          2
used time: 1.135(ms). Execute id is 34249.