gpt4 book ai didi

sql - 从多个相关表中删除行

转载 作者:行者123 更新时间:2023-12-04 05:26:42 24 4
gpt4 key购买 nike

标识符上有三张表,一一对应。我需要从符合条件 A.ID = B.ID = C.ID 的三个表中删除所有记录

现在我按照以下方式进行:

DECLARE
CURSOR CUR IS
SELECT C.ID FROM A
INNER JOIN B ON A."ID" = B."ID"
INNER JOIN C ON B."ID" = C."ID"
WHERE A.STATUS = 'ERROR';
IDX NUMBER;
BEGIN
FOR REC IN CUR LOOP
IDX := REC.ID;
DELETE FROM C WHERE C."ID" = IDX;
DELETE FROM B WHERE B."ID" = IDX;
DELETE FROM A WHERE BP."ID" = IDX;
END LOOP;
COMMIT;
END;

大量数据,这种方式可以进行很长时间的运行。有什么办法可以更快的删除吗?

最佳答案

您可以创建一个 PL/SQL 类型来存储 ID。

CREATE TYPE t_ids AS TABLE OF NUMBER;

从表中删除所有记录 a匹配条件,并将 ID 返回到该类型的变量中。然后从 b 中删除所有记录和 c有了这些 ID。
DECLARE
ids_to_delete t_ids;
BEGIN
DELETE FROM a
WHERE a.status = 'ERROR'
AND EXISTS ( SELECT 1 FROM b WHERE b.id = a.id )
AND EXISTS ( SELECT 1 FROM c WHERE c.id = a.id )
RETURNING a.id
BULK COLLECT INTO ids_to_delete;

DELETE FROM b
WHERE id IN ( SELECT COLUMN_VALUE FROM TABLE( ids_to_delete ) );

DELETE FROM c
WHERE id IN ( SELECT COLUMN_VALUE FROM TABLE( ids_to_delete ) );
END;

这应该表现得更好,因为它不需要循环并且在三个 SQL 语句中完成所有事情,而不是每个 ID 三个语句。

关于sql - 从多个相关表中删除行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13133148/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com