Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page