[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-09-02
1
Medium Priority
?
518 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

591 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