DMSQL WITH FUNCTION子句怎么使用

這篇文章主要介紹“DMSQL WITH FUNCTION子句怎么使用”，在日常操作中，相信很多人在DMSQL WITH FUNCTION子句怎么使用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”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.

到此，關于“DMSQL WITH FUNCTION子句怎么使用”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！