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子句怎么使用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！