SQL Reference - ITERATE

ITERATE

ITERATE is a statement in SQL, generally used in structured Dynamic SQL. It is typically used in loops and is comparable to the continue statement in programming languages such as Java or C++. When executed, the ITERATE statement skips the remaining queries in the current iteration of the loop and moves control to the next iteration of the loop.

Example

DELIMITER //
CREATE PROCEDURE iterate_example()
BEGIN
  SET @x = 0;

  LOOP
    SET @x = @x + 1;
    SELECT @x;

    IF @x >= 10 THEN
      LEAVE LOOP;
    END IF;
  END LOOP;
END //
DELIMITER ;
CALL iterate_example();

Output

Explanation

This is a simple example of the ITERATE statement in MySQL. The code initializes a variable @x to 0 and creates a loop that increments @x by one and prints it out. When @x reaches 10, it exits the loop. The output displays the values of @x as they are updated, from 1 to 10.

Example

DECLARE @Count INT;
DECLARE @Sum INT;

SET @Count = 1;
SET @Sum = 0;

WHILE @Count <= 10
BEGIN
    SET @Sum = @Sum + @Count;

    IF @Count = 5
    BEGIN
        SET @Count = @Count + 1;
        CONTINUE;
    END
    SET @Count = @Count + 1;
END

PRINT @Sum;

Output

Explanation

In this example, a loop is created using a WHILE statement, which adds the value of @Count variable to the @Sum variable. When the value of @Count reaches 5, the CONTINUE statement is triggered, exiting the current cycle and skipping to the next iteration of the loop. This specific scenario is similar to an ITERATE statement in other SQL systems. Since SQL Server doesn’t support ITERATE keyword, CONTINUE is used to simulate its functionality. The output ‘50’ is the total sum of numbers from 1 to 10.

Example

DECLARE
  counter INTEGER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
    counter := counter + 1;
    IF counter > 3 THEN
      EXIT;
    END IF;
  END LOOP;
END;
/

Output

Counter: 1
Counter: 2
Counter: 3

Explanation

This PL/SQL block initializes a looping structure using the LOOP..END LOOP syntax in Oracle. The counter variable is initialized to 1. Inside the loop, the counter value is outputted then increased by 1. Once the counter is greater than 3, the LOOP is exited.