A cursor for loop is an extension of the for loop in PL/SQL and enables you to execute a block of code for each row in a resulting set. This is very useful as it allows you to quickly and efficiently perform extensive calculations in a single computation without loading the data into the memory.
Oracle Cursor For Loop
The for loop fetches a row from the resulting set into the loop index for each iteration. If there’s no row, the for loop closes the cursor.
The following shows the basic syntax of the cursor for loop in Oracle:
LOOP
-- apply this block to each row
END LOOP;
In the provided syntax, the record represents the variable name which prepresents each row in the cursor.
The cursor_name in the loop specifies an explicit cursor that you wish to loop through. Note that a cursor must be open and fetched into the record before using it in a cursor for loop.
If you do not wish to create an explicit cursor, the FOR LOOP implicitly creates a loop index as a record variable of %ROWTYPE when you skip the cursor_name.
Oracle Cursor For Loop Example
Let us take a look at some basic examples of the cursor FOR LOOP in Oracle databases.
cursor emp_cursor is
select first_name, last_name
from employees;
begin
for employee in emp_cursor
loop
DBMS_OUTPUT.PUT_LINE(employee.first_name || ' ' || employee.last_name);
end loop;
end;
In the provided code, we declare a cursor named emp_cursor. We then populate this cursor with the data from the employees’ table using the first_name and last_name columns.
Next, we use a cursor for loop to fetch and process the data in each row from the result set. Note that the employee variable stores each row in the emp_cursor.
The loop concatenates the first_name and last_name columns and prints the result using the DBMS_OUTPUT.PUT_LINE function.
The database engine automatically closes the cursor once the loop terminates.
This should return the following results, demonstrating how we can use an explicit cursor definition to process data from a table:
Oracle Implicit Cursor
If you do not wish to create an explicit cursor, you can allow the database engine to create one and manage it for you by omitting the cursor name as shown in the following:
FOR employee IN (SELECT first_name, last_name
FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(employee.first_name || ' ' || employee.last_name);
END LOOP;
END;
This should return a similar result as an explicit cursor.
Conclusion
In this post, you came across the cursor for loop in Oracle databases to fetch and process each row from a result set.
