Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-02
1
Medium Priority
?
514 Views
Last Modified: 2013-12-19
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.


0
Comment
Question by:diteps06
1 Comment
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36477818
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question