如何在“where value in..."子句中使用参数? [英] How to use parameters in a 'where value in...' clause?
问题描述
当我只有一个状态代码作为参数时，这有效.
This works when I have only one state code as a parameter.
当 parm_list 中有多个 state_code 时，如何让代码工作?
How can I get code to work when I have more than one state_code in parm_list?
要求:
(1)我不想在我的游标定义中硬编码状态代码
(1)I don't want to hard code the state codes in my cursor definition
(2) 我确实希望在我的 where 子句中允许多个状态代码
(2) I do want to allow for more than one state code in my where clause
例如:我想为 parm_list = ('NY','NJ','NC') 运行此代码.我在协调 parm_list 中的单引号与 'where state_code in ' 查询中的单引号时遇到了困难.
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.
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;
推荐答案
从编码的角度来看，使用动态 SQL 是最简单的方法.但是，动态 SQL 的问题在于，您必须对查询的每个不同版本进行硬解析，这不仅有可能增加 CPU 的负担，而且有可能用大量不可共享的 SQL 语句淹没您的共享池，从而推动out 语句你想缓存，导致更多的硬解析和共享池碎片错误.如果您每天运行一次，这可能不是主要问题.如果数百人每天执行数千次，这可能是一个主要问题.
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 方法的示例
An example of the dynamic SQL approach
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.
这篇关于如何在“where value in..."子句中使用参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
