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屋!