将逗号分隔值作为IN参数传递到存储过程中 [英] Passing comma separated value as an IN parameter in stored procedure

问题描述

我是DB2查询的新手。

这里，我将一个逗号分隔的值作为IN参数传递给存储过程。我想在这些值的基础上搜索。

 从USER中选择*，其中user_id in（IN_User）; 
  

这里，IN_User将具有类型的值（'val1'，'val2'，'val3' ）

它应该返回所有具有val1或val2或val3的行作为User_id。尽管我知道这可以使用UDF完成，但是我想知道有没有其他方法可以在没有UDF的情况下执行。

解决方案

p>请创建一个函数来分割逗号分隔的字符串

请参阅以下函数

  CREATE FUNCTION StringToRows（
 cString1 CLOB（10 M），
 cStringSplitting1 VARCHAR（10））
 RETURNS TABLE（Lines VARCHAR（500））
 SPECIFIC StringToRows_Big 
确定
无外部行动
包含SQL 
开始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; 
 
返回
 TEMP1（STRING）as（values（cString1）），
 TEMP2（Lines，STRING_left）as 
（SELECT 
 SUBSTR ，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 
  

请参阅示例存储过程调用函数

  CREATE PROCEDURE TEST_USR（IN @inputParam CLOB（10 M））
 SPECIFIC TEST_USR 
动态结果集1 
 P1 ：BEGIN 
 DECLARE CURSOR1 CURSOR WITH RETURN FOR 
从USER中选择*，其中user_id IN（SELECT * FROM TABLE（StringToRows（@inputParam，'，'）））AS test）; 
 OPEN CURSOR1; 
 END P1 
  

I am new to DB2 queries.

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);

Here, IN_User will have values of the kind ('val1','val2','val3')

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

Please see the below function

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