在NUMBER列的IN子句中使用逗号分隔的值 [英] using comma separated values inside IN clause for NUMBER column

问题描述

我的包装中有2个程序.我正在调用一个过程来获取用逗号分隔的用户ID列表.

I have 2 procedures inside a package. I am calling one procedure to get a comma separated list of user ids.

我将结果存储在VARCHAR变量中.现在，当我使用此逗号分隔的列表将IN子句放入其中时，会抛出"ORA-01722:INVALID NUMBER"异常".

I am storing the result in a VARCHAR variable. Now when I am using this comma separated list to put inside an IN clause in it is throwing "ORA-01722:INVALID NUMBER" exception.

这就是我的变量的样子

l_userIds VARCHAR2(4000) := null;

这是我分配值的地方

l_userIds := getUserIds(deptId);  -- this returns a comma separated list

第二个查询就像-

select * from users_Table where user_id in (l_userIds);

如果运行此查询，则会出现INVALID NUMBER错误.

If I run this query I get INVALID NUMBER error.

有人可以在这里帮忙吗?

Can someone help here.

推荐答案

您是否真的需要返回逗号分隔的列表?通常，最好声明一个收集类型

Do you really need to return a comma-separated list? It would generally be much better to declare a collection type

CREATE TYPE num_table
    AS TABLE OF NUMBER;

声明一个函数，该函数返回此collection的一个实例

Declare a function that returns an instance of this collection

CREATE OR REPLACE FUNCTION get_nums
  RETURN num_table
IS
  l_nums num_table := num_table();
BEGIN
  for i in 1 .. 10
  loop
    l_nums.extend;
    l_nums(i) := i*2;
  end loop;
END;

，然后在查询中使用该集合

and then use that collection in your query

SELECT *
  FROM users_table
 WHERE user_id IN (SELECT * FROM TABLE( l_nums ));

也可以使用动态SQL(@Sebas演示).但是，这样做的缺点是，对过程的每次调用都会生成一个新的SQL语句，在执行该语句之前，需要再次对其进行解析.它还给库缓存带来了压力，这可能导致Oracle清除许多其他可重用的SQL语句，这可能会导致许多其他性能问题.

It is possible to use dynamic SQL as well (which @Sebas demonstrates). The downside to that, however, is that every call to the procedure will generate a new SQL statement that needs to be parsed again before it is executed. It also puts pressure on the library cache which can cause Oracle to purge lots of other reusable SQL statements which can create lots of other performance problems.

这篇关于在NUMBER列的IN子句中使用逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！