oracle 在 PL/SQL 中重置关联数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1731818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Resetting an Associative array in PL/SQL?
提问by 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
让我们来看看 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.
那有意义吗？我基本上需要将它重置为某些东西。如果它是一个从零开始的数字，我可以说 number:=0; 在每次迭代的顶部并完成它。但这不是一个数字，它是一种我可以用干净的 :=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!
坦克！
回答by Majkel
The easiest way:
最简单的方法：
arr_foos.Delete();
Other way is to declare the variable inside the FORloop. This way it will be recreated for each pass.
另一种方法是在FOR循环内声明变量。这样，它将为每次传递重新创建。
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;
回答by Egor Rogov
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.
批量收集在获取之前自动清除集合，并且它的工作速度比循环中逐行读取更快。不幸的是，它不适用于记录，因此每个字段都需要几个 PL/SQL 表。
You can find additional information here: Retrieving Query Results into Collections with the BULK COLLECT Clause
您可以在此处找到更多信息：使用 BULK COLLECT 子句将查询结果检索到集合中
