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.
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;
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;