时间戳

首页 > 解决方案 > 我想在下面存储的 PROC 中参数化 TABLE_TO_BE_DELETED 表

sql - 我想在下面存储的 PROC 中参数化 TABLE_TO_BE_DELETED 表

问题描述

但不是通过获取参数变量，我有一个表，其中包含所有表信息，我想一个接一个地获取表名完成删除获取另一个表名

开始声明SQLSTATE
CHAR(5) DEFAULT '00000';
loop1: WHILE SQLSTATE = '00000' DO
delete from ABC.TABLE_TO_BE_DELETED where ID in
(select ID from ABC.ID_DIM) FETCH FIRST 2000000 ROWS ONLY WITH UR;

IF SQLSTATE = '02000' THEN -- 未找到
LEAVE loop1;
万一;
犯罪;
结束；
结尾@

标签： sqlstored-proceduresdb2

解决方案

对于动态列表中多个表的大量删除行，如果我的目标正确的话。

BEGIN
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

  FOR V AS C1 CURSOR WITH HOLD FOR 
   SELECT 'DELETE FROM (SELECT 1 FROM ' || TABNAME || ' T WHERE EXISTS (SELECT 1 FROM ABC.ID_DIM D WHERE D.ID = T.ID) FETCH FIRST 2000000 ROWS ONLY WITH UR)' AS STMT 
   FROM TABLE_WITH_COLUMN_TABNAME
  DO
    L1: LOOP
      EXECUTE IMMEDIATE V.STMT;
      IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
      COMMIT;
    END LOOP;
  END FOR;
END@

推荐阅读