可以在select语句where子句中使用表变量吗?
问题描述:
我有一个存储过程正在执行两步查询。第一步是从表中收集的VARCHAR2类型的字符的列表,并将其收集到一个表变量,定义如下:可以在select语句where子句中使用表变量吗?
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;
这工作正常了直到下一个位。
现在我想用该集合另一个查询的WHERE子句中的相同的过程中,像这样:
SELECT * FROM table_two WHERE cid IN v_cids;
有没有办法做到这一点?我可以选择一个单独的元素,但我想使用表变量,就像使用常规表一样。我尝试过使用嵌套选择的变体,但似乎也不起作用。
非常感谢,
扎克
答
对于如何实现这一点,您有几种选择。
如果你想使用一个集合,那么你可以使用TABLE函数从中进行选择,但是你使用的集合的类型变得很重要。
为一个简单的例子,这将创建一个数据库类型是数字表:
CREATE TYPE number_tab AS TABLE OF NUMBER
/
创建的类型。
下一块,然后填充的收集和使用它作为一个表,并将其加入到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;
您可以从中看到数据库TYPE集合(number_tab)被视为一个表,并可以像这样使用。
另一种选择是简单地加入你选择你的两个表从你的例子:
SELECT tt.*
FROM table_two tt
INNER JOIN table_one to
ON (to.item = tt.cid);
有这样做的其他方式,但第一可能满足您的需求最好的。
希望这会有所帮助。
答
--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;
除非我误解的问题,这应该只是返回的结果是在表变量。
注意:我从来没有使用Oracle,但我想这种情况会是一样的。
啊。这工作完美。我将该变量作为我的类型进行投射,然后加入,并且出现错误。更好的是直接加入表格的选项......我做了很多动态SQL,并且陷入了这个事情的流程中,并完全错过了明显的表现。非常感谢! – zmouser
很高兴帮助解决问题。 – Ollie