无法通过阵列在DB2存储过程 - SQL PL - 视窗 [英] Unable to pass array to a Stored Procedure in DB2 - SQL PL - Windows

问题描述

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

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

存储过程声明如下：

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

该阵列声明如下

      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 -

这是意外的标记ARRAY [之后发现ARRAY [。预期标记可能包括：.. SQL code = -104，SQLSTATE = 42601，DRIVER = 3.63.123 SQL code：-104，SQL状态：42601

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'

我似乎无法找到任何其他方式做到这一点？任何人都可以请帮助呢？

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

还需要找到一种方法后来通过Java中的数组。

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

推荐答案

SQL PL阵列只能在SQL PL上下文中使用。你需要声明的变量 INTARRAY 键入并调用你的程序，使用该变量，从复合SQL语句：

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:

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

这篇关于无法通过阵列在DB2存储过程 - SQL PL - 视窗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！