(目標TABLE資料較MAPPING 表資料少時使用)
--------目標TABLE------目標欄位----------新資料--------------MAPPING 表-------------目標欄位-----舊資料-----目標欄位---------
UPDATE USER_ROLE a SET a.U_ID=nvl((select new_empno from NEW_OLD_EMPNO_MAPPING b where a.U_ID=b.old_empno),a.u_id)
(目標TABLE資料較MAPPING 表資料多時使用)
CREATE OR REPLACE PROCEDURE proc_update IS
CURSOR c1
IS
SELECT OLD_DETAIL,NEW_DETAIL
FROM MAPPING;
v_c1 c1%rowtype;
BEGIN
open c1;
loop
fetch c1 into v_c1;
exit when c1%notfound ;
begin
update TARGET_TABLE
set emp_no=v_c1.NEW_DETAIL
where emp_no=v_c1.OLD_DETAIL;
exception when DUP_VAL_ON_INDEX then
null;
end;
commit;
end loop;
close c1;
commit;
END proc_update;