Oracle FOR Loop: Usage Guide & Examples

1 year ago
Sophia Anderson
2 minutes

In Oracle, there are two ways to perform loops.

  1. Using a FOR loop: A FOR loop is a type of loop statement that controls the number of times a loop is executed by specifying the initial value, ending value, and step size of a loop variable. The syntax is as follows:
  2. FOR loop_variable IN start_value..end_value LOOP
    — Code to be executed
    END LOOP;
  3. For example, the following example demonstrates printing numbers from 1 to 10 using a FOR loop.
  4. DECLARE
    i INTEGER;
    BEGIN
    FOR i IN 1 TO 10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
    END;
  5. Within the loop, you can execute any SQL statements, PL/SQL code, or call stored procedures.
  6. Using a WHILE loop: A WHILE loop is a type of conditional loop statement that will continue to execute as long as the specified condition is true. The syntax is as follows:
  7. LOOP WHILE condition
    — Code to be executed
    END LOOP;
  8. For example, the following example demonstrates how to use a WHILE loop to print numbers from 1 to 10.
  9. Declare a variable “i” equal to 1. Loop through and display numbers 1 through 10 using a DBMS output. Increment the value of “i” by 1 in each iteration.
  10. Within the loop body, you can execute any SQL statements, PL/SQL code, or call stored procedures.

Whether using a FOR loop or a WHILE loop, it is important to ensure that the code within the loop is able to move the loop variable towards the end value, otherwise it may result in an infinite loop.

bannerAds