oracle 如何在“where value in...”子句中使用参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11041647/
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
How to use parameters in a 'where value in...' clause?
提问by zundarz
This works when I have only one state code as a parameter.
当我只有一个状态代码作为参数时，这有效。
How can I get code to work when I have more than one state_code in parm_list?
当 parm_list 中有多个 state_code 时，如何让代码工作？
Requirements:
要求：
(1)I don't want to hard code the state codes in my cursor definition
(1)我不想在我的游标定义中硬编码状态代码
(2) I do want to allow for more than one state code in my where clause
(2) 我确实希望在我的 where 子句中允许多个状态代码
For example: I want to run this code for parm_list = ('NY','NJ','NC'). I'm encountering difficulties in reconciling single quotes in parm_list with the single quotes in the 'where state_code in ' query.
例如：我想为parm_list = ('NY','NJ','NC')运行此代码。我在协调 parm_list 中的单引号与 'where state_code in ' 查询中的单引号时遇到了困难。
set serveroutput on;
DECLARE
parm_list varchar2(40);
cursor get_state_codes(in_state_codes varchar2)
is
select state_name, state_code from states
where state_code in (in_state_codes);
BEGIN
parm_list := 'NY';
for get_record in get_state_codes(parm_list) loop
dbms_output.put_line(get_record.state_name || get_record.state_code);
end loop;
END;
回答by Justin Cave
Using dynamic SQL is the simplest approach from a coding standpoint. The problem with dynamic SQL, though, is that you have to hard parse every distinct version of the query which not only has the potential of taxing your CPU but has the potential to flood your shared pool with lots of non-sharable SQL statements, pushing out statements you'd like to cache, causing more hard parses and shared pool fragmentation errors. If you're running this once a day, that's probably not a major concern. If hundreds of people are executing it thousands of times a day, that is likely a major concern.
从编码的角度来看，使用动态 SQL 是最简单的方法。但是，动态 SQL 的问题在于，您必须对查询的每个不同版本进行硬解析，这不仅有可能增加 CPU 的负担，而且有可能用大量不可共享的 SQL 语句淹没您的共享池，从而推动out 语句你想缓存，导致更多的硬解析和共享池碎片错误。如果您每天运行一次，这可能不是主要问题。如果数百人每天执行数千次，这可能是一个主要问题。
An example of the dynamic SQL approach
动态 SQL 方法的示例
SQL> ed
Wrote file afiedt.buf
1 declare
2 l_deptnos varchar2(100) := '10,20';
3 l_rc sys_refcursor;
4 l_dept_rec dept%rowtype;
5 begin
6 open l_rc for 'select * from dept where deptno in (' || l_deptnos || ')';
7 loop
8 fetch l_rc into l_dept_rec;
9 exit when l_rc%notfound;
10 dbms_output.put_line( l_dept_rec.dname );
11 end loop;
12 close l_rc;
13* end;
SQL> /
ACCOUNTING
RESEARCH
PL/SQL procedure successfully completed.
Alternately, you can use a collection. This has the advantage of generating a single, sharable cursor so you don't have to worry about hard parsing or flooding the shared pool. But it probably requires a bit more code. The simplest way to deal with collections
或者，您可以使用集合。这具有生成单个可共享游标的优点，因此您不必担心硬解析或淹没共享池。但它可能需要更多的代码。处理集合的最简单方法
SQL> ed
Wrote file afiedt.buf
1 declare
2 l_deptnos tbl_deptnos := tbl_deptnos(10,20);
3 begin
4 for i in (select *
5 from dept
6 where deptno in (select column_value
7 from table(l_deptnos)))
8 loop
9 dbms_output.put_line( i.dname );
10 end loop;
11* end;
SQL> /
ACCOUNTING
RESEARCH
PL/SQL procedure successfully completed.
If, on the other hand, you really have to start with a comma-separated list of values, then you will have to parse that string into a collection before you can use it. There are various ways to parse a delimited string-- my personal favorite is to use regular expressions in a hierarchical query but you could certainly write a procedural approach as well
另一方面，如果您确实必须从逗号分隔的值列表开始，那么您必须先将该字符串解析为一个集合，然后才能使用它。有多种解析分隔字符串的方法——我个人最喜欢在分层查询中使用正则表达式，但您当然也可以编写过程方法
SQL> ed
Wrote file afiedt.buf
1 declare
2 l_deptnos tbl_deptnos;
3 l_deptno_str varchar2(100) := '10,20';
4 begin
5 select regexp_substr(l_deptno_str, '[^,]+', 1, LEVEL)
6 bulk collect into l_deptnos
7 from dual
8 connect by level <= length(replace (l_deptno_str, ',', NULL));
9 for i in (select *
10 from dept
11 where deptno in (select column_value
12 from table(l_deptnos)))
13 loop
14 dbms_output.put_line( i.dname );
15 end loop;
16* end;
17 /
ACCOUNTING
RESEARCH
PL/SQL procedure successfully completed.
回答by Luis Cabral
One option is to use INSTR instead of IN:
一种选择是使用 INSTR 而不是 IN：
SELECT uo.object_name
,uo.object_type
FROM user_objects uo
WHERE instr(',TABLE,VIEW,', ',' || uo.object_type || ',') > 0;
Although this looks ugly, it works well and as long as no index on the column being tested was going to be used (because this prevents the use of any index) the performance won't suffer much. If the column being tested is a primary key for instance, definitely this should not be used.
尽管这看起来很难看，但它运行良好，只要要测试的列上没有索引将被使用（因为这会阻止使用任何索引），性能不会受到太大影响。例如，如果被测试的列是主键，则绝对不应该使用它。
Another option is:
另一种选择是：
SELECT uo.object_name
,uo.object_type
FROM user_objects uo
WHERE uo.object_type IN
(SELECT regexp_substr('TABLE,VIEW', '[^,]+', 1, LEVEL)
FROM dual
CONNECT BY regexp_substr('TABLE,VIEW', '[^,]+', 1, LEVEL) IS NOT NULL);
In this case, the list of values should be concatenated into a single varchar variable, delimited by commas (or anything you like.)
在这种情况下，值列表应该连接成一个单一的 varchar 变量，用逗号（或任何你喜欢的东西）分隔。
