将逗号分隔的数字传递给存储过程中的 IN 子句 [英] Pass comma separated number to IN clause in Stored Procedure

问题描述

我有一个存储过程，它采用 Oracle 中名为 P_LOCATIONS 的 varchar 参数.此位置参数具有以逗号分隔的位置 ID.在数据库中 locationId 是一个数字.

I have a stored procedure which takes a varchar parameter called P_LOCATIONS in Oracle. This locations parameter has location id's that are comma separated. In database locationId is a number.

当有多个位置时，以下 sql 查询会抛出一个无效数字.我知道由于逗号，它无法将 varchar 转换为数字.我怎样才能做到这一点?

Following sql query throws an Invalid number when there are more than one locations. I understand that because of comma its not able to convert a varchar into a number. How can I achieve this?

    create or replace PROCEDURE       GET_RAW_DATA
      (   P_LOCATIONS IN VARCHAR2, 
          Results   OUT   SYS_REFCURSOR
        )AS 
        BEGIN

       select * from tableName where LOCATION_ID IN ( P_LOCATIONS);
      END GET_RAW_DATA;

推荐答案

你所做的最终结果是这样的:

The end result of what you are doing is this:

select * from tableName where LOCATION_ID IN ('1,2,3');

你需要的是:

select * from tableName where LOCATION_ID IN (1,2,3);

所以你可以使用这个:

select * from tableName where LOCATION_ID in (
    select regexp_substr(P_LOCATIONS,'[^,]+{1}',1,level)
    from dual connect by level <= length(regexp_replace(P_LOCATIONS,'[^,]*')) + 1
);

这篇关于将逗号分隔的数字传递给存储过程中的 IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！