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.