在PLSQL中使用逗号分隔的IN参数查询 [英] Query with comma seperated IN parameters in PLSQL

问题描述

我在Oracle 11g中编写了以下代码，以分隔IN参数(I_PRODUCT)并进行查询.当我提供一个参数作为i_PRODUCT时，它将填充一些结果.当我插入多个用逗号分隔的参数时，我期望一个接一个地查询并填充整个结果，但是它不起作用.

I wrote the following in Oracle 11g to separate IN params (I_PRODUCT)and make a query. When I give one parameter as the i_PRODUCT, it is populating some results. When I am inserting multiple comma separated parameters, I am expecting to query one by one and populate the entire result and it is not working.

create or replace PROCEDURE RQUERY 
(
 I_PRODUCT VARCHAR2
, O_Cursor OUT SYS_REFCURSOR
) AS BEGIN
O_Cursor := NULL;
OPEN O_Cursor for

SELECT * FROM Table1 WHERE   
Table1.PRODUCT LIKE ( select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
                     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null);

END RQUERY ;

推荐答案

SELECT * FROM Table1 WHERE   
Table1.PRODUCT LIKE ( select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
                     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null);

LIKE 运算符将在上述查询中失败.您需要使用 IN ，它会在内部被评估为多个 OR .

The LIKE operator would fail in above query. You need to use IN, which would internally be evaluated as multiple OR.

Table1.PRODUCT IN ( select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
                     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null);

此外，关于 不断变化的输入列表 ，请在此处查看我的答案.

Also, regarding varying IN list, have a look at my answer here.

更新

基于OP的评论.

将查询修改为:

WITH str_search AS
(
     select regexp_substr(I_PRODUCT,'[^,]+', 1, level) pattern from dual
     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null
)
SELECT   *
FROM     Table1
WHERE     EXISTS (
          SELECT     NULL
          FROM     str_search
          WHERE     tab.col     LIKE pattern
                 );

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