oracle select into variable 当 select 什么都不返回时？

Question

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 is not null THEN
  execute immediate 'alter table MY_TABLE_NAME drop constraint ' || fName;
 end if;

 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 = 'OTHER_MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='OTHER_MY_COLUMN_NAME';
 if fName is not null THEN
  execute immediate 'alter table OTHER_MY_TABLE_NAME drop constraint ' || fName;
 end if;
end;

你好 @，
我正在使用上面的代码来获取我想要删除的约束的名称，并且我有很多这样的 select into 和 than if -> drop 语句。
我的问题是，如果其中一个选择不返回任何内容，则会引发异常。我可以捕获异常，但在“开始结束”结构的末尾(因此在所有选择语句之后，其余的放置将永远不会被执行)。我该如何安排，如果选择不返回任何内容，我只是不想丢弃任何内容:)

如果有另一种方法来定义一个变量并从 select 中填充它而不抛出异常，无论返回的名称是否为 null，我都更喜欢它:)(现在我只是让这个 select 开始工作，除了选择不返回任何内容的情况:))

Answer 1

使用多个开始/异常/结束块:

declare
 fName varchar2(255 char);
begin
 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';
exception
  when no_data_found then
    fName := null;
end;

 if fName is not null THEN
  execute immediate 'alter table MY_TABLE_NAME drop constraint ' || fName;
 end if;

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 = 'OTHER_MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='OTHER_MY_COLUMN_NAME';
exception
  when no_data_found then
    fName := null;
end;

 if fName is not null THEN
  execute immediate 'alter table OTHER_MY_TABLE_NAME drop constraint ' || fName;
 end if;
end;