Solved

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

Posted on 2011-09-02
1
500 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
Comment Utility
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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now