Oracle Cursor For Loop

A cursor for loop is a type of for loop that allows you to fetch and process the rows from a database cursor without worrying about manually handling the execution cycle.

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:

FOR record IN cursor_name
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.

declare
    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:

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

from https://ift.tt/yjqCMH6