CODEWITHSUNDEEP
sqlarraysoracle-databaseplsql
Nimbocrux
Nimbocrux

Reputation: 519

Updating multiple tables using an array in PL/SQL

I'd like to update my employee database to set the batch_id to "P-1000" if the employee is a plumber.

Instead of creating 5 separate (and slow) queries, I want to:

  1. set each index of the array is a table name
  2. loop through the array
  3. update the table using the string value at the string index

I can't find anything even remotely like the concept I'm proposing here, which leads me to think I'm barking up the wrong tree entirely.

None the less, here is the psuedocode owhat I'm trying to do.

declare
  type array_type is table of varchar2(100) index by binary_integer;
  dmt_tables array_type; 
begin
--fill dm_employeeTables array
    dm_employeeTables(0) := 'dm_address';
    dm_employeeTables(1) := 'dm_communications';
    dm_employeeTables(2) := 'dm_identifier';
    dm_employeeTables(3) := 'dm_name';
    dm_employeeTables(4) := 'dm_qualifications';

-- loop through tables
for i in dm_employeeTables.FIRST .. dmt_tables.LAST
   loop
    update dm_employeeTables(i) a 
    set employee_batch_id = 'P-1000'
    where a.employee_type = 'PLUMBER';
    i=i+1;
  end loop;

end;

Upvotes: 1

Views: 2081

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

You would need to use dynamic SQL. Something like

FOR i IN dm_employeeTables.FIRST .. dmt_tables.LAST
LOOP
  EXECUTE IMMEDIATE 'UPDATE ' || dm_employeeTables(i) ||
                    '   SET employee_batch_id = :1 ' ||
                    ' WHERE employee_type = :2'
    USING 'P-1000', 'PLUMBER';
END LOOP;

Now, from a good coding standpoint, you probably want to declare a new local variable (i.e. l_sql_stmt) that you populate with the SQL statement you want to execute and then pass to EXECUTE IMMEDIATE. That allows you to do things like log the SQL statement (or print it out) before you execute the SQL statement. That generally makes debugging much easier.

Upvotes: 2

Related Questions