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;