PL/SQL查询IN逗号分隔的字符串 [英] PL/SQL query IN comma deliminated string
问题描述
我正在使用Oracle APEX开发应用程序.我有一个用逗号分隔的用户ID字符串,它看起来像这样,
I am developing an application in Oracle APEX. I have a string with user id's that is comma deliminated which looks like this,
45,4932,20,19
此字符串存储为
:P5_USER_ID_LIST
我想要一个查询来查找此列表中的所有用户,我的查询看起来像这样
I want a query that will find all users that are within this list my query looks like this
SELECT * FROM users u WHERE u.user_id IN (:P5_USER_ID_LIST);
我一直收到Oracle错误:无效的数字.但是,如果我将字符串硬编码到查询中,它将起作用.像这样:
I keep getting an Oracle error: Invalid number. If I however hard code the string into the query it works. Like this:
SELECT * FROM users u WHERE u.user_id IN (45,4932,20,19);
有人知道为什么这可能是个问题吗?
Anyone know why this might be an issue?
推荐答案
绑定变量绑定 a 值,在这种情况下为字符串'45,4932,20,19'.您可以按照Randy的建议使用动态SQL和串联,但是您需要非常小心,使用户无法修改该值,否则会出现SQL注入问题.
A bind variable binds a value, in this case the string '45,4932,20,19'. You could use dynamic SQL and concatenation as suggested by Randy, but you would need to be very careful that the user is not able to modify this value, otherwise you have a SQL Injection issue.
更安全的方法是将ID放入PL/SQL进程的Apex集合中:
A safer route would be to put the IDs into an Apex collection in a PL/SQL process:
declare
array apex_application_global.vc_arr2;
begin
array := apex_util.string_to_table (:P5_USER_ID_LIST, ',');
apex_collection.create_or_truncate_collection ('P5_ID_COLL');
apex_collection.add_members ('P5_ID_COLL', array);
end;
然后将您的查询更改为:
Then change your query to:
SELECT * FROM users u WHERE u.user_id IN
(SELECT c001 FROM apex_collections
WHERE collection_name = 'P5_ID_COLL')
这篇关于PL/SQL查询IN逗号分隔的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!