Pl sql block to update a table with periodic commit with values from temp table

Question

I am new to PL/SQL . I have a requirement to update phone number in table employee_details where in the old phone number and new phone number is stored in another table phone_no (columns are old_phone_no and new_phone_no)

I want to take the first row in phone_no (old_phone_no and new_phone_no) and execute update statement on employee_details i.e (update employee_details set phone_no=new_phone_no where phone_no=old_phone_no ) with periodic commits;

The same process should continue until we have traversed all the rows in phone_no table.

Looking for answers from all the experts on Stack Overflow.

Create statements.
--Table which needs to be updated
CREATE TABLE EMPLOYEE_DETAILS
(
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
PhoneNumber varchar(255)
);

-- Temp Table which contains old and new phone numbers
CREATE TABLE PHONE_NO
(
PersonID int,
OldPhone varchar(255),
NewPhone varchar(255)
);

I was thinking of periodic commits as I have some 10 million rows in EMPLOYEE_DETAILS and we have many rows in EMPOYEE_DETAILS for single phone number i.e OldPhone from table PHONE_NO.

I do not want to disturb the production performance. I am okay if there is any other way to do it.

---

Whether the below method works .

DECLARE CURSOR all_phones IS SELECT OldPhone, NewPhone FROM PHONE_NO ORDER by OldPhone;

 TYPE phone_old IS TABLE OF PHONE_NO.OldPhone%TYPE;
 TYPE phone_new IS TABLE OF PHONE_NO.NewPhone%TYPE;

 phone_olds phone_old;
 phone_news phone_new;
 inx1 PLS_INTEGER;

BEGIN OPEN all_phones; FETCH all_phones BULK COLLECT INTO phone_olds, phone_news; CLOSE all_phones;

 FOR inx1 IN 1..phone_olds.count LOOP
    begin
    loop
    update EMPLOYEE_DETAILS
    set PhoneNumber = phone_news(inx1)
    where PhoneNumber = phone_olds(inx1)
    and rowcount <= 10000;
    continue when sql%notfound; 
    commit;
    end loop
    commit;
 END LOOP;

END;

Regards, Jay.

Answer 1

Something like this could maybe work (not tested), but I am not sure you can update a column that has been used for a JOIN because it makes the join invalid :

DECLARE
    CURSOR c IS SELECT pn.newphone
                  FROM employee_details ed
                  JOIN phone_no pn ON pn.oldphone = ed.phonenumber
                   FOR UPDATE OF ed.phonenumber;
    row_count INTEGER := 0;
    commit_frequency CONSTANT INTEGER := 10000;
BEGIN
    FOR rec IN c LOOP
        UPDATE employee_details
           SET phonenumber = rec.newphone
         WHERE CURRENT OF c;
        row_count := row_count + 1;

        IF MOD(row_count, commit_frequency) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;

You better have an index on employee_details.phonenumber!

Similarly you could do the following, which should work if there is no cascading effect:

DECLARE
    commit_frequency CONSTANT INTEGER := 10000;
BEGIN
    FOR rec IN ( SELECT oldphone, newphone
                   FROM phone_no
               )
    LOOP
        UPDATE employee_details
           SET phonenumber = rec.newphone
         WHERE phonenumber = rec.oldphone;
        row_count := row_count + 1;

        IF MOD(row_count, commit_frequency) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;

Cascading effect is the following problem:

     EMPLOYEE_DETAILS
name | surname | phonenumber
-----+---------+-------------
John | Doe     | +1 234 5678
Mike | Lee     | +1 098 7654

     PHONE_NO
oldphone    | newphone
------------+------------
+1 098 7654 | +1 357 9135
+1 234 5678 | +1 098 7654

Here, John Doe's phone number could be updated once, or twice.

Answer 2

Do you know about triggers ? In this case it would be the best way to operate. Trigger are automatic actions executed after certain condition. In your case, you could do

CREATE OR REPLACE TRIGGER my_trigger
   AFTER UPDATE ON your_table
   FOR EACH ROW
       DECLARE
       BEGIN
           INSERT INTO phone_no
           (old_phone_no, new_phone_no)
           VALUES
           (OLD.phoneNumber, NEW.phoneNumber);

       END;
       /

This code will simply insert the old and the new phone number in the table after any update , for each row updated.

Answer 3

The best way which i can think off top of my head is a BULK COLLECT approach with LIMIT condition if your DB infrastructure is not that efficient of handle huge loads. Hope below snippet helps. I have don't have workspace with me now so please pardon any syntactical error if any.

DECLARE
  TYPE lv_nphn  IS TABLE OF phone_number    .newphone%TYPE;
  TYPE lv_ophn  IS TABLE OF phone_number    .oldphone%TYPE;
  TYPE lv_empno IS TABLE OF employee_details.empno   %TYPE;
  lv_nph_tab   lv_nphn;
  lv_oph_tab   lv_ophn;
  lv_empno_tab lv_empno;
  CURSOR c
      IS SELECT pn.newphone,
                pn.oldphone,
                ed.empno
           FROM employee_details ed
           JOIN phone_no pn
             ON pn.oldphone = ed.phonenumber;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT INTO lv_oph_tab,lv_nph_tab,lv_empno_tab LIMIT 10000;
    EXIT WHEN lv_nph_tab.COUNT = 0;

    FORALL I IN lv_nph_tab.FIRST..lv_nph_tab.LAST
        UPDATE employee_details
        SET phonenumber = lv_nph_tab(i)
        WHERE empno     = lv_empno_tab(i);
    COMMIT;
  END LOOP;
END;

Answer 4

I would simply update it:

alter table phone_no add constraint phone_pk primary key(oldphone);

update ( select e.phonenumber, p.newphone
         from   employee_details e
                join phone_no p on p.oldphone = e.phonenumber )
set phonenumber = newphone;

Then worry about whether you have sufficient undo resources etc later if it's actually a problem.

(The constraint is needed because oldphone must be declared unique to avoid ORA-01779: cannot modify a column which maps to a non key-preserved table. You can do this with a plain unique index or else a primary or unique key constraint.)