Oracle - Loop through a Cursor Variable in Oracle

Once a cursor variable is opened with a query statement, it will have the same attributes as a normal cursor and it can be used in the same way a normal cursor too. The following sample script shows you how to loop through a cursor variable:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  emp_cur emp_ref;
  emp_rec employees%ROWTYPE;
BEGIN
  OPEN emp_cur FOR SELECT * FROM employees
    WHERE manager_id = 101;
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Name = ' || 
      emp_rec.first_name || ' ' || emp_rec.last_name); 
  END LOOP; 
  CLOSE emp_cur;
END;
/
Name = Nancy Greenberg
Name = Jennifer Whalen
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins

⇒Working with Cursors in Oracle PL/SQL

⇒⇒Oracle Database Tutorials

2016-10-16, 178👍, 0💬