本文介绍了如何使用变量在动态查询中指定 IN 子句?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!
问题描述
在 PL/SQL 中,您可以使用连接指定 IN 运算符的值:
In PL/SQL, you can specify the values for the IN operator using concatenation:
v_sql := 'select field1
from table1
where field2 in (' || v_list || ')';
是否可以使用变量来做同样的事情?
Is it possible to do the same using a variable?
v_sql := 'select field1
from table1
where field2 in (:v_list)';
如果是,怎么做?
参考 Marcin 的回答,我如何从结果表中进行选择?
With reference to Marcin's answer, how do I select from the resultant table?
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
推荐答案
就像在@Sathya 链接中一样,您可以绑定 varray(我以@Codo 为例):
Like in @Sathya link, you can bind the varray (I took @Codo example):
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;
/
UPDATE:第一个命令可以替换为:
UPDATE: the first command can be replaced with:
CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
/
然后调用:
l_str_tab.extend(1);
当你添加一个值时
这篇关于如何使用变量在动态查询中指定 IN 子句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!