循环值，创建动态查询并添加到结果集中 [英] Looping on values, creating dynamic query and adding to result set
问题描述
我有以下问题.我是一位经验丰富的Java程序员，但是对SQL和PL/SQL有点不了解.
I have the following problem. I am an experienced Java programmer but am a bit of a n00b at SQL and PL/SQL.
我需要执行以下操作.
1将一些数组和其他一些变量传递给过程
1 Pass in a few arrays and some other variables into a procedure
2循环处理数组中的值(它们都具有相同数量的项)并动态创建SQL语句
2 Loop on the values in the arrays (they all have the same number of items) and dynamically create an SQL statement
3运行此语句并将其添加到结果集中(这是过程的OUT参数)
3 Run this statement and add it to the result set (which is an OUT parameter of the procedure)
我已经具有动态创建SQL查询，运行它并将结果添加到结果集(即REF CURSOR)的经验，但是我不确定如何循环并添加每个结果调用查询到相同的结果集.我什至不确定这是否可能.
I already have experience of creating an SQL query on the fly, running it and adding the result to a result set (which is a REF CURSOR) but I'm not sure how I'd loop and add the results of each call to the query to the same result set. I'm not even sure if this is possible.
这是我到目前为止所拥有的(为简单起见，对代码进行了编辑).我知道这是错误的，因为我只是将RESULT_SET的内容替换为最新的查询结果(这在调用此过程的Java中已得到确认).
Here's what I have so far (code edited for simplicity). I know it's wrong because I'm just replacing the contents of the RESULT_SET with the most recent query result (and this is being confirmed in the Java which is calling this procedure).
任何人和所有帮助将不胜感激.
Any and all help would be greatly appreciated.
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;
推荐答案
流水线表函数似乎更适合您想要的东西，尤其是当您正在做的是检索数据时.参见 http://www.oracle-base.com/articles/misc/pipelined-table-functions.php
A pipelined table function seems a better fit for what you want, especially if all you're doing is retrieving data. See http://www.oracle-base.com/articles/misc/pipelined-table-functions.php
您要做的是为输出行创建一种类型.因此，在您的情况下，您将创建一个对象，例如
What you do is create a type for your output row. So in your case you would create an object such as
CREATE TYPE get_data_faster_row AS OBJECT(
seq NUMBER(15,2),
value VARCHAR2(10),
item VARCHAR2(10)
);
然后创建一个表类型，该表类型是由上面的行类型组成的表
Then create a table type which is a table made up of your row type above
CREATE TYPE get_data_faster_data IS TABLE OF get_data_faster_row;
然后创建您的表函数，该函数以流水线方式返回数据. Oracle中的流水线有点像.net中的收益率回报(不确定您是否熟悉).您会找到所需的所有行，然后一次循环将它们管道化".函数完成后，返回的表将包含您通过管道输出的所有行.
Then create your table function that returns the data in a pipelined manner. Pipelined in Oracle is a bit like a yield return in .net (not sure if you're familiar with that). You find all of the rows that you want and "pipe" them out one at a time in a loop. When your function completes the table that's returned consists of all the rows you piped out.
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;
在下面亚历克斯的评论之后，您需要类似这样的东西.我尚无法对此进行测试，但是它应该可以帮助您入门:
Following Alex's comment below, you need something like this. I haven't been able to test this but it should get you started:
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;
下面Alex的评论中的其他信息对回答很有帮助:
Extra info from Alex's comment below useful for the answer:
您可以有来自不同来源的多个循环，只要 来自每个对象的数据被放入相同的对象类型，您可以保持 使用函数中任何位置的管道行语句将它们抽出. 调用方将它们视为表格，其中包含按管道顺序排列的行 他们.而不是调用过程并获得结果集作为输出 参数，您可以查询，从中选择seq，value，item table(package.get_data_faster(a，b，c，d))，当然你可以 如果它们传递的顺序不是什么，仍然有一个order by子句 你想要的.
you can have multiple loops from different sources, and as long as the data from each be put into the same object type, you can just keep pumping them out with pipe row statements anywhere in the function. The caller sees them as a table with the rows in the order you pipe them. Rather than call a procedure and get a result set as an output parameter, you can query as select seq, value, item from table(package.get_data_faster(a, b, c, d)), and of course you can still have an order by clause if the order they're piped isn't what you want.
这篇关于循环值，创建动态查询并添加到结果集中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
