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

Question

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

存储过程声明如下

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

数组是这样声明的

      CREATE TYPE INTARRAY AS INTEGER ARRAY[]@

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

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

我收到以下错误 -

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'

我似乎找不到任何其他方法来做到这一点？有人可以帮忙解决这个问题吗？

后面还需要想办法在Java中传递数组。

Answer 1

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