可以在select语句where clause中使用表变量吗？ [英] Can a table variable be used in a select statement where clause?

问题描述

我有一个执行两步查询的存储过程。第一步是从表中收集VARCHAR2类型字符的列表，并将它们收集到表变量中，定义如下：

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:

TYPE t_cids IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_cids t_cids;

所以基本上我有：

SELECT item BULK COLLECT INTO v_cids FROM table_one;

这个工作正常，直到下一位。

This works fine up until the next bit.

现在我想在同一过程中另一个查询的where子句中使用该集合，如下所示：

Now I want to use that collection in the where clause of another query within the same procedure, like so:

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.

非常感谢，

Zach

推荐答案

您可以选择几种方式实现。

You have several choices as to how you achieve this.

如果要使用集合，则可以使用TABLE函数从中选择，但使用的集合类型非常重要。

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.

一个简单的例子，这将创建一个数字表的数据库类型：

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.

下一个块然后填充集合，并使用它作为表执行一个基本的选择，并将它加入到 EMP 表（有一些输出，所以你可以看到发生了什么）：

The next block then populates the collection and performs a rudimentary select from it using it as a table and joining it to the EMP table (with some output so you can see what's happening):

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;

从这里可以看出，数据库TYPE集合（number_tab）被视为一个表，使用。

You can see from this that the database TYPE collection (number_tab) was treated as a table and could be used as such.

另一个选择是简单地连接您从示例中选择的两个表：

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.

希望这有帮助。

这篇关于可以在select语句where clause中使用表变量吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！