PLSQL中多个参数的逗号分隔查询 [英] Comma Seperated Query for Multiple Parametes in PLSQL

问题描述

到目前为止，我已经可以参考

I am able to query comma separated IN parameter in PLSQL so far with reference to Query with comma seperated IN parameters in PLSQL. and working perfect. My question how do I implement this same solution for at least 3 comma separated parameters. My query parameters are like this,

I_PRODUCT query (R%, L%)
I_MODEL query (E%,T%,R%)
I_TYPE query (A5,B%,C%)

作为函数并调用所有这些参数是否很好?还有其他快速解决方案吗?

Is it good to make as a function and call for all these parameters? Any other quick solution?

create or replace PROCEDURE RQUERY1
(
 I_PRODUCT VARCHAR2
 I_MODEL VARCHAR2
 I_TYPE VARCHAR2
, O_Cursor OUT SYS_REFCURSOR

) AS BEGIN
O_Cursor := NULL;

OPEN O_Cursor FOR
WITH PROD_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 PROD_SEARCH WHERE table1.PRODUCT LIKE pattern );
END RQUERY1 ;

更新:我正在寻找使用存储过程从Java代码中查询参数(I_PRODUCT，I_MODEL，I_TYPE)，并且需要显示输出值.

update: I am looking to query the parameters (I_PRODUCT,I_MODEL,I_TYPE) from my java code using stored procedure and need to display the output value.

推荐答案

我终于明白了你想要什么.

I finally understood what you want.

如此处所述，您可以定义多个with语句.所以你可以这样写:

As described here, you can define multiple with statements. So you can write it like this:

OPEN O_Cursor FOR
WITH PROD_SEARCH AS
(
     select regexp_substr(I_PRODUCT,'[^,]+', 1, level) pattern from dual
     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null
),
MODEL_SEARCH AS
(
     select regexp_substr(I_MODEL,'[^,]+', 1, level) pattern from dual
     connect by regexp_substr(I_MODEL, '[^,]+', 1, level) is not null
),
TYPE_SEARCH AS
(
     select regexp_substr(I_TYPE,'[^,]+', 1, level) pattern from dual
     connect by regexp_substr(I_TYPE, '[^,]+', 1, level) is not null
)
SELECT * FROM table1
  WHERE EXISTS (SELECT NULL FROM PROD_SEARCH WHERE table1.PRODUCT LIKE pattern )
AND EXISTS (SELECT NULL FROM MODEL_SEARCH WHERE table1.MODEL LIKE pattern );
AND EXISTS (SELECT NULL FROM TYPE_SEARCH WHERE table1.TYPE LIKE pattern );
END RQUERY1 ;

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