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.

发表评论

电子邮件地址不会被公开。