在 IN 子句中为 NUMBER 列使用逗号分隔值

2021-12-24 00:00:00 sql oracle plsql procedure

我在一个包中有 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

我的第二个查询就像 -

And my second query is like - 

select * from users_Table where user_id in (l_userIds);

如果我运行这个查询，我会收到 INVALID NUMBER 错误.

If I run this query I get INVALID NUMBER error.

有人可以帮忙吗.

推荐答案

你真的需要返回一个逗号分隔的列表吗?声明一个集合类型通常会好得多

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;

声明一个返回此集合实例的函数

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.

相关文章