Oracle PL/SQL for DBAs

Cursor Expressions

Cursor expressions are essentially nested cursors . When I refer to a “cursor expression,” I am not talking about nested subqueries that determine a result set; instead, I am talking about nested queries that return nested result sets. Let me explain with an example.

    SELECT order_number,
           CURSOR ( SELECT order_line_amt
                      FROM order_lines ol
                     WHERE ol.order_number = orders.order_number )
      FROM orders;

This query returns a list of orders plus a cursor to find the lines of that order later. Here’s how it might be used in a PL/SQL procedure:

 /* File on web: nested_cursor.sql */ CREATE OR REPLACE PROCEDURE nested AS -- cursor to get orders plus a nested cursor -- to its line amounts CURSOR curs_orders IS SELECT order_number, CURSOR ( SELECT order_line_amt FROM order_lines ol WHERE ol.order_number = orders.order_number ) FROM orders; lines_curs SYS_REFCURSOR; -- for order lines v_order_id NUMBER; -- local variables for bulk fetch of lines TYPE v_number_t IS TABLE OF NUMBER; v_line_amt v_number_t; BEGIN OPEN curs_orders; FETCH curs_orders INTO v_order_id, lines_curs; -- for every order... LOOP EXIT WHEN curs_orders%NOTFOUND; -- only process even numbered orders IF MOD(v_order_id,2) = 0 THEN -- get all lines for the order at once FETCH lines_curs BULK COLLECT INTO v_line_amt; -- loop through the order lines IF NVL(v_line_amt.COUNT,0) > 0 THEN FOR counter IN v_line_amt.FIRST..v_line_amt.LAST LOOP process_lines; END LOOP; END IF; END IF; -- only even numbered orders FETCH curs_orders ...