多对多关系表不起作用无法从中删除值 [英] Many to Many Relationship table Not working cannot delete Value from that

问题描述

declare
begin
  for i in (select aid ,address from address)
  loop
    for j in (select aid ,address from address )
    loop
      if i.address=j.address then
        if i.aid!=j.aid then
          update employee_add 
          set aid=i.aid 
          where aid=j.aid;
          delete from address 
          where aid=i.aid;
        end if;
      end if; 
    end loop;
  end loop;
end;
/

此代码可以作为for loop正常工作.之后，它显示错误:------

This code works fine as for loop. After that it shows error :------

*原因:外键值没有匹配的主键值.
*操作:删除外键或添加匹配的主键.

*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

我有表employee[eid (primary key) ,ename]，address[aid (primary key),address]和多对多关系表employee_add[eid,aid]. 请帮忙！在此先感谢:)

I have tables employee[eid (primary key) ,ename] ,address[aid (primary key),address],and many to many relation tableemployee_add[eid,aid]. Please help! Thank in Advance :)

推荐答案

您只能使用一个loop语句和variables(v_address和v_aid)在行之间进行比较，如下所示:在下面的块中:

You can use only one loop statement and variables( v_address and v_aid ) to make a comparison between the rows as in the block below :

declare
  v_address address.address%type;
  v_aid     address.aid%type;  
begin
 for i in (select aid ,address from address order by aid)
 loop  
    if nvl(v_address,'')=i.address then 
         update employee_add set aid=v_aid where aid=i.aid;  
         delete address where aid=i.aid;
    else
       v_address := i.address;
       v_aid := i.aid;   
    end if;
  end loop;
end;

这篇关于多对多关系表不起作用无法从中删除值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！