7

How can I loop through an implicit cursor which is created, for example, from a query?

Here is the sample code:

SERVEROUTPUT on;

DECLARE      
  TYPE ref_cursor IS REF CURSOR;
  cur REF_CURSOR;

BEGIN
  OPEN cur FOR 'SELECT i.item_no, 
                       i.item_descr 
                  FROM ITEMS i 
                 WHERE i.item_no in (1,2,3)';

  ... loop statement to print all item descriptions?

END;
rajeemcariazo
  • 2,476
  • 5
  • 36
  • 62

2 Answers2

10

Here's how to do it allowing for dynamic SQL. You can build the query string up in code however needed (usual warnings about SQL injection apply).

DECLARE      
  TYPE ref_cursor IS REF CURSOR;
  cur REF_CURSOR;

  d_item_no  items.item_no%TYPE;
  d_item_descr  items.item_descr%TYPE;

BEGIN
  OPEN cur FOR 'SELECT i.item_no, 
                       i.item_descr 
                  FROM ITEMS i 
                 WHERE i.item_no in (1,2,3)';
  LOOP
    FETCH cur INTO d_item_no, d_item_descr;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line( d_item_no||' '||d_item_descr );
  END LOOP;

  CLOSE cur;
END;
/
Dave Costa
  • 47,262
  • 8
  • 56
  • 72
8

I'm not up on the 11g changes, but this should work:

BEGIN

  FOR cur IN (SELECT i.item_no, 
                     i.item_descr 
                FROM ITEMS i 
               WHERE i.item_no in (1,2,3))
  LOOP
    DBMS_OUTPUT.PUT_LINE('Row: '|| cur.item_no ||' '|| cur.item_descr);
  END LOOP;

END;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • How if the item numbers in the "IN" clause will come from a string value from a variable? For example: 'select i.tem_no, i.item_descr from items i where i.item_no in (' || p_items_comma_separated || ')' – rajeemcariazo May 04 '11 at 04:30
  • and suppose i'm not particular with the table as long as my column aliases are item_no and item_descr – rajeemcariazo May 04 '11 at 04:32
  • @rajeem_cariazo: The `IN` clause does not support using a variable in place of a comma separated list - in any SQL, you need to use dynamic SQL to construct the query as a string before it is executed. Likewise for the table name in any statement. The alternative is to build a derived table containing the values you want, so you can join against it rather than using an `IN` clause. – OMG Ponies May 04 '11 at 04:46