如何在java中调用包含用户定义类型的oracle存储过程？ [英] How to call oracle stored procedure which include user-defined type in java?

问题描述

我有以下存储过程：

procedure getInfo ( p_ids IN IDS_TABLE, p_details OUT cursor )

输入 IDS_TABLE 是：

create or replace type IDS_TABLE as table of IDS    

create or replace type IDS as object ( id1 NUMBER, id2 NUMBER, id3 NUMBER )

我怎样才能在Java中调用getInfo？

How can I call getInfo in Java?

推荐答案

手动设置Oracle SQL对象和Java对象之间的链接并非易事。特别是，用户定义对象的数组（或嵌套表）比标准数据类型的数组从Java传递到Oracle更复杂。换句话说，使用签名调用过程更容易：

Setting up a link between Oracle SQL objects and java objects manually is not a trivial task. In particular, arrays (or nested tables) of user-defined objects are more complex to pass from java to Oracle than arrays of standard datatypes. In other words, it is easier to call a procedure with signature:

(TABLE OF NUMBER, TABLE OF NUMBER, TABLE OF NUMBER)`

比签名为的程序：

(TABLE OF (NUMBER, NUMBER, NUMBER))   <- your case

您可以在程序周围编写一个包装器，将第二种情况转换为第一种情况。

You can write a wrapper around your procedure to transform the second case into the first case.

话虽这么说，映射你的程序到目前为止并非不可能。以下示例是主要受Tom Kyte的帖子启发。 Tom描述了如何使用 oracle.sql.ARRAY 映射 TABLE OF NUMBER 。在您的情况下，我们还必须使用 oracle.sql.STRUCT 来映射 IDS SQL对象。

That being said, it is by far not impossible to map your procedure. The following example is largely inspired by a post by Tom Kyte. Tom describes how to map a TABLE OF NUMBER using oracle.sql.ARRAY. In your case we will also have to use oracle.sql.STRUCT to map the IDS SQL object.

您可能还想浏览Oracle JDBC文档，特别是使用Oracle对象类型。

You may also want to browse the Oracle JDBC doc, in particular the chapter Working with Oracle Object Types.

首先是类似于你的设置：

First is a setup similar to yours:

SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER );
  2  /
Type created

SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS;
  2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
  2  BEGIN
  3     FOR i IN 1 .. p_ids.COUNT LOOP
  4        dbms_output.put_line(p_ids(i).id1
  5                             || ',' || p_ids(i).id2
  6                             || ',' || p_ids(i).id3);
  7     END LOOP;
  8  END getInfo;
  9  /     
Procedure created

这是java程序：

SQL> CREATE OR REPLACE
  2  AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  import oracle.sql.*;
  7  import oracle.jdbc.driver.*;
  8  
  9  public class ArrayDemo {
 10  
 11     public static void passArray() throws SQLException {
 12  
 13        Connection conn =
 14           new OracleDriver().defaultConnection();
 15  
 16  
 17        StructDescriptor itemDescriptor =
 18           StructDescriptor.createDescriptor("IDS",conn);
 19  
 20        Object[] itemAtributes = new Object[] {new Integer(1),
 21                                               new Integer(2),
 22                                               new Integer(3)};
 23        STRUCT itemObject1 = new STRUCT(itemDescriptor,conn,itemAtributes);
 24  
 25        itemAtributes = new Object[] {new Integer(4),
 26                                      new Integer(5),
 27                                      new Integer(6)};
 28        STRUCT itemObject2 = new STRUCT(itemDescriptor,conn,itemAtributes);
 29  
 30        STRUCT[] idsArray = {itemObject1,itemObject2};
 31  
 32        ArrayDescriptor descriptor =
 33           ArrayDescriptor.createDescriptor( "IDS_TABLE", conn );
 34  
 35        ARRAY array_to_pass =
 36           new ARRAY( descriptor, conn, idsArray );
 37  
 38        OraclePreparedStatement ps =
 39           (OraclePreparedStatement)conn.prepareStatement
 40           ( "begin getInfo(:x); end;" );
 41  
 42        ps.setARRAY( 1, array_to_pass );
 43        ps.execute();
 44  
 45     }
 46  }
 47  /
Java created

我们称之为：

SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME 'ArrayDemo.passArray()';
  5  /
Procedure created

SQL> exec show_java_calling_plsql ;
1,2,3
4,5,6

PL/SQL procedure successfully completed

这篇关于如何在java中调用包含用户定义类型的oracle存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！