You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
A cursor is a pointer to the result set of a SQL query. Oracle uses cursors internally for every SQL statement; PL/SQL lets you work with them explicitly when you need row-by-row processing.
Every DML statement (INSERT, UPDATE, DELETE) and single-row SELECT automatically creates an implicit cursor. You access it through the SQL cursor attributes:
| Attribute | Meaning |
|---|---|
SQL%ROWCOUNT | Number of rows affected by the last DML |
SQL%FOUND | TRUE if the last statement affected at least one row |
SQL%NOTFOUND | TRUE if the last statement affected no rows |
SQL%ISOPEN | Always FALSE for implicit cursors (auto-closed) |
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
END;
/
For queries that return exactly one row, use SELECT INTO to populate a variable:
DECLARE
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name FROM employees WHERE id = 1;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
NO_DATA_FOUND if zero rows are returned.TOO_MANY_ROWS if more than one row is returned.Use explicit cursors when a query returns multiple rows and you need to process them one at a time.
DECLARE
CURSOR c_employees IS
SELECT first_name, salary FROM employees WHERE department_id = 10;
v_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees INTO v_name, v_salary;
EXIT WHEN c_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
END LOOP;
CLOSE c_employees;
END;
/
Always close cursors when done — open cursors consume memory and Oracle has a system limit.
The cursor FOR loop is simpler — Oracle handles OPEN, FETCH, and CLOSE automatically:
DECLARE
CURSOR c_emp IS SELECT first_name, salary FROM employees;
BEGIN
FOR rec IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
END LOOP;
END;
/
Or inline without a named cursor:
BEGIN
FOR rec IN (SELECT first_name, salary FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
END LOOP;
END;
/
Cursors can accept parameters, making them reusable:
DECLARE
CURSOR c_dept(p_dept_id NUMBER) IS
SELECT first_name FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR rec IN c_dept(10) LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name);
END LOOP;
END;
/
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.