Solved

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

Posted on 2011-09-02
1
502 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 73

Accepted Solution

by:
sdstuber earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

770 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