如何使用变量在动态查询中指定IN子句？

Question

在PL/SQL中，可以使用连接指定In运算符的值：

v_sql := 'select field1
from table1
where field2 in (' || v_list || ')';

是否可以使用变量执行相同的操作？

v_sql := 'select field1
from table1
where field2 in (:v_list)'; 

如果是这样，如何？

编辑:参考Marcin的回答，我如何从结果表中选择？

declare

cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = 'PERM';

cursor c_get_food_list (v_food_table varchar2Table)is
select *
from v_food_table;

begin
    for i in c_get_csv_as_tables loop
        for j in c_get_food_list(i.food_list) loop
            dbms_output.put_line(j.element);
        end loop;
    end loop;
end;

我得到以下错误:

ORA-06550: line 10, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 34:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 15, column 13:
PL/SQL: Statement ignored

Answer 1

绑定变量可以在 Oracle SQL 查询中使用“in”子句。

10g有效;我不知道其他版本。

绑定变量为varchar，最多4000个字符。

示例：绑定包含逗号分隔值列表的变量，例如。

： bindvar=1,2,3,4,5

select * from mytable
  where myfield in
    (
      SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
      FROM dual
      CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
    );

Answer 2

不幸的是，不能像这样绑定列表，但是可以使用表函数。读这个

下面是一个基于您的代码的用法示例：

declare

cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = 'PERM';

cursor c_get_food_list (v_food_table varchar2Table)is
select column_value food
from TABLE(v_food_table);

begin
    for i in c_get_csv_as_tables loop
        for j in c_get_food_list(i.food_list) loop
            dbms_output.put_line(j.food);
        end loop;
    end loop;
end;

我在这里使用了column_value伪列

Answer 3

像在@Sathya link中，你可以绑定varray(我举了@科多的例子):

CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
/
DECLARE
  l_str_tab str_tab_type;
  l_count NUMBER;
  v_sql varchar2(3000);
BEGIN
  l_str_tab := str_tab_type();
  l_str_tab.extend(2);
  l_str_tab(1) := 'TABLE';
  l_str_tab(2) := 'INDEX';

  v_sql := 'SELECT COUNT(*) FROM all_objects WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(:v_list))';

  execute immediate v_sql into l_count using l_str_tab;

  dbms_output.put_line(l_count);
END;
/

更新：第一个命令可以替换为：

CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
    /

然后打电话：

l_str_tab.extend(1);

当你添加一个值时