如何从Java中使用Oracle中的关联数组调用过程 [英] How to call a procedure with associative arrays in Oracle from Java

问题描述

我有一个如下所示的存储过程:

I have an stored procedure that looks like this:

TYPE ref_cursor IS REF CURSOR;    
TYPE parametro IS RECORD (
    nombre      VARCHAR2(50), -- I want to remove this value and make it the key of the table instead.
    valor       VARCHAR2(32000),
    tipo        VARCHAR2(1),
    sentencia   VARCHAR2(32000)
);
TYPE parametros IS TABLE OF parametro INDEX BY VARCHAR2(50);

PROCEDURE build_cursor (
    params IN parametros
    results OUT ref_cursor
);

从build_cursor过程中，我希望能够通过其键访问表的内容.

And from the build_cursor procedure, I want to be able to access to the contents of the table by its key.

parametros('key');

但是，我不知道如何从Java构建关联数组，我只看到了简单数组的示例，即:TYPE parametros IS TABLE OF parametro;

However, I don't know how to build an associative array from Java, I have seen only examples of simple arrays, i.e: TYPE parametros IS TABLE OF parametro;

如何从Java调用build_cursor过程?

How can I call the build_cursor procedure from java?

我读了这篇文章:如何调用在Java中包含用户定义类型的oracle存储过程?，但是我不知道我必须对其创建关联数组的java示例进行哪些更改； 我将当前元素的键放在哪里?

I read this: How to call oracle stored procedure which include user-defined type in java? but I don't know what changes do I have to make to his java example for creating the associative array; Where do I put the Key of the current element?

这是Oracle的有效测试.

This is a working test from Oracle.

params('key').nombre        := 'key'; -- I want this to be removed because it's the key.
params('key').valor         := 'Roger';
params('key').tipo          := 'V';
params('key').sentencia     := 'Something';
-- Call the procedure
pk_sql_utils.build_cursor(
    params => params,
    results => :results
);

推荐答案

只有jdbc而不是PL可以引用SQL对象/SQL对象.关联数组是PL/SQL对象，因此您将无法从jdbc中看到"它们.

Only SQL objects can be referenced by jdbc, not PL/SQL objects. Associative arrays are PL/SQL objects, so you won't be able to "see" them from jdbc.

您可以对SQL对象使用包装PL/SQL函数(关联数组类似于一个嵌套的索引表和一个嵌套的值表).

You could use a wrapper PL/SQL function with SQL objects (an associative array is analogous to one nested table of indexes and one nested table of values).

您还可以使用临时表:jdbc批处理插入到临时表中，包装过程(或PL/SQL块)读取临时表并调用您的过程.

You could also use a temporary table: jdbc batch inserts into the temp table, a wrapper procedure (or PL/SQL block) reads the temp table and calls your procedure.

这篇关于如何从Java中使用Oracle中的关联数组调用过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！