oracle - 多对多关系表不工作无法从中删除值
问题描述
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
. 之后它显示错误:------
*原因:外键值没有匹配的主键值。
*操作:删除外键或添加匹配的主键。
我有表employee[eid (primary key) ,ename]
和address[aid (primary
key),address]
多对多关系表employee_add[eid,aid]
。请帮忙!预先感谢 :)
解决方案
您只能使用一个loop
语句和variables
( v_address
and v_aid
) 在行之间进行比较,如下面的块中所示:
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;