Cursor

A cursor is a pointer used to fetch rows from a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in this structure is a pointer to the next record to be fetched from the query results.

Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into the ORA-01000: maximum number of open cursors exceeded error.

PL/SQL examples

PL/SQL example opening a cursor and fetching data from it in a loop:

DECLARE
  CURSOR c1 IS 
         SELECT table_name FROM all_tables;
  v_table_name all_tables.table_name%TYPE;
  v_count      INTEGER := 1;
BEGIN
  OPEN c1;
  LOOP
     FETCH c1 INTO v_table_name;
     IF c1%notfound OR v_count > 2000 THEN
       EXIT;
     END IF;
     v_count := v_count + 1;
  END LOOP;

  -- Does cursor need to be closed
  IF c1%ISOPEN THEN  -- cursor is open
     CLOSE c1;
  END IF;

  dbms_output.put_line('Rows processed: '||v_count);
END;
/

Implement a parameterized cursor:

DECLARE
  CURSOR MyCur(p_sal emp.sal%TYPE) IS 
         SELECT * FROM emp WHERE  sal > p_sal;
BEGIN
  FOR MyRow IN MyCur(123) LOOP
    dbms_Output.Put_Line(MyRow.eName ||' ' ||MyRow.sal);
  END LOOP;
END;
/

Cursor with UPDATE OF and CURRENT OF:

DECLARE
  CURSOR abc IS 
         SELECT a FROM my_seq FOR UPDATE OF a;
  Myvar  NUMBER;
BEGIN
  Myvar := 1;
  FOR MyRow IN abc LOOP
    UPDATE my_seq SET a = Myvar WHERE CURRENT OF abc;
    Myvar := Myvar + 1;
  END LOOP;
END;
/

Also see

• ORA-01000 - Maximum open cursors exceeded
• ORA-01001 - Invalid cursor

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#