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

Question

在oracle 数据库上，我有一个外键，不知道它的名字，只有column_name 和reference_column_name。
我想编写一个 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;

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

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;

然后我得到了这个:

Error report: ORA-06550: line 9, column 5: PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

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

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

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');

输出是:

Error report: SQL Error: ORA-02250: missing or invalid constraint name 02250. 00000 - "missing or invalid constraint name" *Cause: The constraint name is missing or invalid. *Action: Specify a valid identifier name for the constraint name.

对于 sql server (MS SQL)，这很容易做到。只需用@ 声明一个变量并设置它，然后就可以使用它。在 oracle 上，我不知道它不起作用的地方...

Answer 1

你的原始版本基本没问题，只是你不能在 PL/SQL 块中直接执行 DDL；相反，您必须将其包装在 EXECUTE IMMEDIATE 中:

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

即使在编译时就知道约束名称，这也是正确的，但在您的情况下更是如此，因为 fName不是约束名称，而是包含约束名称的变量。

还有这个:

if (fName != '') THEN

无效/有意义，因为在 Oracle '' 中意味着 NULL .你应该写

IF fName IS NOT NULL THEN

反而。