Oracle Cursors

Oracle Cursors

The Oracle database contains two types of cursors:

Implicit cursors are automatically constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement.

The implicit cursor attributes are:

  • SQL%ISOPEN
  • SQL%FOUND
  • SQL%NOTFOUND
  • SQL%ROWCOUNT
  • SQL%BULK_ROWCOUNT
  • SQL%BULK_EXCEPTIONS

Explicit cursors are created by the developer user. The explicit cursor consists of the following parts:

  • Declare a Cursor – declare and define the explicit cursor
  • Open cursor – open cursor with the OPEN statement
  • FETCH statement – fetch rows from the result set
  • CLOSE statement – close the explicit cursor

Implicit cursor example

DECLARE
   v_implicit_cursor number;
BEGIN
   UPDATE customers 
   SET address = 'Address 2Bcd' 
   WHERE id=2;
   
   IF SQL%NOTFOUND THEN
      dbms_output.put_line('No customer found');
   ELSIF SQL%FOUND THEN
      v_implicit_cursor := SQL%ROWCOUNT;
      dbms_output.put_line('Rows = ' || v_implicit_cursor);
   END IF; 
END;

Explicit cursor example

DECLARE
	CURSOR c_cursor IS 
		SELECT name, address 
		FROM customers
		WHERE id in (3,4);
	v_name customers.name%type;
	v_address customers.address%type;
BEGIN
	OPEN c_cursor;
	LOOP
		FETCH c_cursor into v_name, v_address;
		dbms_output.put_line(v_name||' / '||v_address);      
		EXIT WHEN c_cursor%NOTFOUND;
	END LOOP;
	CLOSE c_cursor;
END;