PL/SQL Cursors

There are two types of cursors:

  • Implicit cursors are generated automatically by Oracle server when an SQL statement occurs in the PL/SQL executable part;
  • Explicit cursors are declared and defined by the user when an query which appears in a PL/SQL return multiple lines as a result.

Both implicit and explicit cursors have attributes. The attributes are:

  • %ROWCOUNT is an integer data type and represents the number of lines the cursor loaded.
  • %FOUND is an boolean and has the value TRUE if the last load operation FETCH from a cursor successful (for explicit cursors) or SQL statement returned at least one line (for implicit cursors).
  • %NOTFOUND is an boolean and has the opposite meaning of the attribute %FOUND.
  • %ISOPEN for implicit cursors this attribute is always FALSE because a implicit cursor is closed immediately after execution.

Implicit cursors

DECLARE 
   updated_rows number(2);
BEGIN
   UPDATE I_EMPLOYEES SET salary = salary + 1000 WHERE EMPLOYEE_ID=3;
   IF SQL%NOTFOUND THEN
      dbms_output.put_line('No employee found');
   ELSIF SQL%FOUND THEN
      updated_rows := SQL%ROWCOUNT;
      dbms_output.put_line('Updated rows = ' || updated_rows);
   END IF; 
END;

Explicit cursors

DECLARE
  CURSOR c_emp IS SELECT * FROM i_employees;
  temp I_EMPLOYEES%rowtype;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp into temp;
    dbms_output.put_line(temp.employee_id||' '||temp.first_name||' '||temp.last_name);      
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  CLOSE c_emp;
END;