oracle 10g IN子句查询

发布于 2024-10-25 03:03:22 字数 286 浏览 7 评论 0原文

请忽略下面明显的语法缺陷：

我有一个像这样的 sql 作为命名查询：

select saalry from emp where emp_id in (:id)

id 的类型为数字我想传递一个逗号分隔的列表，如下所示：

String id = 121,123,456

但我收到 ORA-01722: invalid number 如何将逗号分隔的 id 列表传递给 IN 子句？

原文

Please ignore obvious syntax flaws in the below:

I have an sql like this as a named query:

select saalry from emp where emp_id in (:id)

id is of type number
I wanted to pass in a comma separated list like this:

String id = 121,123,456

But I am getting ORA-01722: invalid number
How can I pass a comma separated list of ids to my IN clause?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

可遇━不可求 2024-11-01 03:03:23

假设 :id 是一个包含相对较短的逗号分隔数字列表的字符串（例如 '123,456,789'），这对您来说可能就足够了：

select saalry from emp
where INSTR( ',' || :id || ','
           , ',' || TRIM(TO_CHAR(emp_id)) || ','
           ) > 0;

它不会执行不过，因为它不太可能在 emp_id 上使用索引。

Assuming :id is a string containing a relatively short comma-delimited list of numbers (e.g. '123,456,789'), this may be sufficient for you:

select saalry from emp
where INSTR( ',' || :id || ','
           , ',' || TRIM(TO_CHAR(emp_id)) || ','
           ) > 0;

It won't perform as well, however, since it is unlikely to use an index on emp_id.

回复收藏 0 原文

↘人皮目录ツ 2024-11-01 03:03:23

还有另一种方法来自 http://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

他们的例子是

select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

Which can be put into an in 子句

 select * from emp where ename in (
   select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
   connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );

There is another way that is from http://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Their example is

select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

Which can be put into an in clause

 select * from emp where ename in (
   select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
   connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );

回复收藏 0 原文

~没有更多了~