CASE Statement

Control Statements

WHILE Statement

LOOP Statement

statement_LOOP ::= [ label : ] LOOP statement  END LOOP [ label ];

The LOOP statement executes statements (or block statements) repeatedly, until the LEAVE statement is called or an error occurs.

If an end label is set, the same begin label must also be set. Statements inside the LOOP may not be marked with the same label.

The LOOP statement is generally used when passing through a cursor opened by the OPEN statement. You can also use the FOR statement to pass through all cursor records.

Example 1:

Insert 10 records into a table and number them ascending.

BEGIN ATOMIC 
  DECLARE i INT;      
  SET i = 1; 
  label: LOOP INSERT INTO Tab3 (number) VALUES (i); 
     IF i >= 10 THEN LEAVE label; END IF; 
     SET i = i + 1; 
  END LOOP label; 
END;

Example 2:

Using a LOOP to iterate through a cursor.

PROCEDURE PriceChange();
BEGIN 
  DECLARE err_notfound BIT DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND 
    BEGIN SET err_notfound=TRUE;  END;
  DECLARE curvalue SENSITIVE CURSOR FOR 
    SELECT item_number, price
    FROM ItemList
  FOR UPDATE; 
  DECLARE Item CHAR(20); 
  DECLARE price NUMERIC(14,2); 

  OPEN curvalue; 
  LabelLoop: LOOP 
     FETCH NEXT FROM curvalue INTO Item, price; 
     IF err_notfound IS TRUE THEN LEAVE LabelLoop; END IF; 
     CASE SUBSTRING(Item FROM 1 FOR 1)
       WHEN "X" THEN 
         UPDATE SET price = price*0.9 WHERE CURRENT OF curvalue;
       WHEN "A" THEN 
         UPDATE SET price = price*2 WHERE CURRENT OF curvalue;
       ELSE
         DELETE WHERE CURRENT OF curvalue;
     END CASE; 
  END LOOP LabelLoop; 
  CLOSE curvalue; 
END;

CASE Statement

Control Statements

WHILE Statement