从JDBC调用Oracle面向对象的PL/SQL成员过程 [英] Call Oracle object-oriented PL/SQL member procedures from JDBC

问题描述

在面向对象的PL/SQL中，我可以将成员过程和函数添加到类型中.此处给出一个示例:

In object-oriented PL/SQL, I can add member procedures and functions to types. An example is given here:

create type foo_type as object (
  foo number,

  member procedure proc(p in number),
  member function  func(p in number) return number
);

create type body foo_type as 
  member procedure proc(p in number) is begin
    foo := p*2;
  end proc;

  member function func(p in number) return number is begin
    return foo/p;
  end func;
end;

来自: http://www.adp-gmbh.ch/ora/plsql/oo/member.html

然后在PL/SQL中，我可以像下面这样调用这些成员过程/函数:

In PL/SQL, I can then call these member procedures/functions like this:

declare
    x foo_type;
begin
    x := foo_type(5);
    x.proc(10);
    dbms_output.put_line(x.func(2));
end;

如何使用JDBC的CallableStatement做到这一点?我似乎很难在文档中找到这个.

How can I do it with JDBC's CallableStatement? I can't seem to find this in the documentation easily.

注意:这是一种可能，它内联了类型构造函数:

NOTE: This is one possibility, inlining the type constructor:

CallableStatement call = c.prepareCall(
    " { ? = call foo_type(5).func(2) } ");

但是我正在寻找的东西是这样的(使用java.sql.SQLData作为参数):

But what I'm looking for is something like this (using java.sql.SQLData as a parameter):

CallableStatement call = c.prepareCall(
    " { ? = call ?.func(2) } ");

此外，成员函数，过程可能会修改对象.如何才能用Java重新获得修改后的对象?

Also, member functions, procedures may modify the object. How can I get the modified object back in Java?

推荐答案

在jdbc中，您可以使用out变量来解析和执行PL/SQL块.您可以准备一个可调用的语句，例如:

In jdbc you can parse and execute PL/SQL blocks with out variables. You could prepare a callable statement such as:

declare
    x foo_type;
begin
    x := foo_type(5);
    x.proc(10);
    ? := x.func(2);
end;

然后，您可以使用 CallableStatement.registerOutParameter ，并在执行该语句后，使用适当的get函数来检索该值.

Then you can use CallableStatement.registerOutParameter and after the statement has been executed, use the appropriate get function to retrieve the value.

您可以直接在Java中直接访问FOO_TYPE类型，但是您真的要这样做吗?参见下面的工作示例:

You can access directly a FOO_TYPE type directly in java, but do you really want to do this? See below for a working example:

SQL> create or replace and compile java source named "TestOutParam" as
  2  import java.sql.*;
  3  import oracle.sql.*;
  4  import oracle.jdbc.driver.*;
  5  
  6  public class TestOutParam {
  7  
  8     public static int get() throws SQLException {
  9  
 10        Connection conn =
 11           new OracleDriver().defaultConnection();
 12  
 13        StructDescriptor itemDescriptor =
 14           StructDescriptor.createDescriptor("FOO_TYPE",conn);
 15  
 16        OracleCallableStatement call =
 17           (OracleCallableStatement) conn.prepareCall("declare\n"
 18              + "    x foo_type;\n"
 19              + "begin\n"
 20              + "    x := foo_type(5);\n"
 21              + "    x.proc(10);\n"
 22              + "    ? := x;\n"
 23              + "end;\n");
 24  
 25        call.registerOutParameter(1, OracleTypes.STRUCT, "FOO_TYPE");
 26  
 27        call.execute();
 28  
 29        STRUCT myObj = call.getSTRUCT(1);
 30  
 31        Datum[] myData = myObj.getOracleAttributes();
 32  
 33        return myData[0].intValue();
 34  
 35     }
 36  }
 37  /

这是一个测试类，展示了如何在SQL对象上使用方法registerOutParameter，让我们称之为:

This is a test class to show how you can use the method registerOutParameter on an SQL object, let's call it:

SQL> CREATE OR REPLACE
  2  FUNCTION show_TestOutParam RETURN NUMBER
  3  AS LANGUAGE JAVA
  4  NAME 'TestOutParam.get() return java.lang.int';
  5  /

Function created

SQL> select show_testoutparam from dual;

SHOW_TESTOUTPARAM
-----------------
               20

这篇关于从JDBC调用Oracle面向对象的PL/SQL成员过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！