在存储过程中将逗号分隔值作为 IN 参数传递 [英] Passing comma separated value as an IN parameter in stored procedure
问题描述
我是 DB2 查询的新手.
I am new to DB2 queries.
在这里,我在存储过程中传递一个逗号分隔值作为 IN 参数.我想根据这些值进行搜索.
Here, I am passing a comma separated value as an IN parameter in a Stored Procedure. I want to search on the basis of those values.
Select * from USER where user_id in (IN_User);
在这里,IN_User 将具有 ('val1','val2','val3') 类型的值
Here, IN_User will have values of the kind ('val1','val2','val3')
它应该返回所有以 val1 或 val2 或 val3 作为 User_id 的行.据我所知,这可以使用 UDF 来完成,但我想知道有没有其他方法可以在没有 UDF 的情况下做到这一点.
It should return all the rows which has val1 or val2 or val3 as the User_id. As much as I know this can be done using UDF but I want to know is there any other way to do it without UDF.
推荐答案
请创建一个函数来分割逗号分隔的字符串
please create a function to split the comma separated string
请看下面的功能
CREATE FUNCTION StringToRows(
cString1 CLOB (10 M) ,
cStringSplitting1 VARCHAR(10) )
RETURNS TABLE (Lines VARCHAR(500))
SPECIFIC StringToRows_Big
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
DECLARE cStringSplitting VARCHAR(10);
DECLARE LenSplit SMALLINT;
SET cStringSplitting = cStringSplitting1;
SET LenSplit = LENGTH(cStringSplitting);
IF LENGTH(TRIM(cStringSplitting)) = 0 THEN
SET cStringSplitting = ' ', LenSplit = 1 ;
END IF ;
RETURN WITH
TEMP1 ( STRING) as (values (cString1) ),
TEMP2 ( Lines, STRING_left) as
(SELECT
SUBSTR(STRING,1, CASE WHEN LOCATE(cStringSplitting, STRING) = 0 THEN LENGTH(STRING) ELSE LOCATE(cStringSplitting,STRING) - 1 END),
(CASE WHEN (LOCATE(cStringSplitting, STRING) = 0) THEN '' ELSE SUBSTR(STRING, LOCATE(cStringSplitting,STRING) + LenSplit) END)
FROM TEMP1 WHERE LENGTH(STRING) > 0
UNION ALL
SELECT
SUBSTR(STRING_left,1, CASE LOCATE(cStringSplitting,STRING_left) WHEN 0 THEN LENGTH(STRING_left) ELSE LOCATE(cStringSplitting,STRING_left) - 1 END),
(CASE WHEN LOCATE(cStringSplitting,STRING_left) = 0 THEN '' ELSE SUBSTR(STRING_left, LOCATE(cStringSplitting,STRING_left) + LenSplit) END)
FROM TEMP2 WHERE LENGTH(STRING_left) > 0 )
SELECT Lines FROM TEMP2;
END
请参阅示例存储过程以调用该函数
please see the sample stored procedure to call the function
CREATE PROCEDURE TEST_USR(IN @inputParam CLOB (10 M))
SPECIFIC TEST_USR
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR
Select * from USER where user_id IN (SELECT * FROM TABLE(StringToRows(@inputParam, ',')) AS test);
OPEN CURSOR1;
END P1
这篇关于在存储过程中将逗号分隔值作为 IN 参数传递的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!