blocks|key|2897973|text|这是开发您的PL/SQL技能和一般Oracle知识的一个很好的练习！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2897974|您需要标识所有表中具有从主表降级的关系的所有受约束的列。您可以从两个视图获取所需的所有信息：ALL_CONSTRAINTS和ALL_CONS_COLUMNS。(如果所有表都与执行脚本的用户位于相同的模式中，则可以根据需要使用USER_CONSTRAINTS和USER_CONS_COLUMNS+)|offset|length|2897975|此查询将查找引用回给定表的所有外键约束(本例中为CUSTOMER)：|style|CODE|2897976|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|code-block|syntax|javascript|2897977|现在，对于该查询的每个结果，您可以使用CONSTRAINT_NAME列来获取表和列名，您可以使用它们来编写DELETE语句，以删除所有子表中的所有子行。|2897978|此示例获取名为CUSTOMER_FK1的约束的表名和列名|2897979|SELECT+table_name,+column_name
++FROM+user_cons_columns
+WHERE+constraint_name+=+'CUSTOMER_FK1'

TABLE_NAME++++++++++++++++++++COLUMN_NAME+++++++++++++++++++++++
-----------------------------+------------------------------------
RESERVATION+++++++++++++++++++CUSTOMER_UID|2897980|所以你可以这样做，例如：|2897981|DELETE+FROM+reservation
+WHERE+customer_uid+=+00153464|2897982|或|2897983|DELETE+FROM+reservation
+WHERE+customer_uid+IN+(SELECT+customer_uid
++++++++++++++++++++++++++FROM+customer
+++++++++++++++++++++++++WHERE+customer_type+=+'X')|2897984|但是您的子表也有子表，所以您当然必须首先删除那些子行(称为子行)。假设有一个名为reservation_detail的表，它与保留具有外键关系，reservation_detail的delete命令可能如下所示：|2897985|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')|2897986|如果reservation_detail也有孩子..。你明白了吧。当然，您可以使用连接而不是嵌套查询，但原理是相同的:依赖关系的层次越深，delete命令就变得越复杂。|2897987|所以现在你知道如何做了，挑战是编写一个通用的PL/SQL脚本来删除所有子行、孙行、曾子行……(ad+infinitum)对于任何给定表，从下到上。您将不得不使用recursion。写起来应该是一个有趣的程序！|2897988|(最后一次编辑:删除了脚本；有关最终解决方案，请参阅我的另一个答案。)|2897989|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#sthref927|1|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1035.htm#sthref923|2|http://en.wikipedia.org/wiki/Recursion_%2528computer_science%2529^0|0|1A|F|0|1Q|G|1|0|O|8|0|0|J|F|0|7|C|0|0|0|0|0|0|0|0|0|28|9|2|0|0^^$0|@$1|2|3|4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1P|8|@]|9|@$D|1Q|E|1R|1|1S]|$D|1T|E|1U|1|1V]]|A|$]]|$1|F|3|G|5|6|7|1W|8|@$D|1X|E|1Y|H|I]]|9|@]|A|$]]|$1|J|3|K|5|L|7|1Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|20|8|@$D|21|E|22|H|I]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|23|8|@$D|24|E|25|H|I]]|9|@]|A|$]]|$1|S|3|T|5|L|7|26|8|@]|9|@]|A|$M|N]]|$1|U|3|V|5|6|7|27|8|@]|9|@]|A|$]]|$1|W|3|X|5|L|7|28|8|@]|9|@]|A|$M|N]]|$1|Y|3|Z|5|6|7|29|8|@]|9|@]|A|$]]|$1|10|3|11|5|L|7|2A|8|@]|9|@]|A|$M|N]]|$1|12|3|13|5|6|7|2B|8|@]|9|@]|A|$]]|$1|14|3|15|5|L|7|2C|8|@]|9|@]|A|$M|N]]|$1|16|3|17|5|6|7|2D|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|2E|8|@]|9|@$D|2F|E|2G|1|2H]]|A|$]]|$1|1A|3|1B|5|6|7|2I|8|@]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|2J|8|@]|9|@]|A|$]]]|1D|$1E|$5|1F|1G|1H|A|$1I|1J]]|1K|$5|1F|1G|1H|A|$1I|1L]]|1M|$5|1F|1G|1H|A|$1I|1N]]]]

This is a great exercise in developing your PL/SQL skills and general Oracle knowledge!

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: <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#sthref927" rel="nofollow noreferrer">ALL_CONSTRAINTS</a> and <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1035.htm#sthref923" rel="nofollow noreferrer">ALL_CONS_COLUMNS</a>. (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)

This query will find all the foreign key constraints which refer back to a given table (<code>CUSTOMER</code> in this example):

<pre><code>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
</code></pre>

Now, for each of the results from that query, you can use the <code>CONSTRAINT_NAME</code> column to get a table and column name which you can use to write DELETE statements to delete all child rows in all child tables. 

This example gets the table and column name for a constraint called <code>CUSTOMER_FK1</code>

<pre><code>SELECT table_name, column_name
 FROM user_cons_columns
 WHERE constraint_name = 'CUSTOMER_FK1'

TABLE_NAME COLUMN_NAME 
----------------------------- ------------------------------------
RESERVATION CUSTOMER_UID
</code></pre>

So you could do, for example:

<pre><code>DELETE FROM reservation
 WHERE customer_uid = 00153464
</code></pre>

or

<pre><code>DELETE FROM reservation
 WHERE customer_uid IN (SELECT customer_uid
 FROM customer
 WHERE customer_type = 'X')
</code></pre>

But your child tables also have child tables, so of course you will have to delete those child 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:

<pre><code>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')
</code></pre>

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.

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 <a href="http://en.wikipedia.org/wiki/Recursion_%28computer_science%29" rel="nofollow noreferrer">recursion</a>. Should be a fun program to write!

(Last edit: removed the script; see my other answer for the final solution.)

blocks|key|5215185|text|问题是，如果顶级键列没有一直传播到底部。如果你可以从parent_id+=+:1的孙子文件中删除，那就好了。如果你非做不可，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5215186|DELETE+FROM+grandchild
WHERE+child_id+in+(SELECT+id+FROM+child+WHERE+parent_id+=+:1)|code-block|syntax|javascript|5215187|然后，深入6到7层将给出难看的(并且可能很慢的)查询。|5215188|虽然你说你不能使约束级联，但你能让它们在初始时立即延迟吗？这样就不会影响现有的代码。您的“删除”会话将使所有约束被推迟。然后从父记录中删除，从子记录不在父记录中的子项中删除，从子记录中没有匹配的子项中删除，等等。|5215189|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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, 

<pre><code>DELETE FROM grandchild
WHERE child_id in (SELECT id FROM child WHERE parent_id = :1)
</code></pre>

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...

blocks|key|5215203|text|(我的第一个答案变得太长，很难编辑，它得到了社区维基，这真的很烦人。以下是该脚本的最新版本。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5215204|此脚本尝试通过递归执行级联删除。当有循环引用时，它应该避免无限循环。但它要求所有循环引用约束都具有ON+DELETE+SET+NULL或ON+DELETE+CASCADE。|offset|length|style|CODE|5215205|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+%7C%7C+',+'+%7C%7C+cols.child_col;
++++++++++++END+IF;

++++++++++++IF+parent_cols+IS+NULL+THEN
++++++++++++++++parent_cols++:=+cols.parent_col;
++++++++++++ELSE
++++++++++++++++parent_cols++:=+parent_cols+%7C%7C+',+'+%7C%7C+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+('+%7C%7C+child_cols+%7C%7C+')+in+(select+'+%7C%7C+parent_cols+%7C%7C+'+from+'+%7C%7C+table_owner+%7C%7C+'.'+%7C%7C+parent_table+%7C%7C
++++++++++++'+'+%7C%7C+where_clause+%7C%7C+')';

++++++++delete_cascade(cons.owner,+child_table,+new_where_clause);
++++END+LOOP;

++++/*+construct+the+delete+statement+for+the+current+table+*/
++++delete_command++:=+'delete+from+'+%7C%7C+table_owner+%7C%7C+'.'+%7C%7C+parent_table+%7C%7C+'+'+%7C%7C+where_clause;

++++--+this+just+prints+the+delete+command
++++DBMS_OUTPUT.put_line(delete_command+%7C%7C+';');

++++--+uncomment+if+you+want+to+actually+execute+it:
++++--EXECUTE+IMMEDIATE+delete_command;

++++--+remember+to+issue+a+COMMIT+(not+included+here,+for+safety)
END;|code-block|syntax|javascript|5215206|entityMap^0|0|1D|I|1W|H|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

(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 <code>ON DELETE SET NULL</code> or <code>ON DELETE CASCADE</code>.

<pre><code>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;
</code></pre>

Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL?

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.

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?

(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.

Please pay attention to this:

<a href="https://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008/2677145#2677145">Generate Delete Statement From Foreign Key Relationships in SQL 2008?</a>

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... :-(

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.

How to generate DELETE statements in PL/SQL, based on the tables FK relations?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

是否可以通过脚本/工具使用Oracle PL/SQL根据表fk关系自动生成多个delete语句？在示例中:我有一个表: first (CHICKEN_CODE NUMBER)，有30个表具有对其CHICKEN_CODE的fk引用，我需要删除这些表；还有其他150个表与这30个表有外键链接，我需要先删除它们。有没有我可以...

问如何在PL/SQL中根据表的FK关系生成DELETE语句？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PL/SQL中根据表的FK关系生成DELETE语句？EN