NUMBER_ARRAY的prepareStatement设置为null无效 [英] preparedStatement setting null for NUMBER_ARRAY doesnt work

问题描述

ArrayDescriptor arrayDescriptor =
    ArrayDescriptor.createDescriptor("NUMBER_ARRAY", conn);

if (parameter != null) {
    ARRAY oracleArray =
            new ARRAY(arrayDescriptor, conn, intList.toArray());
    ps.setArray(i, oracleArray);
} 
else {
    ps.setNull(i, Types.ARRAY, "NUMBER_ARRAY");
}

在上面的代码中，我试图将oracleArray设置为null.它不会引发任何异常.但是我猜Oracle不会把它当作null.传递null的方式可能是什么，我的意思是将一个列表设置为null，这是Orcle DB可以将其视为NUMBER_ARRAY(null)的原因，该列表随后将传递给存储过程(SP). SP已准备好接受空列表.如果它为空列表，则不返回任何内容.如果它不是一个空列表，它将给出结果.这是一个有效的方案.

In the above code I was trying to set oracleArray the to null. It does'nt throw any exception. But Oracle doesnt take it as null I guess. What could be the way to pass in a null, I mean to set a list to null what a orcle DB could take it as NUMBER_ARRAY(null) which will be later passed to a Stored Procedure(SP). The SP is ready to take a null list. If it is a null list it returns nothing. And if it is not a null list it will give the results. this is a valid scenario.

推荐答案

对于空数组，空数组和仅包含单个NULL值的数组，您似乎有些困惑.

It seems you have some confusion about null arrays, empty arrays and arrays that contain only a single NULL value.

NULL数组不存在数组，就像NULL数字不存在数字一样.空数组是存在的数组，但其中包含0个元素.两者都与NUMBER_ARRAY(null)不同，NUMBER_ARRAY(null)是一个包含单个NULL值的数组.

A NULL array is the absence of an array, in the same way that a NULL number is the absence of a number. An empty array is an array that exists, but has 0 elements in it. Both are different from NUMBER_ARRAY(null), which is an array that contains a single NULL value.

数组上的COUNT方法返回数组中元素的数量，说明了这三个元素之间的区别.

The COUNT method on an array, which returns the number of elements in the array, provides an illustration of the differences between these three.

首先，创建一个NULL数组:

SQL> declare
  2    l_null_array     number_array  := null;
  3  begin
  4    dbms_output.put_line('Count: ' || l_null_array.COUNT);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 4

在这里，我们得到一个错误.我们找不到l_null_array中有多少个元素，因为我们没有数组来查找其元素数.

Here, we get an error. We can't find out how many elements there are in l_null_array because we don't have an array to find the number of elements of.

第二，一个空数组:

SQL> declare
  2    l_empty_array    number_array  := number_array();
  3  begin
  4    dbms_output.put_line('Count: ' || l_empty_array.COUNT);
  5  end;
  6  /

Count: 0

PL/SQL procedure successfully completed.

在这里，我们可以找到一个空数组中的元素数量，该数量为零.

Here, we can find the number of elements in an empty array, and that number is zero.

最后，一个仅包含NULL的数组:

Finally, an array containing only NULL:

SQL> declare
  2    l_array_containing_null    number_array  := number_array(null);
  3  begin
  4    dbms_output.put_line('Count: ' || l_array_containing_null.COUNT);
  5  end;
  6  /
Count: 1

PL/SQL procedure successfully completed.

此数组中有一个元素，而那个元素是NULL.

This array has one element within it, and that one element is NULL.

请注意，您可以根据需要将任意数量的参数传递给NUMBER_ARRAY构造函数，这些值将是数组的初始内容.例如，NUMBER_ARRAY(1, 4, 18, 11, 22, 6)创建一个包含6个元素的数字数组.

Note that you can pass as many arguments as you like to the NUMBER_ARRAY constructor function, and these values will be the initial contents of the array. For example, NUMBER_ARRAY(1, 4, 18, 11, 22, 6) creates a number array with 6 elements in it.

那么，我们如何使用JDBC设置每种数组?

So, how can we set each kind of array using JDBC?

• 要设置NULL数组，请使用

ps.setNull(i, Types.ARRAY, "NUMBER_ARRAY");

就像上面一样.

对于空数组，请使用:

ps.setArray(i, new ARRAY(arrayDescriptor, conn, new Integer[0]));

对于仅包含单个NULL值的数组，请使用

For an array containing a single NULL value only, use

ps.setArray(i, new ARRAY(arrayDescriptor, conn, new Integer[] { null }));

在这些示例中，我使用的是Integer数组，但其他数字类型也应适用.

I'm using an Integer array in these examples, but other numeric types should work too.

这篇关于NUMBER_ARRAY的prepareStatement设置为null无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！