oracle 如何在oracle sql的WHERE IN子句中传递变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4462415/
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 pass a variable in WHERE IN clause of oracle sql?
提问by Ravi Gupta
Hi
I have a variable $1 which hold comma separated email addresses like [email protected],[email protected] . I wish to pass this variable in a where clause like
嗨，
我有一个变量 $1 ，它保存逗号分隔的电子邮件地址，如 [email protected],[email protected] 。我希望在一个 where 子句中传递这个变量，比如
where myColumn in ()
But obviously this won't work, I tried APEX_UTIL.STRING_TO_TABLE and DBMS_UTILITY.COMMA_TO_TABLE but in vain.
但显然这行不通，我尝试了 APEX_UTIL.STRING_TO_TABLE 和 DBMS_UTILITY.COMMA_TO_TABLE 但徒劳无功。
Any help appreciated.
任何帮助表示赞赏。
采纳答案by Dan
As Pavanred alluded to, the easiest way -- though not necessarily the best -- is to interpolate the values yourself. You don't say what your calling language is, but something like:
正如 Pavanred 所暗示的，最简单的方法——虽然不一定是最好的——是自己插入这些值。你没有说你的呼叫语言是什么，而是说：
sql = "SELECT something FROM whatever WHERE myColumn in (" + + ")"
However, this means it's very important that you have pre-checked all the values in $1 to make sure that they are either numbers, or properly escaped strings, or whatever else it is that you need to pass but cannot be raw values supplied by a user, to avoid a SQL injection.
但是，这意味着您必须预先检查 $1 中的所有值以确保它们是数字或正确转义的字符串，或者您需要传递但不能是由用户，以避免 SQL 注入。
The other option is to make it a two-step process. First, insert the values from $1 into a temporary table, then select those values as a subquery:
另一种选择是使其成为一个两步过程。首先，将 $1 中的值插入到临时表中，然后选择这些值作为子查询：
WHERE myColumn in (SELECT temp_value FROM temp_table)
回答by Jeremy Shimanek
It's not pretty, but you can achieve a 100% one-step SQL injection-proof solution like this. You plug in the "criteria" CTE to your query, which parses the comma-sperated list into a table. Then you can use it in an in query like "where column in (select item from criteria)":
这并不漂亮，但您可以像这样实现 100% 的一步式 SQL 防注入解决方案。您将“标准”CTE 插入到您的查询中，这会将逗号分隔的列表解析为一个表。然后你可以在查询中使用它，比如“where column in（从条件中选择项目）”：
with criteria as
(
select
substr(criteria_list, item_start, item_length) as item
from
(
select
criteria_list,
comma_pos + 1 as item_start,
nvl(next_comma_pos - 1, length(criteria_list)) - comma_pos as item_length
from
(
select
criteria_list,
comma_pos,
lead(comma_pos) over(order by comma_pos) as next_comma_pos
from
(
select
as criteria_list,
instr(, ',', 1, level) as comma_pos
from
dual
connect by
instr(, ',', 1, level) > 0 and instr(, ',', 1, level) <= instr(, ',', -1)
union all
select
,
0
from
dual
)
)
)
)
select * from some_table where column in (select item from criteria)回答by Erich Kitzmueller
A simple workaround, which might be good enough in your case (but is not performant), is to use LIKE instead:
一个简单的解决方法，在您的情况下可能足够好（但性能不佳），是使用 LIKE 代替：
select * from something where like '%,'||mycolumn||',%';
$1 is your comma separated list, as a string, with a comma prepended and appended, e.g.
$1 是你的逗号分隔列表，作为一个字符串，在前面和附加一个逗号，例如
,[email protected],[email protected],[email protected],
