从Java访问存储过程的％ROWTYPE [英] Accessing %ROWTYPE of Stored Procedure from Java

问题描述

我有一个看起来像的存储过程:

 PROCEDURE get_curx(    p_buf     IN  ni_imsi%ROWTYPE,
                        p_bufx    IN  ni_imsi%ROWTYPE,
                        p_cur     OUT CurTyp,
                        p_where   IN  VARCHAR2 DEFAULT '',
                        p_orderby IN  VARCHAR2 DEFAULT '',
                        p_max     IN  NUMBER   DEFAULT 0,
                        p_lock    IN  NUMBER   DEFAULT 0,
                        p_hint    IN  VARCHAR2 DEFAULT 'NONE',
                        p_debug   IN  NUMBER   DEFAULT 0,
                        p_count   IN  BOOLEAN  DEFAULT FALSE);

我正在从这样的JAVA程序调用此过程:

CallableStatement cs = connection.prepareCall("{call ni_imsi_pkg.get_curx(?,?,?,?,?,?)}");

            cs.setObject( 1, ? );  // i have no clue what to mention here
            cs.setObject( 2, ? );  //i have no clue what to mention here

            cs.registerOutParameter(3, OracleTypes.CURSOR);
            cs.setString(4, " WHERE current_state = 60000 AND rownum <= 2 ");
            cs.setString(5, " ORDER BY imsi_number");
            cs.setInt(6, 5);

但是我不知道如何设置前两个参数.请帮助我.谢谢

推荐答案

正如Mike所说，您不能在JDBC调用中直接引用行类型，因为行类型仅在PL/SQL和所有使用的类型中有效驱动程序必须在SQL级别定义.

As Mike said, you can't directly reference a row type in a JDBC call, as row types are only valid within PL/SQL and all the types used by the driver have to be defined at SQL level.

您可以定义自己的SQL对象类型以隐藏表结构(如果表被更改，则必须记住要更新)，以及一个包装器过程，该过程采用该类型并将其转换为对实数的调用程序.这是一个基于对偶的演示，因为我不知道您的真实表结构:

You could define your own SQL object type that shadows your table structure (which you'll have to remember to update if the table is altered), and a wrapper procedure that takes that type and converts it into a call to your real procedure. This is a demo based on dual, since I don't know your real table structure:

create type ni_imsi_rowtype as object (dummy varchar2(1)) -- use your real table's columns/types
/

create package ni_imsi_pkg as
  procedure get_curx(p_buf dual%rowtype, p_cur out sys_refcursor);
  procedure get_curx_wrapper(p_buf ni_imsi_rowtype, p_cur out sys_refcursor);
end ni_imsi_pkg;
/

create package body ni_imsi_pkg as
  -- original procedure, simplified for demo
  procedure get_curx(p_buf dual%rowtype, p_cur out sys_refcursor) is
  begin
    open p_cur for select * from dual where dummy = p_buf.dummy;
  end;

  -- wrapper procedure taking new type instead of rowtype
  procedure get_curx_wrapper(p_buf ni_imsi_rowtype, p_cur out sys_refcursor) is
    l_buf dual%rowtype;
  begin
    l_buf.dummy := p_buf.dummy;
    get_curx(l_buf, p_cur);
  end;
end ni_imsi_pkg;
/

然后在Java端，您可以填充该内容并将其作为STRUCT发送:

Then on the Java side you can populate and send this as a STRUCT:

// Object array containing the values corresponding to your row type
Object[] rowObj = { "X" };
// Struct based on the SQL type you created
StructDescriptor structDesc = StructDescriptor.createDescriptor("NI_IMSI_ROWTYPE", conn);
STRUCT rowStruct = new STRUCT(structDesc, conn, rowObj);

// Call wrapper function instead of real one
cs = conn.prepareCall("{ call ni_imsi_pkg.get_curx_wrapper(?,?) }");
// Pass the struct defined earlier
cs.setObject(1, rowStruct);
cs.registerOutParameter(2, OracleTypes.CURSOR);
// and other arguments for your real calll

如果您不能修改实际的软件包，则可以为包装器创建一个新的软件包，或者通过一个简单的过程创建一个软件包.或者甚至可以在匿名块中进行转换，尽管这会使Java代码更加复杂:

If you can't modify your real package then you could create a new one for the wrapper, or a simple procedure; or you could even do the conversion in an anonymous block, though that makes the Java code more complicated:

cs = (OracleCallableStatement) conn.prepareCall(
    "declare l_typ ni_imsi_rowtype; l_buf dual%rowtype; "
        + "begin l_typ := ?; l_buf.dummy := l_typ.dummy; ni_imsi_pkg.get_curx(l_buf, ?); "
        + "end;"
);

...仍绑定相同的结构，因此仍需要SQL类型.只有语句会更改，但是现在无需包装即可调用原始过程.

... still binding the same struct so the SQL type is still required. Only the statement changes, but it can now call the original procedure without a wrapper.

这篇关于从Java访问存储过程的％ROWTYPE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！