如何在oracle sql的WHERE IN子句中传递变量? [英] how to pass a variable in WHERE IN clause of oracle sql?
问题描述
嗨
我有一个变量 $1 ，它保存逗号分隔的电子邮件地址，如 john@example.com,pat@example.com .我希望在像
Hi
I have a variable $1 which hold comma separated email addresses like john@example.com,pat@example.com . I wish to pass this variable in a where clause like
where myColumn in ($1)
但显然这行不通，我尝试了 APEX_UTIL.STRING_TO_TABLE 和 DBMS_UTILITY.COMMA_TO_TABLE 但徒劳无功.
But obviously this won't work, I tried APEX_UTIL.STRING_TO_TABLE and DBMS_UTILITY.COMMA_TO_TABLE but in vain.
感谢任何帮助.
推荐答案
正如 Pavanred 所暗示的，最简单的方法——虽然不一定是最好的——是自己插入值.你没有说你的通话语言是什么，而是说:
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:
sql = "SELECT something FROM whatever WHERE myColumn in (" + $1 + ")"
然而，这意味着您必须预先检查 $1 中的所有值，以确保它们是数字或正确转义的字符串，或者您需要传递但不能是原始值的任何其他内容，这一点非常重要由用户提供，以避免 SQL 注入.
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 中的值插入到临时表中，然后选择这些值作为子查询:
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:
WHERE myColumn in (SELECT temp_value FROM temp_table)
这篇关于如何在oracle sql的WHERE IN子句中传递变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
