DB2在存储过程的插入中使用With子句

rsaldnfx  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(221)

我在存储过程中使用下面的代码。只要我不使用TEMP_3的块,它就工作正常。我得到了这样的错误

在游标值构造函数的例程、复合SQL语句或参数列表中不支持参数或SQL变量“'WITH TEMP AS(SELECT * FROM TABLEE”的数据类型。SQLCODE= -789,SQLSTATE= 429 BB,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;
ltskdhd1

ltskdhd11#

错误太多。
仅举一个目标表示例。

BEGIN
  DECLARE C1 CURSOR FOR S1;
  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
     )
  )
  SELECT 1
  FROM SYSIBM.SYSDUMMY1';
  OPEN C1;
  CLOSE C1;
END

请提供完整的示例以及相应的CREATE TABLEINSERT INTO声明以及this示例。

相关问题