ITERATE

MySQL

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

1
2
3
4
5
6
7
8
9
10

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.

SQL Server

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

50

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.

Oracle

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.