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