在不知道 Oracle 名称的情况下删除索引或约束

Question

在 Oracle 数据库上，我有一个外键，但不知道其名称，只知道列名和引用列名。 我想编写一个sql脚本，如果存在的话应该删除这个外键，所以这是我使用的代码：

declare
 fName varchar2(255 char);
begin
 SELECT x.constraint_name into fName FROM all_constraints x
 JOIN all_cons_columns c ON
 c.table_name = x.table_name AND c.constraint_name = x.constraint_name
 WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME';
end;

这个脚本的输出是“匿名块完成”，所以它是成功的，但是当我添加删除部分时：

declare
 fName varchar2(255 char);
begin
 SELECT x.constraint_name into fName FROM all_constraints x
 JOIN all_cons_columns c ON
 c.table_name = x.table_name AND c.constraint_name = x.constraint_name
 WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME';
 if (fName != '') THEN
  alter table MY_TABLE_NAME drop constraint fName;
 end if;
end;

然后我得到这个：

错误报告：ORA-06550：第 9 行，第 5 列：PLS-00103：遇到 当期望出现以下情况之一时，符号“ALTER”：

begin case 声明 goto 退出 ifloop mod null pragma raise 返回选择更新，同时使用 <<关闭当前删除 获取锁插入打开回滚保存点设置sql执行提交 forall合并管道 06550. 00000 - “第 %s 行，第 %s 列：\n%s” *原因：通常是 PL/SQL 编译错误。 *行动：

谁能告诉我这里有什么问题吗？

我还尝试将所有内容放入一个函数中：

declare
  function getFName return varchar2 is
    fName varchar2(255 char);
  begin
   SELECT x.constraint_name into fName FROM all_constraints x
   JOIN all_cons_columns c ON
   c.table_name = x.table_name AND c.constraint_name = x.constraint_name
   WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME';

   return fName;
  end;
begin
  if getFName() != '' then
   alter table all_events drop constraint getFName(); 
  end if;
end; 

结果是由语句“alter table”引起的相同错误

这也没有帮助：

alter table all_events drop constraint
   (SELECT x.constraint_name into fName FROM all_constraints x
   JOIN all_cons_columns c ON
   c.table_name = x.table_name AND c.constraint_name = x.constraint_name
   WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME');

输出是：

错误报告：SQL 错误：ORA-02250：约束名称缺失或无效 02250. 00000 - “约束名称缺失或无效” *原因：约束名称丢失或无效。 *操作：为约束名称指定有效的标识符名称。

对于 sql 服务器（MS SQL）来说，做到这一点非常容易。只需用 @ 声明一个变量并设置它，然后使用它即可。在 oracle 上我不知道它不起作用......

Answer 1

您的原始版本大部分都很好，只是您不能直接在 PL/SQL 块中执行 DDL；相反，您必须将其包装在 EXECUTE IMMEDIATE 中：

execute immediate 'alter table MY_TABLE_NAME drop constraint "' || fName || '"';

即使约束名称在编译时已知，这也是正确的，但在您的情况下它是双重正确的，因为 fName< /code> 不是约束名称，而是包含约束名称的变量。

另外，这个：

if (fName != '') THEN

无效/没有意义，因为在 Oracle 中 '' 表示 NULL。你应该写

IF fName IS NOT NULL THEN

。

Answer 2

这是删除列的所有类型为“R”的约束的方法：

begin
    FOR rec IN (SELECT x.constraint_name fName FROM all_constraints x
        JOIN all_cons_columns c ON
        c.table_name = x.table_name AND c.constraint_name = x.constraint_name
        WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME')
    LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE_NAME DROP CONSTRAINT "' || rec.fName || '"';
    END LOOP;
end;