将 REFCURSOR 类型变量从 Java 发送到 Oracle 存储过程 [英] send REFCURSOR type variable to Oracle Stored procedure from Java

问题描述

我已经找遍了这个，似乎找不到答案.如果这已经发布，请指出我的方向.

I´ve looked all over for this and can´t seem to find an answer. If this has already been posted, please point me in that direction.

我有一个必须从 Java 调用的 Oracle 存储过程.这个 SP 请求一个 REFCURSOR 类型作为 IN 参数(因为我必须向它发送一个 REFCURSOR).

I have an Oracle Stored procedure that I have to invoke from Java. This SP ask´s for a REFCURSOR type as an IN parameter (as in I have to SEND a REFCURSOR to it).

我知道如何从 SP 接收 REFCURSOR 作为响应(例如，使用 callablestatement 和 Stmt.registerOutParameter(3, OracleTypes.CURSOR)；这不是我想要的.

I know how to receive a REFCURSOR as a response from an SP (using a callablestatement and Stmt.registerOutParameter(3, OracleTypes.CURSOR); for example. This is NOT what I want.

我无法修改 SP(可悲的是......)

I can´t modify the SP (sadly enough...)

我需要示例代码来:

-在 Java 中创建 REFCURSOR

-create the REFCURSOR in Java

-将其作为参数发送给 SP.

-Send it as a parameter to the SP.

感谢您提供的任何帮助.

Thx a lot for any help provided.

推荐答案

来自 Oracle 文档:

使用 REF CURSORs 是将查询结果从 Oracle 数据库返回到客户端应用程序的最强大、灵活和可扩展的方法之一.

Introduction to REF CURSORs

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

REF CURSOR 是一种 PL/SQL 数据类型，其值是数据库上查询工作区的内存地址.本质上，REF CURSOR 是指向数据库结果集的指针或句柄.REF CURSORs 通过 OracleRefCursor ODP.NET 类表示.

REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class.

REF CURSORs 具有以下特点:

• REF CURSOR 指的是数据库上的内存地址.因此，客户端必须在 REF CURSOR 的生命周期内连接到数据库才能访问它.

• A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.

REF CURSOR 涉及额外的数据库往返.当 REF CURSOR 返回给客户端时，直到客户端打开 REF CURSOR 并请求数据时，才会返回实际数据.请注意，在用户尝试读取数据之前，不会检索数据.

A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.

REF CURSOR 不可更新.REF CURSOR 表示的结果集是只读的.您不能使用 REF CURSOR 更新数据库.

A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.

REF CURSOR 不可向后滚动.REF CURSOR 表示的数据以只进、串行方式访问.您不能在 REF CURSOR 内放置一个记录指针来指向结果集中的随机记录.

A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.

REF CURSOR 是一种 PL/SQL 数据类型.您在 PL/SQL 代码块中创建并返回 REF CURSOR.

A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

因此，您不能在 Java 中创建 REF CURSOR；相反，您需要在数据库中创建它，将其传递给您的 Java 应用程序，然后将其传递回您正在调用的过程(或者在数据库中完成所有操作).

So, you cannot create a REF CURSOR in Java; instead you will need to create it in the database, pass it to your Java application and then pass it back to the procedure you are calling (or else do it all in the database).

这篇关于将 REFCURSOR 类型变量从 Java 发送到 Oracle 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！