Question

This is one of those "there s gotta be a better way" questions. Let me set up the problem, then I ll give you my hacked solution, and perhaps you can suggest a better solution. Thanks!

Lets take this little tidbit of PL/SQL

DECLARE
 TYPE foo_record IS RECORD (foo%type, bar%type);
 TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER;
 arr_foos foo_records;

 CURSOR monkeys is SELECT primates FROM zoo;
 row_monkey monkeys%rowtype;
BEGIN
 FOR row_monkey IN monkeys loop
  /*
    at this point in each iteration I need to have the associative array 
    arr_foos in its original state. if this were java, I d declare it 
    right here and its scope would be limited to this iteration. However, 
    this is not java, so the scope of the array is effectively global and 
    I can t have one iteration s data meddle with the next.
  */
  null;
 END LOOP;
END;

Does that make sense? I basically need to reset it to something. If it was a number that starts at zero, I could just say number:=0; at the top of every iteration and be done with it. But this is not a number, it s a type that I can just reset with a clean :=0.

Anyway, onto my hack:

DECLARE
 TYPE foo_record IS RECORD (foo%type, bar%type);
 TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER;
 arr_foos foo_records;
 arr_foos_reset foo_records;

 CURSOR monkeys is SELECT primates FROM zoo;
 row_monkey monkeys%rowtype;
BEGIN
 FOR row_monkey IN monkeys loop
  arr_foos := arr_foos_reset;
  null;
 END LOOP;
END;

I figured that if I can manage to preserve a member of the same type in an original state, then I can just set the working variable back to whatever the value is of the original. And, surprisingly enough, it works (I think.) But there s gotta be a better way. Can anyone help?

T anks!

Answer 1

The easiest way:

arr_foos.Delete();

Other way is to declare the variable inside the FOR loop. This way it will be recreated for each pass.

Like this:

DECLARE
 TYPE foo_record IS RECORD (foo%type, bar%type);
 TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER;     

 CURSOR monkeys is SELECT primates FROM zoo;
 row_monkey monkeys%rowtype;
BEGIN
 FOR row_monkey IN monkeys loop
  DECLARE
    arr_foos foo_records;
  BEGIN
    null;
  END;
 END LOOP;
END;

Answer 2

Are you going to read data from zoo table into a collection? Then there s a better way:

DECLARE
  type foos_ts is table of zoo.foo%type index by pls_integer;
  foos foos_t;
BEGIN
  select foo
  bulk collect into foos
  from zoo;
  ...
END;

Bulk collect automatically clears the collection before fetching, and it works faster then reading row-by-row in loop. Unfortunately, it does n work with records, so you ll need several PL/SQL tables for each of field.

You can find additional information here: Retrieving Query Results into Collections with the BULK COLLECT Clause

友情链接