使用游标复制表的字段到另一个表,根据id(双方的主键均为此名称)进行关联,z_n.departid的值有1,2,3三种,但结果更新的a_p.departid字段全部为1
这是为什么?
复制字段数值有什么更好的办法吗?谢谢!代码为:
create or replace procedure INSERTVALUES_TO_DEPART IS
departid_v NUMBER(15);
CURSOR FETCH_ZNAME_DATA IS
select z.departid from z_n z,a_p a where a.id=z.id;
BEGIN
IF FETCH_ZNAME_DATA%ISOPEN=FALSE THEN
OPEN FETCH_ZNAME_DATA;
END IF;
loop
fetch FETCH_ZNAME_DATA into departid_v;
exit when FETCH_ZNAME_DATA%NOTFOUND;
update a_p set departid=departid_v where exists (select 1 from a_p a,z_n z where z.id=a.id);
end loop;
close FETCH_ZNAME_DATA;
commit;
END INSERTVALUES_TO_DEPART;
这是为什么?
复制字段数值有什么更好的办法吗?谢谢!代码为:
create or replace procedure INSERTVALUES_TO_DEPART IS
departid_v NUMBER(15);
CURSOR FETCH_ZNAME_DATA IS
select z.departid from z_n z,a_p a where a.id=z.id;
BEGIN
IF FETCH_ZNAME_DATA%ISOPEN=FALSE THEN
OPEN FETCH_ZNAME_DATA;
END IF;
loop
fetch FETCH_ZNAME_DATA into departid_v;
exit when FETCH_ZNAME_DATA%NOTFOUND;
update a_p set departid=departid_v where exists (select 1 from a_p a,z_n z where z.id=a.id);
end loop;
close FETCH_ZNAME_DATA;
commit;
END INSERTVALUES_TO_DEPART;
set departid=(select departid from z_n where id=a_p.id)
where id in (select id from z_n);
SELECT a.departid, b.departid did
FROM a_p a, z_n b
WHERE a.id = b.id)
SET departid = did
departid_v NUMBER(15);
id_v integer;
CURSOR FETCH_ZNAME_DATA IS
select z.departid,z.id from z_n z,a_p a where a.id=z.id;
BEGIN
IF FETCH_ZNAME_DATA%ISOPEN=FALSE THEN
OPEN FETCH_ZNAME_DATA;
END IF;
loop
fetch FETCH_ZNAME_DATA into departid_v,id_v;
exit when FETCH_ZNAME_DATA%NOTFOUND;
update a_p a set a.departid=departid_v where a.id=v_id;
end loop;
close FETCH_ZNAME_DATA;
commit;
END INSERTVALUES_TO_DEPART;
using z_n z
on (a.id=z.id)
when matched then
update set a.departid=departid_v
wangrui4917 最符合我的实现过程