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;
Cursor For Loop With Simple Explicit Cursor In Oracle Database As the name suggests Cursor For Loop is a type of For loop provided by oracle PL/SQL which makes working with cursors in oracle database a lot easier by executing OPEN, FETCH & CLOSE Cursor statements implicitly in the background for you.
The cursor_name is the name of an explicit cursor that is not opened when the loop starts. Note that besides the cursor name, you can use a SELECT statement as shown below: In this case, the cursor FOR LOOP declares, opens, fetches from, and closes an implicit cursor.
An explicit cursor is a named pointer to a private SQL area that stores information for processing a specific query or DML statement—typically, one that returns or affects multiple rows. You can use an explicit cursor to retrieve the rows of a result set one at a time. Before using an explicit cursor, you must declare and define it.
SQL SELECT statement (not PL/SQL SELECT INTO statement). For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal—you cannot reference it with the name SQL.
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;
/
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With