在 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' 是放在一张桌子上.

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 数组调用上述存储过程:

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