返回 Oracle 引用游标并附加多个结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/417178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning Oracle ref cursor and appending multiple results
提问by Ricardo Villamil
I have this problem I'm hoping someone knows the answer to. I have an oracle stored procedure that takes a customer id and returns all the customer's orders in a ref_cursor. Oversimplifying it, this is what I have:
我有这个问题，希望有人知道答案。我有一个 oracle 存储过程，它接受一个客户 ID 并在 ref_cursor 中返回所有客户的订单。过度简化它，这就是我所拥有的：
Orders
- orderId
- siteID
Customers
- siteID
- Name
GetOrder(siteID, outCursor) /* returns all orders for a customer */
Now, I need to write another procedure that takes a customer name and does a LIKE query to get all custIds, then I need to reuse the GetOrder method to return all the orders for the custIds found, something like this:
现在，我需要编写另一个过程，该过程采用客户名称并执行 LIKE 查询以获取所有 custIds，然后我需要重用 GetOrder 方法来返回找到的 custIds 的所有订单，如下所示：
PROCEDURE GetOrderbyCustName(
p_name IN VARCHAR2,
curReturn OUT sys_refcursor
)
IS
siteid number;
BEGIN
FOR rec in SELECT site_id FROM customers WHERE name LIKE p_name
LOOP
-- This will replace curReturn in each iteration
-- how do I append instead?
GetOrder(rec.site_id,
curReturn
);
END LOOP;
END GetOrderbyCustName;
My question is, how do I append the return of GetOrder to curReturn in each iteration? As it's written right now it overwrites it in each cycle of the loop. Thanks!!
我的问题是，如何在每次迭代中将 GetOrder 的返回附加到 curReturn？正如它现在写的那样，它会在循环的每个循环中覆盖它。谢谢！！
回答by Tony Andrews
You can't do it like that - cursors cannot be appended or merged. Just do this instead:
你不能那样做——游标不能被附加或合并。只需这样做：
PROCEDURE GetOrderbyCustName(
p_name IN VARCHAR2,
curReturn OUT sys_refcursor
)
IS
BEGIN
OPEN curReturn FOR
SELECT o.orderID, o.siteID
FROM Orders o
JOIN Customers c ON c.siteID = o.siteID
WHERE c.name LIKE p_name;
END GetOrderbyCustName;
回答by Dave Costa
If the query is simple, I would say go with Tony's answer. This is not only simple but likely to perform better than executing one query for each siteID.
如果查询很简单，我会说选择托尼的答案。这不仅简单，而且可能比对每个站点 ID 执行一个查询执行得更好。
If it is fairly complex then it might be worth some extra effort to reuse the GetOrder procedure so you only have to maintain one query.
如果它相当复杂，那么重用 GetOrder 过程可能值得付出一些额外的努力，这样您只需维护一个查询。
To do this, you would need to actually fetch the data from the refcursor on each iteration of the loop, and put it into some other data structure.
为此，您需要在循环的每次迭代中从 refcursor 中实际获取数据，并将其放入其他一些数据结构中。
One option, if it makes sense for the interface, is to change GetOrderbyCustName to have a PL/SQL index-by table as its output parameter instead of a refcursor. Append to that table on each iteration through the loop.
如果接口有意义，一种选择是更改 GetOrderbyCustName 以将 PL/SQL 索引表作为其输出参数而不是引用。通过循环在每次迭代时附加到该表。
If you really need to return a refcursor, you can use a nested table type instead and then return a cursor querying that nested table. Something like this (not tested code):
如果你真的需要返回一个 refcursor，你可以使用嵌套表类型，然后返回一个查询该嵌套表的游标。像这样（未经测试的代码）：
CREATE TYPE number_table_type AS TABLE OF NUMBER;
PROCEDURE GetOrderbyCustName(
p_name IN VARCHAR2,
curReturn OUT sys_refcursor
)
IS
cursor_source_table number_table_type := number_table_type();
single_site_cursor sys_refcursor;
orderID NUMBER;
BEGIN
FOR rec in SELECT site_id FROM customers WHERE name LIKE p_name
LOOP
-- This will replace curReturn in each iteration
-- how do I append instead?
GetOrder(rec.site_id,
single_site_cursor
);
-- Fetch all rows from the refcursor and append them to the nested table in memory
LOOP
FETCH single_site_cursor INTO orderID;
EXIT WHEN single_site_cursor%NOTFOUND;
cursor_source_table.extend();
cursor_source_table( cursor_source_table.COUNT+1) := orderID;
END LOOP;
END LOOP;
OPEN curReturn FOR
SELECT * FROM TABLE( cursor_source_table );
END GetOrderbyCustName;
