Some Oracle DB cursors examples

Steven Feuerstein had an excellent article on Working with Cursors on the March 2013 Oracle Magazine. It is a good article for newbies or those who somehow never got to work with this aspect of the DB.

For me, it has been a while since I've delved in Oracle development (mostly non-db web related technologies nowadays), and as I've had my share of returning into Oracle in the past (and can assume with confidence that will also have similar experiences in the future), I think it would be wise to keep around some examples (quoted from that article), to make those head-scratching, 'now, how does this syntax go' moments less annoying...

Simple Fetch:

DECLARE 

   l_employee employees%ROWTYPE; 

BEGIN 

   SELECT * 

   INTO l_employee 

   FROM employees 

   WHERE employee_id = 138; 

   DBMS_OUTPUT.put_line ( l_employee.last_name); 

END;

Working with an explicit Cursor definition:

DECLARE
  CURSOR c1 IS SELECT department_id FROM departments;
  deptid departments.department_id%TYPE;
BEGIN
  OPEN c1;
  FETCH c1 INTO deptid;
  CLOSE c1;
  /* if handled more than one, could put the fetch in loop:
  OPEN c1;
  LOOP
     FETCH x into l_variable; EXIT WHEN c1%NOTFOUND;
     -- do something with l_variable;
  END LOOP;
  CLOSE c1;
  -- don't forget to handle other EXCEPTIONS
  -- see more basic examples
  */ 

 END;

Working with the For Loop and an implicit Cursor definition :

(my personal favorite)

DECLARE
  NULL;
BEGIN
   FOR employee_rec IN (
      SELECT *
      FROM employees
      WHERE department_id = 10)
   LOOP
       DBMS_OUTPUT.put_line (
       employee_rec.last_name); 

   END LOOP;
END;

The Article has much more to offer, and it is a part of an excellent series on PL/SQL that Oracle Magazine is running. Worth the time for those wishing to get acquainted, refresh their memory, or get deeper...