Oracle PL/SQL for loop

A For Loop is a control structure in PL/SQL that repeats a block of code a specific number of times. It is efficient for situations where you know the exact number of iterations.

In simple terms, a For Loop in PL SQL is like telling the computer to perform a task repeatedly, starting from one point and stopping at another. It is often used when you need to go through a list of items, count numbers, or repeat actions a fixed number of times.

You can use this loop when you need to execute a set of statements multiple times. This loop is simple and efficient.

Why Use a For Loop?

• Efficiency: Saves time and lines of code compared to writing repetitive statements manually.
• Control: You can specify the starting and ending points of the loop.
• Automatic Increment/Decrement: The loop counter changes automatically, which reduces errors.
• Flexibility: You can loop through numbers, arrays, or records from a database table.

Syntax of For Loop in Oracle PL/SQL

FOR counter IN start_number .. final_number 
LOOP
   sequence_of_statements;
END LOOP;

Syntax Analysis

1. FOR: This keyword initiates the loop.
2. counter: A loop variable that changes in each iteration.
3. IN: Defines the range for the loop.
4. start_number .. final_number: Specifies the start and end values for the loop counter.
5. LOOP: Marks the beginning of the loop body.
6. sequence_of_statements: The code block that runs repeatedly.
7. END LOOP: Marks the end of the for loop.

How For Loop Works?

• The loop starts by setting the counter to the initial value.
• It checks if the counter is within the given range.
• If true, the loop body runs.
• After each iteration, the counter increases automatically.
• When the counter exceeds the final value, the loop stops.

Key Features of For Look in PL/SQL

• The loop counter can be a number, a variable, or an expression.
• The starting value does not have to be 1.
• The counter always increases or decreases by 1.
• The loop range can be determined dynamically at runtime.
• The loop counter behaves like a constant within the loop. You cannot change its value manually.
• Any attempt to modify the loop counter will result in a compilation error.
• If the values are not numbers, PL/SQL raises a VALUE_ERROR.

Example: Displaying 10 Countries from an Array

The following example prints the names of 10 countries stored in an array.

DECLARE
   TYPE country_array IS VARRAY(10) OF VARCHAR2(50);
   countries country_array := 
        country_array('India', 'USA', 'UK', 'Canada', 'Australia', 
                     'Germany', 'France', 'Japan', 'China', 'Brazil');
   i NUMBER;
BEGIN
   FOR i IN 1 .. 10 LOOP
      dbms_output.put_line('Country: ' || countries(i));
   END LOOP;
END;

Country: India
Country: USA
Country: UK
Country: Canada
Country: Australia
Country: Germany
Country: France
Country: Japan
Country: China
Country: Brazil

For Loop in Cursors

For Loop work seamlessly with Oracle explicit cursors, reducing the need to fetch data manually. This combination is useful for processing database records efficiently.

Example of Cursor For Loop

DECLARE
   CURSOR emp_cursor 
   IS 
   SELECT *
     FROM employees;
BEGIN
   FOR emp_rec IN emp_cursor 
   LOOP
      dbms_output.put_line('Employee Name: ' || emp_rec.employee_name);
      dbms_output.put_line('Manager Name: ' || emp_rec.manager_name);
      dbms_output.put_line('Salary: ' || emp_rec.salary);
   END LOOP;
END;

Reverse For Loop

Sometimes, you need to run the loop in reverse order. Use the REVERSE keyword to achieve this. The counter decreases instead of increasing.

Syntax for Reverse For Loop

FOR counter IN REVERSE start_number .. final_number LOOP
   sequence_of_statements;
END LOOP;

Example: Reverse FOR Loop to display 10 Countries

DECLARE
   TYPE country_array IS VARRAY(10) OF VARCHAR2(50);
   countries country_array := 
            country_array('India', 'USA', 'UK', 'Canada', 'Australia', 
                          'Germany', 'France', 'Japan', 'China', 'Brazil'
                          );
   i NUMBER;
BEGIN
   FOR i IN REVERSE 1 .. 10 
   LOOP
      dbms_output.put_line('Country: ' || countries(i));
   END LOOP;
END;
/

Country: Brazil
Country: China
Country: Japan
Country: France
Country: Germany
Country: Australia
Country: Canada
Country: UK
Country: USA
Country: India

Conclusion

• For Loop is best for fixed iterations.
• Reverse For Loop helps in decreasing sequences.
• You can use it for real-world tasks like displaying lists from an array.

PL/SQL For Loop makes coding easier and more efficient! You might want to know about performance tuning of PL/SQL package. You can visit the article to know more on this.