对值进行循环，创建动态查询并添加到结果集中

Question

我有以下问题。我是一名经验丰富的Java程序员，但在SQL和PL/SQL方面有点不在行。

我需要做以下事情。

1将一些数组和一些其他变量传递到过程中

2循环数组中的值(它们都有相同数量的项)并动态创建SQL语句

3运行此语句并将其添加到结果集(这是过程的OUT参数)

我已经有过动态创建SQL查询、运行它并将结果添加到结果集(这是一个REF CURSOR)的经验，但我不确定如何循环并将每次调用查询的结果添加到同一结果集。我甚至不确定这是否可能。

以下是我到目前为止所拥有的(为了简单起见，对代码进行了编辑)。我知道这是错误的，因为我只是用最新的查询结果替换RESULT_SET的内容(这在调用此过程的Java中得到了证实)。

如有任何帮助，我们将不胜感激。

TYPE REF_CURSOR IS REF CURSOR;  


PROCEDURE GET_DATA_FASTER(in_seq_numbers IN seq_numbers_array, in_values IN text_array, in_items IN text_array, list IN VARCHAR2, RESULT_SET OUT REF_CURSOR) AS  
query_str VARCHAR2(4000);
seq_number NUMBER;
the_value VARCHAR2(10);
the_item VARCHAR2(10);
BEGIN
FOR i IN 1..in_seq_numbers.COUNT
LOOP
seq_number := in_seq_numbers(i);
the_value := trim(in_values(i));
the_item := trim(in_items(i));
query_str := 'SELECT distinct '||seq_number||' as seq, value, item
FROM my_table ai';                    
query_str := query_str || '
WHERE ai.value = '''||the_value||''' AND ai.item = '''||the_item||'''
AND ai.param = ''BOOK''
AND ai.prod in (' || list || ');
OPEN RESULT_SET FOR query_str;
END LOOP;
EXCEPTION WHEN OTHERS THEN
RAISE;
END GET_DATA_FASTER;

Answer 1

流水线表函数似乎更适合您的需求，尤其是当您所做的只是检索数据时。看见http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

您要做的是为输出行创建一个类型。所以在你的情况下，你会创建一个对象，比如

CREATE TYPE get_data_faster_row AS OBJECT(
seq    NUMBER(15,2),
value  VARCHAR2(10),
item   VARCHAR2(10)
);

然后创建一个表类型，它是由上方的行类型组成的表

CREATE TYPE get_data_faster_data IS TABLE OF get_data_faster_row;

然后创建以流水线方式返回数据的表函数。Oracle中的流水线有点像.net中的收益回报(不确定您是否熟悉)。您可以找到所需的所有行，并在循环中一次"管道"输出一行。当函数完成时，返回的表由您管道输出的所有行组成。

CREATE FUNCTION Get_Data_Faster(params) RETURN get_data_faster_data PIPELINED AS
BEGIN
-- Iterate through your parameters 
--Iterate through the results of the select using
-- the current parameters. You'll probably need a 
-- cursor for this
PIPE ROW(get_data_faster_row(seq, value, item));
LOOP;
LOOP;
END;

编辑：根据亚历克斯在下面的评论，你需要这样的东西。我还没能测试这个，但它应该会让你开始：

CREATE FUNCTION Get_Data_Faster(in_seq_numbers IN seq_numbers_array, in_values IN text_array, in_items IN text_array, list IN VARCHAR2) RETURN get_data_faster_data PIPELINED AS
TYPE r_cursor IS REF CURSOR;
query_results r_cursor;
results_out get_data_faster_row := get_data_faster_row(NULL, NULL, NULL);
query_str VARCHAR2(4000);
seq_number NUMBER;
the_value VARCHAR2(10);
the_item VARCHAR2(10);
BEGIN
FOR i IN 1..in_seq_number.COUNT
LOOP
seq_number := in_seq_numbers(i);
the_value := trim(in_values(i));
the_item := trim(in_items(i));
query_str := 'SELECT distinct '||seq_number||' as seq, value, item
FROM my_table ai';                    
query_str := query_str || '
WHERE ai.value = '''||the_value||''' AND ai.item = '''||the_item||'''
AND ai.param = ''BOOK''
AND ai.prod in (' || list || ');
OPEN query_results FOR query_str;
LOOP
FETCH query_results INTO 
results_out.seq,
results_out.value,
results_out.item;
EXIT WHEN query_results%NOTFOUND;
PIPE ROW(results_out);
END LOOP;
CLOSE query_results;
END LOOP;
END;

亚历克斯下面评论中的额外信息对答案很有用：

您可以拥有来自不同来源的多个循环，只要每个对象的数据都被放入相同的对象类型中，您可以只保留在函数中的任何位置使用管道行语句将它们输出。调用者将它们视为一个表，其中的行按管道顺序排列他们而不是调用过程并将结果集作为输出参数，您可以从中作为select seq、value、item进行查询表(package.get_data_faster(a，b，c，d))，当然您可以如果他们发送的订单不是你想要的。