代码之家 › 专栏 › 技术社区 › Ricardo Villamil

返回Oracle ref游标并附加多个结果

sys-refcursor oracle

Ricardo Villamil · 技术社区 · 17 年前

我有这个问题,我希望有人知道答案。我有一个oracle存储过程,它接受一个客户id,并在ref_游标中返回客户的所有订单。过于简单化,这就是我所拥有的:

Orders
- orderId
- siteID

Customers
- siteID
- Name

GetOrder(siteID, outCursor) /* returns all orders for a customer */

现在,我需要编写另一个过程,该过程采用客户名称并执行类似的查询以获取所有CUSTID,然后我需要重用GetOrder方法来返回找到的CUSTID的所有订单,如下所示:

   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;

我的问题是,如何在每次迭代中将GetOrder的返回附加到curReturn?正如它现在写的那样,它在循环的每个周期中都会覆盖它。谢谢

2 回复 | 直到 17 年前

Tony Andrews 17 年前

您不能这样做-不能追加或合并游标。只需这样做:

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;

Dave Costa 17 年前

如果问题很简单,我会说同意托尼的答案。这不仅简单,而且可能比为每个siteID执行一个查询执行得更好。

为此,您需要在循环的每次迭代中从refcursor实际获取数据,并将其放入其他数据结构中。

如果对接口有意义的话,一个选项是将GetOrderbyCustName更改为将PL/SQL索引表作为其输出参数,而不是refcursor。在循环的每次迭代中追加到该表。

如果确实需要返回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;