oracle PL/SQL 查询 IN 逗号分隔的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7040741/
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
PL/SQL query IN comma deliminated string
提问by oracle_APEX_so
I am developing an application in Oracle APEX. I have a string with user id's that is comma deliminated which looks like this,
我正在 Oracle APEX 中开发应用程序。我有一个以逗号分隔的用户 ID 字符串，看起来像这样，
45,4932,20,19
This string is stored as
该字符串存储为
: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);
I keep getting an Oracle error: Invalid number. If I however hard code the string into the query it works. Like this:
我不断收到 Oracle 错误：无效号码。但是，如果我将字符串硬编码到查询中，则它可以工作。像这样：
SELECT * FROM users u WHERE u.user_id IN (45,4932,20,19);
Anyone know why this might be an issue?
任何人都知道为什么这可能是一个问题？
回答by Tony Andrews
A bind variable binds avalue, 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.
绑定变量绑定一个值，在本例中为字符串 '45,4932,20,19'。您可以按照 Randy 的建议使用动态 SQL 和串联，但您需要非常小心，用户无法修改此值，否则您会遇到 SQL 注入问题。
A safer route would be to put the IDs into an Apex collection in a PL/SQL process:
更安全的方法是将 ID 放入 PL/SQL 进程中的 Apex 集合中：
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')
回答by vbguru
An easier solution is to use instr:
一个更简单的解决方案是使用instr：
SELECT * FROM users u
WHERE instr(',' || :P5_USER_ID_LIST ||',' ,',' || u.user_id|| ',', 1) !=0;
tricks:
技巧：
',' || :P5_USER_ID_LIST ||','
to make your string ,45,4932,20,19,
使你的字符串 ,45,4932,20,19,
',' || u.user_id|| ','
to have i.e. ,32,and avoid to select the 32being in ,4932,
有即,32,避免选择32存在,4932,
回答by Alex Peta
I have faced this situation several times and here is what i've used:
我已经多次遇到这种情况，这是我使用过的：
SELECT *
FROM users u
WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%'
ive used the like operator but you must be a little carefull of one aspect here: your item P5_USER_ID_LIST must be ",45,4932,20,19," so that like will compare with an exact number "',45,'".
我使用了 like 运算符，但您必须在这里注意一个方面：您的项目 P5_USER_ID_LIST 必须是“,45,4932,20,19”，这样 like 将与确切的数字“',45,'”进行比较。
When using it like this, the select will not mistake lets say : 5 with 15, 155, 55.
像这样使用时，选择不会出错，可以说：5 与 15、155、55。
Try it out and let me know how it goes;)
试试看，让我知道它是怎么回事；）
Cheers , Alex
干杯，亚历克斯
回答by Emu
If possible the best idea may be to not store your user ids in csv! Put them in a table or failing that an array etc. You cannot bind a csv field as a number.
如果可能，最好的主意可能是不要将您的用户 ID 存储在 csv 中！将它们放在表中或失败数组等。您不能将 csv 字段绑定为数字。
回答by m12lrpv
Please dont use: WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%' because you'll force a full table scan although with the users table you may not have that many so the impact will be low but against other tables in an enterprise environment this is a problem.
请不要使用：WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%' 因为你会强制进行全表扫描，尽管使用您可能没有那么多用户表，因此影响会很小，但是对于企业环境中的其他表，这是一个问题。
EDIT: I have put together a script to demonstrate the differences between the regex method and the wildcard like method. Not only is regex faster but it's also a lot more robust.
编辑：我已经编写了一个脚本来演示正则表达式方法和类似通配符的方法之间的差异。正则表达式不仅更快，而且更健壮。
-- Create table
create table CSV_TEST
(
NUM NUMBER not null,
STR VARCHAR2(20)
);
create sequence csv_test_seq;
begin
for j in 1..10 loop
for i in 1..500000 loop
insert into csv_test( num, str ) values ( csv_test_seq.nextval, to_char( csv_test_seq.nextval ));
end loop;
commit;
end loop;
end;
/
-- Create/Recreate primary, unique and foreign key constraints
alter table CSV_TEST
add constraint CSV_TEST_PK primary key (NUM)
using index ;
alter table CSV_TEST
add constraint CSV_TEST_FK unique (STR)
using index;
select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002, 100003 , 100004, 100005','[^,]+', 1, Level) From Dual
Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;
select *
from csv_test t
where ('%,' || '100001,100002, 100003, 100004 ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002, 100003 , 100004, 100005','[^,]+', 1, Level) From Dual
Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;
select *
from csv_test t
where ('%,' || '100001,100002, 100003, 100004 ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;
drop table csv_test;
drop sequence csv_test_seq;
回答by Bigyellowbee
Solution from Tony Andrews works for me. The process should be added to "Page processing" >> "After submit">> "Processes".
Tony Andrews 的解决方案对我有用。该流程应添加到“页面处理”>>“提交后”>>“流程”中。
回答by user10266688
As you are Storing User Ids as String so You can Easily match String Using Likeas Below
由于您将用户 ID 存储为字符串，因此您可以使用Like如下轻松匹配字符串
SELECT * FROM users u WHERE u.user_id LIKE '%'||(:P5_USER_ID_LIST)||'%'
For Example
例如
:P5_USER_ID_LIST = 45,4932,20,19
Your Query Surely Will return Any of 1 User Id which Matches to Users table
您的查询肯定会返回与用户表匹配的 1 个用户 ID 中的任何一个
This Will Surely Resolve Your Issue , Enjoy
这肯定会解决您的问题，享受
回答by Kevin
Create a native query rather than using "createQuery/createNamedQuery"
创建本机查询而不是使用“createQuery/createNamedQuery”
回答by Stephen ODonnell
The reason this is an issue is that you cannot just bind an in list the way you want, and just about everyone makes this mistake at least once as they are learning Oracle (and probably SQL!).
这是一个问题的原因是你不能只以你想要的方式绑定一个 in 列表，并且几乎每个人在学习 Oracle（可能还有 SQL！）时至少犯过一次这个错误。
When you bind the string '32,64,128', it effectively becomes a query like:
当您绑定字符串 '32,64,128' 时，它实际上变成了如下查询：
select ...
from t
where t.c1 in ('32,64,128')
To Oracle this is totally different to:
对于 Oracle 而言，这与以下情况完全不同：
select ...
from t
where t.c1 in (32,64,128)
The first example has a single string value in the in list and the second has a 3 numbers in the in list. The reason you get an invalid number error is because Oracle attempts to cast the string '32,64,128' into a number, which it cannot do due to the commas in the string.
第一个示例在 in 列表中有一个字符串值，第二个在 in 列表中有 3 个数字。您收到无效数字错误的原因是 Oracle 尝试将字符串 '32,64,128' 转换为数字，由于字符串中的逗号，它无法执行此操作。
A variation of this "how do I bind an in list" question has come up on here quite a few times recently.
这个“如何绑定一个列表”问题的一个变体最近在这里出现了好几次。
Generically, and without resorting to any PLSQL, worrying about SQL Injection or not binding the query correctly, you can use this trick:
一般而言，无需求助于任何 PLSQL，无需担心 SQL 注入或未正确绑定查询，您可以使用以下技巧：
with bound_inlist
as
(
select
substr(txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
as token
from (select ','||:txt||',' txt from dual)
connect by level <= length(:txt)-length(replace(:txt,',',''))+1
)
select *
from bound_inlist a, users u
where a.token = u.id;
回答by Randy
you will need to run this as dynamic SQL.
您需要将其作为动态 SQL 运行。
create the entire string, then run it dynamically.
创建整个字符串，然后动态运行它。
