使用带有JDBC和PL/SQL存储过程的VARRAY的新手简单示例

Question

我试图创建一个简单的示例来了解如何通过基于VARRAY的PL/SQL存储过程将Java程序中的数据存储到Oracle数据库（11.2G）中，然后从Java程序中检索（浮点数组）数据。

我已经尽力把Java程序组合在一起了，但可能会出现一两个错误。我还纠结于如何编写要编写的PL/SQL代码，然后从数据库中读取VARRAY。如有任何建议，我们将不胜感激。

Java程序：

// create example array
double[] myArray = new double[3];
myArray[0] = 1.1;
myArray[1] = 2.2;
myArray[2] = 3.3;
...
// setup call to stored procedure using SQL92 syntax
cs = conn.prepareCall( "{call my_sproc (?,?)}" );
// set IN parameters
cs.setString(1, myArray);
// set OUT parameters
cs.registerOutParameter(2, Types.ARRAY, "my_array");
// execute
cs.execute();
// retrieve array 
double[] returnedArray = new double[3];
returnedArray = cs.getArray(2);
...

PL/SQL存储过程：

create or replace procedure my_sproc ( 
    input_array IN as VARRAY(3) of BINARY_FLOAT,
    output_array OUT as VARRAY(3) of BINARY_FLOAT )
as
begin
-- how to write input_array into any example table?
-- how to read input_array from example table and store in variable: output_array?
end my_sproc;

Answer 1

在PL/SQL中使用VARRAY是非常罕见的，因为您必须指定最大长度。使用基于嵌套表或关联数组的集合要常见得多。

如果你确实想使用基于VARRAY的集合，你可以做一些类似的事情

create type float_array
    is varray(3) of binary_float;
/
create table foo (
  col1 number
);
/
create or replace procedure varray_proc( p_in_arr   in float_array,
                                         p_out_arr out float_array )
as
begin
  for i in 1 .. p_in_arr.count
  loop
    insert into foo( col1 )
      values( p_in_arr(i) );
  end loop;
  select col1*2
    bulk collect into p_out_arr
    from foo;
end;
/

您可以从PL/SQL 调用该过程

SQL> declare
  2    l_in_arr  float_array := float_array( 1.1, 2.2, 3.3 );
  3    l_out_arr float_array;
  4  begin
  5    varray_proc( l_in_arr,
  6                 l_out_arr );
  7    for i in 1 .. l_out_arr.count
  8    loop
  9      dbms_output.put_line( l_out_arr(i) );
 10    end loop;
 11  end;
 12  /
2.20000005E+000
4.4000001E+000
6.5999999E+000
PL/SQL procedure successfully completed.

声明和使用嵌套的表类型会更常见，它最终看起来几乎与VARRAY代码完全相同，只是没有长度限制

create type float_nt
    is table of binary_float;
create or replace procedure varray_proc( p_in_arr   in float_nt,
                                         p_out_arr out float_nt )
as
begin
  for i in 1 .. p_in_arr.count
  loop
    insert into foo( col1 )
      values( p_in_arr(i) );
  end loop;
  select col1*2
    bulk collect into p_out_arr
    from foo;
end;
/

如果您想存储阵列的元素顺序

create table bar (
  col1     number, 
  order_by number
);
create or replace procedure varray_proc( p_in_arr   in float_nt,
                                         p_out_arr out float_nt )
as
begin
  for i in 1 .. p_in_arr.count
  loop
    insert into bar( col1, order_by )
      values( p_in_arr(i), i );
  end loop;
  select col1*2
    bulk collect into p_out_arr
    from bar
   order by order_by;
end;
/