Oracle PL/SQL 中的表变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1573326/
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
Table Variables in Oracle PL/SQL?
提问by Sonny Boy
I've recently started a new position as a developer and I'm having a bit of trouble with PL/SQL. I've used MS SQL for a number of years but I'm finding PL/SQL a bit trickier.
我最近开始担任开发人员的新职位，但我在使用 PL/SQL 时遇到了一些麻烦。我已经使用 MS SQL 很多年了，但我发现 PL/SQL 有点棘手。
One of the things I used to do when writing functions and stored procedures in MS SQL was to put reoccuring result sets into a table variable so I wouldn't have to requery them throughout my procedure.
在 MS SQL 中编写函数和存储过程时，我经常做的一件事是将重复出现的结果集放入表变量中，这样我就不必在整个过程中重新查询它们。
Something like this:
像这样的东西：
declare @badPeople table(recordPointer int)
insert into @badPeople
select BP_Record_Pointer
from People
where BP_Bad = 1
I'm looking at doing something similar in PL/SQL but I'm not quite sure how to get started. Any ideas if this is even possible?
我正在考虑在 PL/SQL 中做一些类似的事情，但我不太确定如何开始。如果这可能的话，有什么想法吗？
回答by Allan
declare
type t_number is table of number;
v_numbers t_number;
begin
select BP_Record_Pointer
bulk collect into v_numbers
from People
where BP_Bad = 1;
end;
This will create a nested table containing the values from your table. It's important to note that the table will go out-of-scope at the end of the anonymous block, so anything you want to do with it needs to be done inside that block.
这将创建一个包含表中值的嵌套表。重要的是要注意该表将在匿名块的末尾超出范围，因此您想要对其进行的任何操作都需要在该块内完成。
回答by Kuberchaun
In Oracle unlike MS SQL cursors are not considered the devil and something to avoid. So typically in Oracle you will make use of cursors much more often than you would in MS SQL. Of course if you can do it with only SQL and no cursor that is best, but don't be afraid to test and use cursors in Oracle. Get a good book on PL/SQL, this is a good one http://www.amazon.com/Oracle-PL-SQL-Programming-4th/dp/0596009771. The post by Gratzy is another option you can use. So get a good book that you can use to figure out when to use what. PL/SQL is a very rich environment compared to T-SQL. While it has more of a learning curve once you get over the initial curve it's a really easy and power full language. And on top of that it's a lot of fun.
在 Oracle 中，与 MS SQL 游标不同，不被认为是魔鬼，并且需要避免。因此，通常在 Oracle 中，您将比在 MS SQL 中更频繁地使用游标。当然，如果您只能使用 SQL 而没有游标，那是最好的，但不要害怕在 Oracle 中测试和使用游标。获得一本关于 PL/SQL 的好书，这是一本好书http://www.amazon.com/Oracle-PL-SQL-Programming-4th/dp/0596009771。Gratzy 的帖子是您可以使用的另一个选项。所以找一本好书，你可以用它来弄清楚何时使用什么。与 T-SQL 相比，PL/SQL 是一个非常丰富的环境。虽然一旦你克服了最初的曲线，它就会有更多的学习曲线，但它是一种非常简单且强大的完整语言。最重要的是，这很有趣。
