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

Question

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

BEGIN

声明 SQLSTATECHAR(5( 默认值 '00000';
loop1： 而 SQLSTATE = '00000' DO
delete from ABC.TABLE_TO_BE_DELETED 其中 ID 在
(从 ABC.ID_DIM 中选择 ID (仅获取前 2000000 行;

如果 SQLSTATE = '02000' 则 -- 未找到

LEAVE loop1;
END IF;
COMMIT;
END WHILE;
END@

Answer 1

对于从动态列表中多个表的块中大量删除行，如果我正确无误地为您提供了目标。

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@

Answer 2

我要做的是在游标中获取表的结果，然后循环它们

以下几行

：

DECLARE Tables_cursor CURSOR FOR SELECT table_name FROM table_of_tables
OPEN Tables_cursor
FETCH NEXT FROM Tables_cursor INTO @table_name
DELETE FROM @table_name where ID in
(select ID from ABC.ID_DIM ) FETCH FIRST 2000000 ROWS ONLY WITh UR;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Tables_cursor INTO @table_name
END 

我从未使用过，但我认为它会是这样的：

EXEC SQL DECLARE C1 CURSOR FOR
SELECT tablename  FROM table_of_tables
EXEC SQL OPEN C1;
DO WHILE (SQLCODE = 0);
EXEC SQL FETCH C1 INTO :TABLE_NAME
DELETE FROM TABLE_NAME as curentTable where ID in
(select ID from ABC.ID_DIM ) FETCH FIRST 2000000 ROWS ONLY WITh UR;
END;
EXEC SQL CLOSE C1;