如何将输入参数作为用逗号分隔的字符串或 DB2 中 IN 子句的列表传递 [英] how to pass input parameter as a string separated by comma or list for IN clause in DB2

问题描述

我是存储过程的新手，我有一个疑问，我需要将输入参数作为用逗号分隔的字符串或 DB2 中 IN 子句的列表传递.请参阅以下示例程序

I am new in stored procedure ,i have one doubt,I need to pass input parameter as a string separated by comma or list for IN clause in DB2. please see the below sample procedure

 CREATE PROCEDURE  TEST_SP(IN  @listofUsername)
  SPECIFIC TEST_SP DYNAMIC RESULT SETS 1
P1:BEGIN
  DECLARE CURSOR1 CURSOR WITH RETURN FOR  
  SELECT  F_NAME FROM TEST  WHERE USER_NAME IN (@listofusername);
    }
    OPEN CURSOR1;  
END P1 

请指导我，1 上面的存储过程怎么写2 是否可以将任何类型的列表作为输入参数传递，如果可以，哪个会提供更好的性能.

Please guide me, 1 how to write the above stored procedure 2 Is it possible to pass any kind of list as a input parameter ,if yes , which one will give better performance.

推荐答案

您可以创建一个数组数据类型，并将您的参数定义为该数据类型.

You can create an array datatype, and define your parameter as that datatype.

CREATE OR REPLACE TYPE UserList as char(10) ARRAY[100]

我选择 char(10) 是因为它们在我的系统 IBM i 上就是如此.使用适合您的任何东西，以及一个足够大的数组大小以供您使用.

I picked char(10) because that's what they are on my system, IBM i. Use whatever is appropriate for yours, and an array size large enough for whatever use you may have.

将此用作过程参数的类型.

Use this as the type for your procedure parameter.

这篇关于如何将输入参数作为用逗号分隔的字符串或 DB2 中 IN 子句的列表传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！