无法将数组传递给DB2中的存储过程 - SQL PL - Windows

I am unable to call a stored procedure which has an input parameter as an integer array.

我无法调用具有输入参数作为整数数组的存储过程。

The stored procedure declaration is as follows

存储过程声明如下

      CREATE OR REPLACE PROCEDURE TESTSCHEMA.TESTARRAY
      (IN CHECKSTATUS INTEGER,
      IN JOBID INTARRAY)

The array was declared like this

数组被声明为这样

      CREATE TYPE INTARRAY AS INTEGER ARRAY[]@

When I try to call the procedure using

当我尝试使用时调用该过程

      CALL TESTSCHEMA.TESTARRAY( 1 , array[21,22,23] )@

I get the following error -

我收到以下错误 -

An unexpected token "ARRAY[" was found following "ARRAY[". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.63.123 SQL Code: -104, SQL State: 42601'

在“ARRAY [”之后发现了意外的令牌“ARRAY [”。预期的令牌可能包括：“”.. SQLCODE = -104，SQLSTATE = 42601，DRIVER = 3.63.123 SQL代码：-104，SQL状态：42601'

I cannot seem to find any other way to do this? Can anyone please help with this?

我似乎无法找到任何其他方法来做到这一点？任何人都可以帮忙吗？

Also need to find a way to pass the array in Java later.

还需要找到一种方法在Java中传递数组。

1 个解决方案

#1

SQL PL arrays can only be used in SQL PL context. You'll need to declare a variable of the INTARRAY type and call your procedure, using that variable, from a compound SQL statement:

SQL PL数组只能在SQL PL上下文中使用。您需要声明一个INTARRAY类型的变量，并使用该变量从复合SQL语句中调用您的过程：

db2inst1@blusrv:~> db2 "create type INTARRAY AS INTEGER ARRAY[]"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 "create or replace procedure testarray(in checkstatus integer, in jobid intarray) begin call dbms_output.put_line('testarray'); end"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.
db2inst1@blusrv:~> db2 "begin declare v intarray; set v =  array[21,22,23]; call testarray(1,v); end"
DB20000I  The SQL command completed successfully.

testarray