oracle - 在 oracle DB 中创建用于查找和删除特定表的外键约束的过程

Question

我是 Oracle 新手，我正在尝试在 Oracle 中编写一个过程来删除表的外键约束。我已经为 MySQL 及其工作做了这个。我不确定语法，为此道歉，但如果我单独运行它，我的查询正在工作。我想为多个表做同样的事情(删除外键约束)并且不想多次编写查询。因此，首先我要找到与该表关联的外键，将它们存储在游标中，然后通过创建和执行与该表关联的删除约束查询来删除所有外键。以下代码给了我多个错误。

CREATE OR REPLACE PROCEDURE removeConstraintsForTable(vTableName IN varchar2) IS
BEGIN
cName VARCHAR(2048);
sql_stmt VARCHAR2(2048);
CURSOR cur IS
          SELECT DISTINCT CONSTRAINT_NAME
          FROM ALL_CONSTRAINTS WHERE OWNER= sys_context('userenv','current_schema')
          AND TABLE_NAME = vTableName AND CONSTRAINT_TYPE='R';
BEGIN
    OPEN cur;
    LOOP
    FETCH cur INTO cName;
    EXIT WHEN cur%notfound;
    sql_stmt := CONCAT(CONCAT(CONCAT('ALTER TABLE ',vTableName),CONCAT(' DROP FOREIGN KEY ',cName)),';');
    SELECT sql_stmt FROM dual;
    INSERT INTO TEMP(Name) VALUES(sql_stmt);
    COMMIT;
    END LOOP;
END
/


CALL removeConstraintsForTable('table1');
CALL removeConstraintsForTable('table2');
CALL removeConstraintsForTable('table3');
CALL removeConstraintsForTable('table4');
COMMIT;

Answer 1

您在程序的开头有一个额外的 BEGIN，而最后的 END 缺少一个分号。您实际上不应该使用 VARCHAR，并且无论如何您都可以使用数据字典声明 cName 变量；然而，隐式循环会更简单，因为使用连接运算符 || 而不是嵌套的 CONCAT() 调用，生成的语句不应以分号结尾:

create or replace procedure removeconstraintsfortable(p_table_name in varchar2) is
  sql_stmt varchar2(2048);
begin
  for rec in (
    select owner, constraint_name
    from all_constraints
    where owner = sys_context('userenv','current_schema')
    and table_name = p_table_name
    and constraint_type = 'R'
  )
  loop
    sql_stmt := 'ALTER TABLE "' || rec.owner || '"."' || p_table_name || '"'
      || ' DROP CONSTRAINT "' || rec.constraint_name || '"';

    insert into temp(name) values(sql_stmt);
  end loop;
  commit;
end;
/

正如评论中指出的那样，生成的语句应该是drop constraint。

我不确定为什么要插入到表中或执行语句的位置，但如果您愿意，可以一次完成所有操作:

create or replace procedure removeconstraintsfortable(p_table_name in varchar2) is
  sql_stmt varchar2(2048);
begin
  for rec in (
    select owner, constraint_name
    from all_constraints
    where owner = sys_context('userenv','current_schema')
    and table_name = p_table_name
    and constraint_type = 'R'
  )
  loop
    sql_stmt := 'ALTER TABLE "' || rec.owner || '"."' || p_table_name || '"'
      || ' DROP CONSTRAINT "' || rec.constraint_name || '"';

    dbms_output.put_line(sql_stmt);
    execute immediate sql_stmt;
  end loop;
end;
/

dbms_output 调用只是向您显示生成的语句，然后 execute immediate 立即执行它。

快速演示；非常基本的表格设置:

create table t42 (id number primary key);
create table t43 (id number references t42 (id));

select table_name, constraint_name, constraint_type
from all_constraints
where table_name in ('T42', 'T43');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T43                            SYS_C00138153                  R
T42                            SYS_C00138152                  P

然后调用显示生成语句的过程:

set serveroutput on
exec removeConstraintsForTable('T43');

ALTER TABLE "STACKOVERFLOW"."T43" DROP CONSTRAINT "SYS_C00138153"

PL/SQL procedure successfully completed.

然后检查约束已经消失:

select table_name, constraint_name, constraint_type
from all_constraints
where table_name in ('T42', 'T43');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
T42                            SYS_C00138152                  P