Need advise from PL/SQL experts on script

From: LGold <leighlhg_at_home.com>
Date: Wed, 14 Feb 2001 04:47:26 GMT
Message-ID: <y5oi6.3141$RU.116774@news1.rdc1.ab.home.com>

Hi there,

I need some advise from any PL/SQL expert regarding the PL/SQL script below. What I am trying to accomplish is to change customer number in a table to a new number. This new number is stored in a mapping table that consists of 2 columns, one stores the old customer, and the other, the new customer number. I am new in PL/SQL and would like comments on the script below. My concern is if I have a mapping table with data like this (see below), will the conversion be done twice to the row that has old customer number of 333? If it has been converted to 456, will it run the risk of being converted to 777 after that?

map table structure - old_cust, new_cust

                                        333        456
                                        444        457
                                        456        777

DECLARE
  cursor c1 is select a.rowid, b.new_cust from customer a, map_table b,   where b.old_cust = a.cust_num;
  c1_rec c1%ROWTYPE;
  commit_cnt number := 0;

BEGIN
   open c1;
   LOOP

     fetch c1 into c1_rec;
     exit when c1%NOTFOUND;
     update customer set cust_num = c1_rec.new_cust
     where  rowid = c1_rec.rowid;

     commit_cnt := commit_cnt + 1;
     if commit_cnt > 20000 then
       commit;
       commit_cnt := 0;
       close c1;
       open c1;
     end if;

    END LOOP;
  close c1;
  commit;
END;
/

Thanks. Received on Tue Feb 13 2001 - 22:47:26 CST