steve: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!
-----------------------------------------------------------
MajkelThe 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;
来源:http://stackoverflow.com/questions/1731818/resetting-an-associative-array-in-pl-sql