选择返回时没有oracle select into变量？

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而不是if – >删除声明.
我的问题是,如果其中一个选择没有返回任何异常,则抛出异常.我可以捕获异常,但是在“begin end”结构的末尾(所以在所有select语句之后,所以其余的drop永远不会被执行).我怎么能安排这个,如果选择返回什么我只是不想丢掉任何东西:)

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

解决方法

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;

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!