将数组作为参数传递给DB2存储过程 [英] Pass Array as parameter in DB2 Stored Procedure

问题描述

我试图创建一个将数组作为参数的存储过程，并在WHILE循环中迭代该数组并将字符添加到表中。

I am trying to create a stored procedure which takes an array as a parameter and in the WHILE loop iterates through this array and adds the chars into a table.

例如，如果我有一个['a'，'b'，'c']数组，我希望将其传递到我的存储过程中，并将字符'a'，'b'和'c'放置到a中

For example if I had an array of ['a','b','c'] I would want to pass this into my stored procedure and the characters 'a' , 'b' and 'c' to be placed into a table.

我的SP成功创建，但是在尝试调用过程时遇到问题。有人可以指出我如何传递数组吗？我的过程如下。。。

My SP creates successfully, but I am having issues when I try to call my procedure. Can anybody point me towards how to pass in an array? My procedure is as follows....

    DROP PROCEDURE DB.LWRH_DYNAMIC_SP@
create type stringArray as VARCHAR(100) array[100]@
CREATE PROCEDURE DB.LWRH_SP
(
    IN list stringArray
)
LANGUAGE SQL
BEGIN
    DECLARE i, MAX INTEGER;
    DECLARE c CHAR(1);
    SET i = 0;
    SET MAX = CARDINALITY(list);
    WHILE i <= MAX DO
    SET c = list[i];
    INSERT INTO schema.test ("SERVICE TYPE")values (c);
    END WHILE;

END@

CALL DB.LWRH_SP('')@ 

推荐答案

使用 DB2数组构造函数，以使用数组类型的输入参数来调用存储过程。

Use the DB2 array constructor to call a stored procedure with array typed input parameters.

使用数组构造函数创建整数数组的示例：

An example of creating an array of integers using the array constructor:

ARRAY[1, 2, 3]

对于您的存储过程示例：

For your stored procedure example:

create type stringArray as VARCHAR(100) array[100]@
CREATE PROCEDURE DB.LWRH_SP
(
    IN list stringArray
)
LANGUAGE SQL
BEGIN
    DECLARE i, MAX INTEGER;
    DECLARE c CHAR(1);
    SET i = 0;
    SET MAX = CARDINALITY(list);
    WHILE i <= MAX DO
    SET c = list[i];
    INSERT INTO schema.test ("SERVICE TYPE")values (c);
    END WHILE;
END@

您可以使用以下VARCHAR数组调用上述存储过程： / p>

You can call the above stored procedure with an array of VARCHAR using:

CALL DB.LWRH_SP(ARRAY['a', 'b', 'c'])@ 

注意：以我的经验，某些SQL开发人员工具（例如DBArtisan）可能不适用于上述情况使用数组构造函数进行存储过程调用的语法，但是它确实可以与Linux命令行db2工具一起使用。 DB2 UDB CLP 。

NOTE: In my experience some SQL developer tools (eg. DBArtisan) might not work with the above syntax of stored procedure call using the array constructor, but it definitely works with the Linux command line db2 tool aka. DB2 UDB CLP.

这篇关于将数组作为参数传递给DB2存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！