故障008：CTE混合插入迁移改写
DM技术交流QQ群：940124259
1. 问题描述
近期越来越多软件开发商适配达梦数据库，中途难免会遇到各种各样的问题，毕竟从不同数据库中移植过来，各大数据库的提供的SQL写法都有独特的语法风格，加上很多开发人员不是很关心底层的东西，尽是一套ORM机制操纵数据库，一旦遇到问题，无从下手。
今日给大家普及的适配话题是关于CTE递归查询插入，CTE是绝大多数据库中非常炫酷的特性，比较明显的特点是SQL中带with关键字，可以用它完成很多骚操作，比如递归查询、子查询优化等等。值得大家注意的是CTE在不同数据库的写法有一点点差异：顺序和位置。
请大家随时适应各大数据库厂商的产品功能，与时俱进，达梦已经尽可能与主流数据库兼容语法，个别的地方靠大家翻阅手册，适当改写。
某公司一开发人员向我方询求帮助，原先数据库采用postgresql，开发语言使用python，适配过程很坎坷，因为他对数据库很多都不懂，我们只能一步一步协助他改写SQL，排查个别python写法问题。此次为大家讲解CTE与INSERT、集合操作的改写案例。
先上菜，开发人员提供的截图，试调试、改写的错误SQL：
2. 解决方法
2.1 问题剖析
步骤1：快速定位SQL错误点
| 以上提示明显是语法书写不符合达梦数据库格式，代码错误发生在第一行CTE别名附近。再仔细观察，达梦官方手册中涉及with的写法，从未提到recursive关键字，则这个多余的。 |
|---|
步骤2：大概掌握整体语句的作用和含义
| 凡是了解一点数据库CTE，基本知道CTE有两大功能：普通CTE写法与递归CTE写法。既然看到recursive和with内部的union（引用CTE表别名）子句写法，进一步断定属于递归查询CTE，则在union后追加all 关键字，完成with递归查询的完整语法书写，符合达梦数据库SQL引擎的识别和解析。 |
|---|
步骤3：基于步骤2初步的适配改写，再次检验SQL执行效果，逐渐完善
| 一般认为通过步骤2的调整，理论是没问题，但在实际测试过程中，仍报错。with整体语法在外层末尾只能跟select查询语句，目前在达梦数据库。 |
|---|
步骤4：换写insert与with顺序位置，调整为insert … select 格式的书写格式
| 进一步把with转成select表子查询方式，符合达梦数据库的SQL语法。 |
|---|
2.2 改写SQL
改写1：仅改with，报错，不符合达梦语法
报错：根本不支持with后面直接使用insert方法。
改写2：简单调换with位置，放在末尾，报错
报错：group_imply没事先声明定义，insert into先调用，报错属情理之中。
改写3：最终版，改成insert…select的子查询表达式
提取insert主干部分到前面，with和集合差集放入子查询表达式。
INSERT INTO res_groups_users_rel (gid, uid)
(
WITH group_imply(gid, hid) AS (
SELECT gid, hid
FROM res_groups_implied_rel
UNION ALL
SELECT i.gid, r.hid
FROM res_groups_implied_rel r
JOIN group_imply i ON (i.hid = r.gid)
)
SELECT i.hid, r.uid
FROM group_imply i, res_groups_users_rel r
WHERE r.gid = i.gid
AND i.gid = 5
EXCEPT
SELECT r.gid, r.uid
FROM res_groups_users_rel r
JOIN group_imply i ON (r.gid = i.hid)
WHERE i.gid = 5
)
3. CTE知识拓展
3.1 CTE优势
-
避免嵌套SQL语句的层次过多的旧式写法，使SQL语句易于阅读和维护。
-
鉴于子查询式的临时表不太适合频繁查询而又数据量大的情况，引用CTE效率高，表现在即时即用，不会存储为持久的对象（可理解为语句级的临时表，相较于派生表查询结果集存放在匿名临时表【并不是代表没有名称，数据库系统内部自动命名】中，它是被定义存放于有命名的临时表中，方便于语句中多次引用），仅在查询期间有效，并且它可以自引用，支持在同一个查询（整个SQL语句）中被引用多次。
-
WITH CTE子句所定义的表名被调用两次以上，优化器会自动将 WITH CTE 子句所获取的数据放入一个临时表（特点：产生少量redo日志，不产生undo日志，数据存活周期短，用完自动释放空间）里，很多SQL调优通过这样特性和思想来提高速度。
本人将CTE语法分为普通CTE和递归CTE，递归CTE区别是在CTE语句当中增强UNION ALL递归子句部分，而此篇文章主要围绕该话题展开，而普通CTE没有特别介绍的地方，使用比较简单，但递归CTE注意点特别多。
3.2 注意事项
CTE递归查询语句的公用表达式的表列名必须定义，这个与其他数据库（MySQL、SQLSERVER）的差异之处。公用表达式表别名不能出现在定位点成员（可理解为迭代起点），而在递归成员【 引用公用表达式表别名的SELECT查询语句，通过引用自身反复迭代执行，下一次迭代的数据基于上一次迭代的查询结果，当且仅当本次迭代结果为空集时才终止迭代】中有且仅能引用一次。- CTE递归查询语句必须明确定位点成员，并且朝已知方向迭代到尽头，避免无限迭代下去。
递归查询的层次深度受参数CTE_MAXRECURSION影响，默认值100，即表示允许迭代查询层次为100次。
如果现场遇到很大的结果集，迭代次数肯定会超过100，此时可修改参数值范围【1,4294967294】。- 可以把
CTE递归查询中涉及相关子查询优化为WITH FUNCTION形式，受CTE_OPT_FLAG参数影响。 CTE查询语句目前仅支持是SELECT查询语句，有别于其他数据库（重点）。但语法上它支持任意SELECT语句，就实际情况而言，只有CTE查询语句中引用CTE表达式表别名，CTE才有意义。
3.3 CTE递归应用
示例1：CTE递归查询员工上下级关系图。
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP
(
EMPLOYEEID SMALLINT,
FIRST_NAME VARCHAR2 (30) NOT NULL,
LAST_NAME VARCHAR2 (40) NOT NULL,
TITLE VARCHAR2 (50) NOT NULL,
DEPTID SMALLINT NOT NULL,
MANAGERID INT NULL
);
INSERT INTO EMP VALUES (1, 'KEN', 'SANCHEZ', 'CHIEF EXECUTIVE OFFICER', 16, NULL);
INSERT INTO EMP VALUES (273, 'BRIAN', 'WELCKER', 'VICE PRESIDENT OF SALES', 3, 1);
INSERT INTO EMP VALUES (274, 'STEPHEN','JIANG', 'NORTH AMERICAN SALES MANAGER',3, 273);
INSERT INTO EMP VALUES (275, 'MICHAEL', 'BLYTHE', 'SALES REPRESENTATIVE',3, 274);
INSERT INTO EMP VALUES (276, 'LINDA', 'MITCHELL', 'SALES REPRESENTATIVE',3, 274);
INSERT INTO EMP VALUES (285, 'SYED', 'ABBAS', 'PACIFIC SALES MANAGER', 3, 273);
INSERT INTO EMP VALUES (286, 'LYNN', 'TSOFLIAS', 'SALES REPRESENTATIVE',3, 285);
INSERT INTO EMP VALUES (16, 'DAVID', 'BRADLEY', 'MARKETING MANAGER', 4, 273);
INSERT INTO EMP VALUES (23, 'MARY', 'GIBSON', 'MARKETING SPECIALIST', 4, 16);
COMMIT;
-- CTE递归查询输出组织的树状结构
with
cte(deptid, lvl, mgrno, empno, ename, title) as
(
select e.deptid, 0 lvl, e.managerid mgrno, e.employeeid empno, e.first_name ename, e.title
from emp e where e.managerid is null -- 定位点成员，即递归的初始启动值
union all
select e.deptid, lvl + 1 as lvl,e.managerid mgrno, e.employeeid empno, e.first_name ename, e.title
from cte x join emp e on x.empno = e.managerid -- 把x.empno看作迭代变量字段，每次传入上一轮的结果行与目标表关联。
)
select * from cte;
示例2：递归计数，突破递归层次
WITH cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;
-- 会话级：打开递归层次的最大上限
alter session set 'CTE_MAXRECURSION'=4294967294;
示例3：重复拼接字符串
WITH cte(n, str) AS
(
SELECT 1 AS n, 'a' AS str -- 咱达梦有点优势：'字符' 字符常量的数据类型默认是varchar(32767)类型。
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 10
)
SELECT * FROM cte;
-- 即时函数
WITH FUNCTION sf_repeatstr(vstr varchar, n int) RETURN VARCHAR
AS
v_str VARCHAR := '';
BEGIN
IF (vstr IS NOT NULL) OR (vstr != '') THEN
WITH cte(lvl, str) AS
(
SELECT 1 AS lvl, vstr AS str
UNION ALL
SELECT lvl + 1, CONCAT(str, vstr) FROM cte WHERE lvl < n
)
SELECT TOP 1 str INTO v_str FROM cte ORDER BY lvl DESC;
END IF;
RETURN v_str;
END;
select sf_repeatstr('帅小宝', 3);
1500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
