When working with databases, one of the most important tasks is handling multiple rows of data. In Oracle PL/SQL, a powerful way to process multiple rows returned by a query is through cursors. Among the different cursor types, the cursor FOR loop is one of the simplest and most efficient constructs. It helps developers process query results row by row without writing too much boilerplate code. In this tutorial, you will learn what cursor FOR loops are, why they are useful, how to use them in different scenarios, and you’ll see working examples that you can test in your own Oracle environment.
What is a Cursor in PL/SQL?
A cursor is a pointer to a context area in memory where the database stores the results of an SQL query. When you run a SELECT statement in PL/SQL, the database engine does not immediately return all the rows—it returns a reference to a memory area. A cursor acts as a mechanism to retrieve and process these rows one by one.
There are two main types of cursors in PL/SQL:
- Implicit cursors: Automatically created by Oracle whenever you run a SELECT INTO, INSERT, UPDATE, or DELETE statement. You don’t need to define them explicitly.
- Explicit cursors: Manually defined by developers for queries that return more than one row. This gives you more control over fetching and processing rows.
A cursor FOR loop is a simplified way of working with explicit cursors. Instead of opening, fetching, and closing a cursor manually, PL/SQL can handle all of that automatically.
Why Use Cursor FOR Loops?
A cursor FOR loop is often the best choice when you need to iterate through query results row by row. It reduces coding effort and improves readability. Here are some key advantages:
- You don’t need to open, fetch, and close the cursor manually; PL/SQL handles it for you.
- It automatically declares a record variable that matches the cursor’s row structure.
- The loop ends automatically when all rows have been processed, so there is no need to check for %NOTFOUND
- It helps avoid common mistakes like forgetting to close a cursor or fetch the next row.
If you are just starting with PL/SQL, using cursor FOR loops is the simplest and safest way to process multiple rows from a query.
Syntax of Cursor FOR Loop
The general syntax is straightforward:
FOR record_variable IN cursor_name LOOP
-- Your processing logic goes here
END LOOP;Alternatively, you can use a cursor FOR loop with a SELECT statement directly without declaring the cursor first:
FOR record_variable IN (SELECT column1, column2 FROM table_name WHERE condition) LOOP
-- Process each row
END LOOP;PL/SQL automatically handles:
- Opening the cursor
- Fetching rows into the record variable
- Closing the cursor when the loop ends
This makes the code much shorter and less error-prone compared to manual cursors.
Example 1: Simple Cursor FOR Loop
Let’s start with a basic example. Suppose you have an employees table:
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
salary NUMBER
);
INSERT INTO employees VALUES (1, 'Alice Johnson', 5000);
INSERT INTO employees VALUES (2, 'Bob Smith', 6000);
INSERT INTO employees VALUES (3, 'Carla Diaz', 7000);
COMMIT;Now you want to print all employee names and salaries. Instead of fetching rows manually, you can use a cursor FOR loop:
DECLARE
CURSOR emp_cursor IS
SELECT emp_id, emp_name, salary
FROM employees;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.emp_id ||
' | Name: ' || emp_record.emp_name ||
' | Salary: ' || emp_record.salary);
END LOOP;
END;
/How this works:
- The cursor emp_cursor is declared to select all employees.
- FOR emp_record IN emp_cursor automatically fetches each row into emp_record.
- Inside the loop, you can directly reference the columns using column_name.
- No explicit OPEN, FETCH, or CLOSE is needed—PL/SQL does it internally.
When you run this, you’ll see output like:
ID: 1 | Name: Alice Johnson | Salary: 5000
ID: 2 | Name: Bob Smith | Salary: 6000
ID: 3 | Name: Carla Diaz | Salary: 7000
Example 2: Cursor FOR Loop with Inline SELECT
Sometimes you don’t need to declare a cursor separately. You can place the SELECT statement directly inside the FOR loop.
BEGIN
FOR emp_record IN (
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 5500
) LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.emp_name || ' earns ' || emp_record.salary);
END LOOP;
END;
/Here, Oracle automatically creates and manages the cursor behind the scenes. This is useful for quick iterations when you don’t need to reuse the cursor.
Output:
Bob Smith earns 6000
Carla Diaz earns 7000
Example 3: Using Parameters in Cursor FOR Loop
You can make cursors more dynamic by adding parameters. This allows you to pass conditions at runtime.
DECLARE
CURSOR emp_cursor(p_min_salary NUMBER) IS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary >= p_min_salary;
BEGIN
FOR emp_record IN emp_cursor(6000) LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.emp_name ||
' | Salary: ' || emp_record.salary);
END LOOP;
END;
/In this example:
- The cursor takes a parameter p_min_salary.
- When you call emp_cursor(6000), it retrieves only employees with salary greater than or equal to 6000.
Example 4: Nested Cursor FOR Loops
You can also use nested cursor loops when you want to process related data from two or more tables. For example, suppose you have another table departments:
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100)
);
INSERT INTO departments VALUES (10, 'HR');
INSERT INTO departments VALUES (20, 'IT');
INSERT INTO departments VALUES (30, 'Finance');
UPDATE employees SET emp_id = 1, salary = 5000 WHERE emp_name = 'Alice Johnson';
UPDATE employees SET emp_id = 2, salary = 6000 WHERE emp_name = 'Bob Smith';
UPDATE employees SET emp_id = 3, salary = 7000 WHERE emp_name = 'Carla Diaz';
-- Assign departments
ALTER TABLE employees ADD (dept_id NUMBER);
UPDATE employees SET dept_id = 10 WHERE emp_id = 1;
UPDATE employees SET dept_id = 20 WHERE emp_id = 2;
UPDATE employees SET dept_id = 30 WHERE emp_id = 3;
COMMIT;Now you can create a nested cursor loop to display employees grouped by department:
DECLARE
CURSOR dept_cursor IS
SELECT dept_id, dept_name FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT emp_name, salary
FROM employees
WHERE dept_id = p_dept_id;
BEGIN
FOR dept_rec IN dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.dept_name);
FOR emp_rec IN emp_cursor(dept_rec.dept_id) LOOP
DBMS_OUTPUT.PUT_LINE(' - ' || emp_rec.emp_name || ' earns ' || emp_rec.salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE('----------------------');
END LOOP;
END;
/Expected output:
Department: HR
- Alice Johnson earns 5000
----------------------
Department: IT
- Bob Smith earns 6000
----------------------
Department: Finance
- Carla Diaz earns 7000
----------------------
This example shows how cursor FOR loops can be nested to handle hierarchical data structures, such as departments and employees.
Example 5: Updating Data with a Cursor FOR Loop
Cursors can also be used to update records one by one. Let’s say you want to give a 10% salary increase to all employees earning less than 6000.
DECLARE
CURSOR low_salary_cursor IS
SELECT emp_id, salary
FROM employees
WHERE salary < 6000;
BEGIN
FOR emp_rec IN low_salary_cursor LOOP
UPDATE employees
SET salary = emp_rec.salary * 1.10
WHERE emp_id = emp_rec.emp_id;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Salaries updated successfully.');
END;
/After running this, Alice Johnson’s salary will increase from 5000 to 5500.
Key Points to Remember
- Cursor FOR loops are easier to use than explicit cursors, since Oracle manages OPEN, FETCH, and CLOSE.
- Always use cursor FOR loops when you want to process all rows from a query.
- Use parameterized cursors when you need dynamic filtering.
- For complex hierarchies, nested cursor loops are useful.
- You can use cursor FOR loops for SELECT, UPDATE, or DELETE operations depending on your needs.
- While convenient, cursor loops are not always the most efficient; for large datasets, consider using bulk collect and FORALL for better performance.
Best Practices for Cursor FOR Loops
To use cursor FOR loops effectively:
- Always use descriptive cursor names so the purpose of the loop is clear.
- Keep business logic inside the loop simple, and avoid heavy computation inside each iteration.
- Prefer SQL set-based operations if possible, since they are usually faster than row-by-row processing.
- Use LIMIT and bulk operations when dealing with millions of rows to prevent performance bottlenecks.
- Remember that a cursor FOR loop is read-only by default. To update or delete rows, you need an explicit cursor with FOR UPDATE and then perform UPDATE ... WHERE CURRENT OF.
Conclusion
Cursor FOR loops in PL/SQL provide a clean and efficient way to process query results without manually managing cursors. They simplify coding, reduce errors, and make your PL/SQL programs easier to read and maintain. While they are not always the fastest option for very large datasets, they are an excellent choice for most everyday database tasks such as fetching rows, displaying results, or performing row-by-row operations. By mastering cursor FOR loops, you gain a strong foundation for handling data programmatically within Oracle databases.
