DB2存储过程中使用WITH子句INSERT时添加TEMP_3块遇SQLCODE=-789错误

存储过程中执行以下代码时，只要不包含TEMP_3块就能正常运行，添加后触发错误：
参数或SQL变量'WITH TEMP AS ( SELECT * FROM TABLEE'的数据类型在例程、复合SQL语句或游标值构造函数的参数列表中不受支持。SQLCODE=-789, SQLSTATE=429BB, DRIVER=4.28.11

原代码 #

DECLARE C1 CURSOR FOR S1;
'WITH TEMP AS (
        SELECT *
        FROM TABLEE 
        WHERE ID = 2
        )), 
        TEMP_1 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_A (Col_1, Col_2)
        SELECT Col_1, 'A'
        FROM TABLE_A
        JOIN TABLEE ON ID = Col_1
        )),
        TEMP_2 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_B (Col_1, Col_2)
        SELECT Col_1, 'B'
        FROM TABLE_B
        JOIN TABLEE ON ID = Col_1
        )),
        TEMP_3 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_C (Col_1, Col_2)
        SELECT Col_1, 'C'
        FROM TABLE_C
        JOIN TABLEE ON ID = Col_1
        )),
        SELECT 1
        FROM SYSIBM.SYSDUMMY1';
  OPEN C1;
  CLOSE C1;

问题原因与修复 #

报错根源是CTE（公共表表达式）的语法错误：

• TEMP块结尾多了一个右括号：原代码里的))需改为)
• TEMP_3块结尾多余逗号，主查询SELECT 1 ...前不能保留逗号

另外原代码存在逻辑疏漏——DECLARE C1 CURSOR FOR S1中的S1未定义，需先通过PREPARE语句将动态SQL字符串绑定到S1，再声明游标。同时动态SQL内的单引号需要转义（用两个单引号替代一个）。

修正后的完整代码示例：

-- 准备动态SQL语句
PREPARE S1 FROM '
WITH TEMP AS (
        SELECT *
        FROM TABLEE 
        WHERE ID = 2
        ), 
        TEMP_1 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_A (Col_1, Col_2)
        SELECT Col_1, ''A''
        FROM TABLE_A
        JOIN TABLEE ON ID = Col_1
        )),
        TEMP_2 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_B (Col_1, Col_2)
        SELECT Col_1, ''B''
        FROM TABLE_B
        JOIN TABLEE ON ID = Col_1
        )),
        TEMP_3 AS (
        SELECT COUNT(1) AS ID FROM NEW TABLE (
        INSERT INTO TABLE_C (Col_1, Col_2)
        SELECT Col_1, ''C''
        FROM TABLE_C
        JOIN TABLEE ON ID = Col_1
        )
        SELECT 1
        FROM SYSIBM.SYSDUMMY1';

-- 声明并操作游标
DECLARE C1 CURSOR FOR S1;
OPEN C1;
CLOSE C1;

内容的提问来源于stack exchange，提问作者mastermistik