oracle 如何根据表 FK 关系在 PL/SQL 中生成 DELETE 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2677081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to generate DELETE statements in PL/SQL, based on the tables FK relations?
提问by UltraCommit
Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL?
是否可以通过脚本/工具使用 Oracle PL/SQL 基于表 fk 关系自动生成许多删除语句？
In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first.
在示例中：我有表：CHICKEN (CHICKEN_CODE NUMBER) 并且有 30 个表具有对其 CHICKEN_CODE 的 fk 引用，我需要删除；还有其他 150 个表外键链接到我需要先删除的 30 个表。
Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me?
是否有一些我可以运行的工具/脚本 PL/SQL，以便根据 FK 关系为我生成所有必要的删除语句？
(by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!)
（顺便说一下，我知道关系上的级联删除，但请注意：我不能在我的生产数据库中使用它，因为它很危险！）
I'm using Oracle DataBase 10G R2.
我正在使用 Oracle 数据库 10G R2。
Please pay attention to this:
请注意这一点：
Generate Delete Statement From Foreign Key Relationships in SQL 2008?
Another user has just written it in SQL SERVER 2008, anyone is able to convert to Oracle 10G PL/SQL? I am not able to... :-(
另一个用户刚刚在 SQL SERVER 2008 中编写它，任何人都可以转换为 Oracle 10G PL/SQL？我不能... :-(
Please assume that V_CHICKEN and V_NATION are the criteria to select the CHICKEN to delete from the root table: the condition is: "where COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION" on the root table.
请假设 V_CHICKEN 和 V_NATION 是从根表中选择要删除的 CHICKEN 的条件：条件是：根表上的“其中 COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION”。
回答by Igby Largeman
(My first answer became too long and difficult to edit, and it got Community Wikified, which is really annoying. Here is the latest version of the script.)
（我的第一个答案变得太长且难以编辑，并且得到了社区维基化，这真的很烦人。这是脚本的最新版本。）
This script attempts to perform a cascading delete through recursion. It should avoid infinite loops when there are circular references. But it requires that all circular referential constraints have ON DELETE SET NULLor ON DELETE CASCADE.
此脚本尝试通过递归执行级联删除。当存在循环引用时，应避免无限循环。但它要求所有循环引用约束都具有ON DELETE SET NULL或ON DELETE CASCADE。
CREATE OR REPLACE PROCEDURE delete_cascade(
table_owner VARCHAR2,
parent_table VARCHAR2,
where_clause VARCHAR2
) IS
/* Example call: execute delete_cascade('MY_SCHEMA', 'MY_MASTER', 'where ID=1'); */
child_cons VARCHAR2(30);
parent_cons VARCHAR2(30);
child_table VARCHAR2(30);
child_cols VARCHAR(500);
parent_cols VARCHAR(500);
delete_command VARCHAR(10000);
new_where_clause VARCHAR2(10000);
/* gets the foreign key constraints on other tables which depend on columns in parent_table */
CURSOR cons_cursor IS
SELECT owner, constraint_name, r_constraint_name, table_name, delete_rule
FROM all_constraints
WHERE constraint_type = 'R'
AND delete_rule = 'NO ACTION'
AND r_constraint_name IN (SELECT constraint_name
FROM all_constraints
WHERE constraint_type IN ('P', 'U')
AND table_name = parent_table
AND owner = table_owner)
AND NOT table_name = parent_table; -- ignore self-referencing constraints
/* for the current constraint, gets the child columns and corresponding parent columns */
CURSOR columns_cursor IS
SELECT cc1.column_name AS child_col, cc2.column_name AS parent_col
FROM all_cons_columns cc1, all_cons_columns cc2
WHERE cc1.constraint_name = child_cons
AND cc1.table_name = child_table
AND cc2.constraint_name = parent_cons
AND cc1.position = cc2.position
ORDER BY cc1.position;
BEGIN
/* loops through all the constraints which refer back to parent_table */
FOR cons IN cons_cursor LOOP
child_cons := cons.constraint_name;
parent_cons := cons.r_constraint_name;
child_table := cons.table_name;
child_cols := '';
parent_cols := '';
/* loops through the child/parent column pairs, building the column lists of the DELETE statement */
FOR cols IN columns_cursor LOOP
IF child_cols IS NULL THEN
child_cols := cols.child_col;
ELSE
child_cols := child_cols || ', ' || cols.child_col;
END IF;
IF parent_cols IS NULL THEN
parent_cols := cols.parent_col;
ELSE
parent_cols := parent_cols || ', ' || cols.parent_col;
END IF;
END LOOP;
/* construct the WHERE clause of the delete statement, including a subquery to get the related parent rows */
new_where_clause :=
'where (' || child_cols || ') in (select ' || parent_cols || ' from ' || table_owner || '.' || parent_table ||
' ' || where_clause || ')';
delete_cascade(cons.owner, child_table, new_where_clause);
END LOOP;
/* construct the delete statement for the current table */
delete_command := 'delete from ' || table_owner || '.' || parent_table || ' ' || where_clause;
-- this just prints the delete command
DBMS_OUTPUT.put_line(delete_command || ';');
-- uncomment if you want to actually execute it:
--EXECUTE IMMEDIATE delete_command;
-- remember to issue a COMMIT (not included here, for safety)
END;
回答by Gary Myers
The problem is if the top level key column isn't propagated all the way down to the bottom. If you can do DELETE FROM grandchild WHERE parent_id = :1, it is fine. If you have to do,
问题是顶级键列是否没有一直向下传播到底部。如果你可以从孙子那里删除 parent_id = :1，那就没问题了。如果你必须这样做，
DELETE FROM grandchild
WHERE child_id in (SELECT id FROM child WHERE parent_id = :1)
then going down six or seven deep will give you ugly (and probably slow) queries.
然后深入六到七层会给你带来丑陋（而且可能很慢）的查询。
While you said you can't make the constraints CASCADE, can you make them deferrable initally immediate ? That way existing code should not be impacted. Your 'delete' session would make all constraints deferred. Then delete from the parent, delete from the child where the record wasn't in the parent, delete from the grandchild where there's no match in the child etc...
虽然你说你不能让约束级联，你能不能让它们一开始就立即延迟？这样现有的代码不应该受到影响。您的“删除”会话将推迟所有约束。然后从父级中删除，从父级中没有记录的子级中删除，从子级中没有匹配项的孙子中删除等等......
回答by Igby Largeman
This is a great exercise in developing your PL/SQL skills and general Oracle knowledge!
这是培养您的 PL/SQL 技能和一般 Oracle 知识的绝佳练习！
You need to identify all constrained columns in all tables with relations descending from your master table. You can get all the information you need from two views: ALL_CONSTRAINTSand ALL_CONS_COLUMNS. (If all the tables are in the same schema as the user executing the script, you can use USER_CONSTRAINTS and USER_CONS_COLUMNS if you prefer)
您需要使用从主表降序的关系来标识所有表中的所有约束列。您可以从两个视图中获取所需的所有信息：ALL_CONSTRAINTS和ALL_CONS_COLUMNS。（如果所有表与执行脚本的用户都在同一架构中，您可以根据需要使用 USER_CONSTRAINTS 和 USER_CONS_COLUMNS）
This query will find all the foreign key constraints which refer back to a given table (CUSTOMERin this example):
此查询将找到所有引用回给定表的外键约束（CUSTOMER在本例中）：
SELECT constraint_name, table_name, constraint_type
FROM all_constraints
WHERE constraint_type = 'R'
AND r_constraint_name IN (SELECT constraint_name
FROM all_constraints
WHERE constraint_type IN ('P', 'U')
AND table_name = 'CUSTOMER');
CONSTRAINT_NAME C
------------------------------ -
CUSTOMER_FK1 R
CUSTOMER_FK4 R
CUSTOMER_FK5 R
CUSTOMER_FK3 R
CUSTOMER_FK2 R
Now, for each of the results from that query, you can use the CONSTRAINT_NAMEcolumn to get a table and column name which you can use to write DELETE statements to delete all child rows in all child tables.
现在，对于该查询的每个结果，您可以使用该CONSTRAINT_NAME列获取一个表和列名，您可以使用它们来编写 DELETE 语句以删除所有子表中的所有子行。
This example gets the table and column name for a constraint called CUSTOMER_FK1
此示例获取名为的约束的表和列名称 CUSTOMER_FK1
SELECT table_name, column_name
FROM user_cons_columns
WHERE constraint_name = 'CUSTOMER_FK1'
TABLE_NAME COLUMN_NAME
----------------------------- ------------------------------------
RESERVATION CUSTOMER_UID
So you could do, for example:
所以你可以这样做，例如：
DELETE FROM reservation
WHERE customer_uid = 00153464
or
或者
DELETE FROM reservation
WHERE customer_uid IN (SELECT customer_uid
FROM customer
WHERE customer_type = 'X')
But your child tables also have child tables, so of course you will have to delete thosechild rows (call them grandchild rows) first. Supposing there is a table called reservation_detail which has a foreign key relationship with reservation, your delete command for reservation_detail might look like:
但是您的子表也有子表，因此您当然必须先删除这些子行（称为孙行）。假设有一个名为reservation_detail 的表与reservation 有外键关系，则reservation_detail 的删除命令可能如下所示：
DELETE FROM reservation_detail
WHERE reservation_uid in (SELECT reservation_uid
FROM reservation
WHERE customer_uid IN (SELECT customer_uid
FROM customer
WHERE customer_type = 'X')
And if reservation_detail also has children... you get the idea. Of course you could use joins instead of nested queries, but the principle is the same: the more levels deep your dependencies go, the more complex your delete commands become.
如果reservation_detail 也有孩子……你懂的。当然，您可以使用连接而不是嵌套查询，但原理是相同的：依赖关系越深，删除命令就越复杂。
So now you know how to do it, the challenge is to write a generic PL/SQL script to delete all child rows, grandchild rows, great-grandchild rows ... (ad infinitum) for any given table, from the bottom up. You will have to employ recursion. Should be a fun program to write!
所以现在你知道怎么做了，挑战是编写一个通用的 PL/SQL 脚本来删除任何给定表的所有子行、孙行、曾孙行......（无限），从下到上。您将不得不使用递归。应该是一个有趣的程序编写！
(Last edit: removed the script; see my other answer for the final solution.)
（上次编辑：删除了脚本；有关最终解决方案，请参阅我的其他答案。）
