FOR statement (Repeat a statement on rows)
Function
Repeats the execution of a given SQL statement with respect to rows in a table.
Format
[starting-label:] |
Operands
Common rules
Notes
Examples
Of the data listed in table T1, assign the data with a column C1(INT type) less than or equal to 100 to Table T2, and for other data, define a procedure (PROC1) that assigns the data to Table T3:
CREATE PROCEDURE PROC1 ()
FLBL :
FOR LVN AS SELECT C1,C2,C3 FROM T1 DO
IF C1 <= 100 THEN
INSERT INTO T2 VALUES(LVN.C1,LVN.C2,LVN.C3) ;
ELSE
INSERT INTO T3 VALUES(LVN.C1,LVN.C2,LVN.C3) ;
END IF ;
END FOR FLBL
SQL statements equivalent to the FOR statements shown in the above example can be implemented in the following SQL statements:
CREATE PROCEDURE PROC1 ()
LVN :
BEGIN
DECLARE C1,C2,C3 INT ;
DECLARE FCN CURSOR FOR SELECT C1,C2,C3 FROM T1 ;
DECLARE AT_END CHAR(1) DEFAULT 'N' ;
OPEN FCN ;
FLBL :
WHILE AT_END != 'N' DO
FETCH FCN INTO C1,C2,C3 ;
IF SQLCODE = 100 THEN
SET AT_END = 'Y' ;
ELSE
IF C1 <= 100 THEN
INSERT INTO T2 VALUES(LVN.C1,LVN.C2,LVN.C3) ;
ELSE
INSERT INTO T3 VALUES(LVN.C1,LVN.C2,LVN.C3) ;
END IF ;
END IF ;
END WHILE FLBL ;
CLOSE FCN ;
END LVN