Download PL/SQL User's Guide and Reference

Cursor Attributes
BEGIN
OPEN emp_cur;
LOOP
-- loop through the table and get each employee
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line('Employee #' || emp_rec.employee_id ||
' is ' || emp_rec.last_name);
END LOOP;
CLOSE emp_cur;
END;
/
The following example uses %ISOPEN to make a decision:
IF NOT (emp_cur%ISOPEN) THEN
OPEN emp_cur;
END IF;
FETCH emp_cur INTO emp_rec;
The following PL/SQL block uses %ROWCOUNT to fetch the names and salaries of the
five highest-paid employees:
DECLARE
CURSOR c1 is
SELECT last_name, employee_id, salary FROM employees
ORDER BY salary DESC;
-- start with highest-paid employee
my_name employees.last_name%TYPE;
my_empno employees.employee_id%TYPE;
my_sal
employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_name, my_empno, my_sal;
EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
dbms_output.put_line('Employee ' || my_name || ' (' || my_empno || ') makes
' || my_sal);
END LOOP;
CLOSE c1;
END;
/
The following example raises an exception if many rows are deleted:
DELETE FROM accts WHERE status = 'BAD DEBT';
IF SQL%ROWCOUNT > 10 THEN
RAISE out_of_bounds;
END IF;
Related Topics
Cursors, Cursor Variables
PL/SQL Language Elements
13-33