如何根据表 FK 关系在 PL/SQL 中生成 DELETE 语句? [英] How to generate DELETE statements in PL/SQL, based on the tables FK relations?

问题描述

是否可以通过脚本/工具使用 Oracle PL/SQL 自动生成多个基于表 fk 关系的删除语句?

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

在示例中:我有一个表:CHICKEN (CHICKEN_CODE NUMBER) 并且有 30 个表对其 CHICKEN_CODE 的 fk 引用，我需要删除；还有其他 150 个表外键链接到我需要先删除的那 30 个表.

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.

是否有一些我可以运行的工具/脚本 PL/SQL，以便为我生成基于​​ FK 关系的所有必要的删除语句?

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!)

我使用的是 Oracle 数据库 10G R2.

I'm using Oracle DataBase 10G R2.

请注意:

从外部生成删除语句SQL 2008 中的关键关系?

另一个用户刚刚在 SQL SERVER 2008 中编写了它，任何人都可以转换为 Oracle 10G PL/SQL?我无法... :-(

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

请假设 V_CHICKEN 和 V_NATION 是从根表中选择要删除的 CHICKEN 的条件:条件是:根表上的其中 COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION".

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.

推荐答案

(我的第一个答案变得太长而且难以编辑，并且它得到了社区维基化，这真的很烦人.这是最新版本的脚本.)

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

此脚本尝试通过递归执行级联删除.当存在循环引用时，应避免无限循环.但它要求所有循环引用约束都具有ON DELETE SET NULL 或ON DELETE CASCADE.

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 NULL or 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;

这篇关于如何根据表 FK 关系在 PL/SQL 中生成 DELETE 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！