Procedure to scroll through table names and execute the same set of instructions

There is a set of update instructions to execute on a number of tables. Instead of repeating the instructions is there a way I can stored the table names in a collection like an array and call the items for elaboration.
The format of the procedure is as follows:

Procedure update_tabs (id, new_sal)
Update <tablename>
 Set sal := new_sal
Where emp_id = id

I have to do the update in four tables tab_A, tab_B, tab_C and tab_D.
All the tables have the columns emp_id and sal.

Instead of writing the code four times that is:

Procedure update_tabs (id, rpdate, new_sal)
Update tab_A
 Set sal := new_sal
Where emp_id = id;
commit;
Update tab_B
 Set sal := new_sal
Where emp_id = id;
Commit;
   |
  |
Update tab_D
 Set sal := new_sal
Where emp_id = id;
Commit;

I would like to write it in this format
Procedure update_tabs (id, rp_date, new_sal)
--A recursive instruction to elaborate the four tables
Update <current_tablename>
 Set sal := new_sal
Where emp_id = id;
Commit;
End loop;

There is also an additional table tab_E where the condition of update regards the rp_date
Update tab_E
 Set sal := new_sal
Where in_date = rp_date;
Commit;

The collection should now have 5 elements. There can be a check if the table name equals tab_E the above instructions should be executed.
How ca I achieve a loop to scroll through the table names and execute the required set of instructions.


LVL 1
diteps06Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
if you only have 5 tables and they will be static then I recommend NOT trying to do this dynamically.  If the set of tables will be large or variable then try something like this...


CREATE OR REPLACE PROCEDURE update_tables(
    id           IN NUMBER,
    rp_date      IN DATE,
    new_sal      IN NUMBER,
    tablenames   IN DBMS_SQL.varchar2s
)
IS
BEGIN
    FOR t IN 1 .. tablenames.COUNT
    LOOP
        IF tablenames(t) != 'TAB_E'
        THEN
            EXECUTE IMMEDIATE
                'update ' || tablenames(t) || ' set sal = :new_sal where emp_id = :id'
                USING new_sal, id;
        ELSE
            EXECUTE IMMEDIATE
                'update ' || tablenames(t) || ' set sal = :new_sal where in_date = :rp_date'
                USING new_sal, rp_date;
        END IF;

        COMMIT;
    END LOOP;
END;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.