oracle 可以在 select 语句 where 子句中使用表变量吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8348013/
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
Can a table variable be used in a select statement where clause?
提问by zmouser
I have a stored procedure that is doing a two-step query. The first step is to gather a list of VARCHAR2 type characters from a table and collect them into a table variable, defined like this:
我有一个执行两步查询的存储过程。第一步是从表中收集 VARCHAR2 类型字符的列表，并将它们收集到表变量中，定义如下：
TYPE t_cids IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_cids t_cids;
So basically I have:
所以基本上我有：
SELECT item BULK COLLECT INTO v_cids FROM table_one;
This works fine up until the next bit.
这在下一位之前都可以正常工作。
Now I want to use that collection in the where clause of another query within the same procedure, like so:
现在我想在同一过程中另一个查询的 where 子句中使用该集合，如下所示：
SELECT * FROM table_two WHERE cid IN v_cids;
Is there a way to do this? I am able to select an individual element, but I would like to use the table variable like a would use a regular table. I've tried variations using nested selects, but that doesn't seem to work either.
有没有办法做到这一点？我可以选择一个单独的元素，但我想像使用常规表一样使用表变量。我尝试过使用嵌套选择的变体，但这似乎也不起作用。
Thanks a lot,
非常感谢，
Zach
扎克
回答by Ollie
You have several choices as to how you achieve this.
关于如何实现这一目标，您有多种选择。
If you want to use a collection, then you can use the TABLE function to select from it but the type of collection you use becomes important.
如果您想使用一个集合，那么您可以使用 TABLE 函数从中进行选择，但您使用的集合类型变得很重要。
for a brief example, this creates a database type that is a table of numbers:
举个简单的例子，这将创建一个数据库类型，它是一个数字表：
CREATE TYPE number_tab AS TABLE OF NUMBER
/
Type created.
类型已创建。
The next block then populates the collection and performs a rudimentary select from it using it as a table and joining it to the EMPtable (with some output so you can see what's happening):
然后下一个块填充集合并从中执行基本选择，将其用作表并将其连接到EMP表（带有一些输出，以便您可以看到发生了什么）：
DECLARE
-- Create a variable and initialise it
v_num_tab number_tab := number_tab();
--
-- This is a collection for showing the output
TYPE v_emp_tabtype IS TABLE OF emp%ROWTYPE
INDEX BY PLS_INTEGER;
v_emp_tab v_emp_tabtype;
BEGIN
-- Populate the number_tab collection
v_num_tab.extend(2);
v_num_tab(1) := 7788;
v_num_tab(2) := 7902;
--
-- Show output to prove it is populated
FOR i IN 1 .. v_num_tab.COUNT
LOOP
dbms_output.put_line(v_num_tab(i));
END LOOP;
--
-- Perform a select using the collection as a table
SELECT e.*
BULK COLLECT INTO v_emp_tab
FROM emp e
INNER JOIN TABLE(v_num_tab) nt
ON (e.empno = nt.column_value);
--
-- Display the select output
FOR i IN 1 .. v_emp_tab.COUNT
LOOP
dbms_output.put_line(v_emp_tab(i).empno||' is a '||v_emp_tab(i).job);
END LOOP;
END;
You can see from this that the database TYPE collection (number_tab) was treated as a table and could be used as such.
从中可以看出，数据库 TYPE 集合 (number_tab) 被视为一个表，可以这样使用。
Another option would be to simply join your two tables you are selecting from in your example:
另一种选择是简单地加入您在示例中选择的两个表：
SELECT tt.*
FROM table_two tt
INNER JOIN table_one to
ON (to.item = tt.cid);
There are other ways of doing this but the first might suit your needs best.
还有其他方法可以做到这一点，但第一种方法可能最适合您的需求。
Hope this helps.
希望这可以帮助。
回答by Toast
--Doesn't work.
--SELECT item BULK COLLECT AS 'mySelectedItems' INTO v_cids FROM table_one;
SELECT table_two.*
FROM table_two INNER JOIN v_cids
ON table_two.paramname = v_cids.mySelectedItems;
Unless I'm misunderstanding the question, this should only return results that are in the table variable.
除非我误解了这个问题，否则这应该只返回表变量中的结果。
Note: I've never used Oracle, but I imagine this case would be the same.
注意：我从未使用过 Oracle，但我想这种情况是一样的。
