Allows the repetitive execution of one or more SQL statements in
a compound statement.
See also the FOR, REPEAT and WHILE statements.
1 – Environment
You can use the LOOP control statement only within a compound
statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1mloop-statement = [m
[1m [m
[1mqqqwqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqk[m
[1m mq> <beginning-label> : j[m [1mx[m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1m mq> [1;4mLOOP[m[1m qqwq> compound-use-statement qwqqqk [m
[1m mqqqqqqqqqqqqq<qqqqqqqqqqqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mq> [1;4mEND[m[1m [1;4mLOOP[m[1m qqwqqqqqqqqqqqqqqqqqqwq> [m
[1m mq> <ending-label> j [m
[1m [m
(B)0[m[1mcompound-use-statement = [m
[1m [m
[1mqqqqwq> call-statement qqqqqqqqqqqqqqwqq> ;[m
[1m tq> commit-statement qqqqqqqqqqqqu [m
[1m tq> control-statement qqqqqqqqqqqu [m
[1m tq> delete-statement qqqqqqqqqqqqu [m
[1m tq> get-diagnostics-statement qqqu [m
[1m tq> insert-statement qqqqqqqqqqqqu [m
[1m tq> lock-table-statement qqqqqqqqu[m [1m [m
[1m tq> rollback-statement qqqqqqqqqqu [m
[1m tq> set-transaction-statement qqqu [m
[1m tq> singleton-select-statement qqu [m
[1mtq> start-transaction-statement qu[m
[1m tq> trace-statement qqqqqqqqqqqqqu [m
[1m mq> update-statement qqqqqqqqqqqqj [m
3 – Arguments
3.1 – beginning-label
Assigns a name to a control loop. A beginning label used with the
LEAVE statement lets you perform a controlled exit from a loop. A
named loop is called a labeled loop statement. If you include an
ending label, it must be identical to its corresponding beginning
label. A beginning label must be unique within the procedure in
which the label is contained.
3.2 – compound-use-statement
Identifies the SQL statements allowed in a compound statement
block. See the Compound_Statement HELP topic for the list of
valid statements.
3.3 – END_LOOP
Marks the end of a control loop. If you choose to include the
optional ending label, it must match exactly its corresponding
beginning label. An ending label must be unique within the
procedure in which the label is contained.
The optional end-label argument makes multistatement procedures
easier to read, especially in very complex multistatement
procedure blocks.
3.4 – LOOP
Marks the start of a control loop. A LOOP statement enables you
to execute the associated sequence of SQL statements called a
compound statement. After SQL executes the statements within the
loop, control returns to the LOOP statement at the top of the
loop for subsequent statement execution. Looping occurs until
SQL encounters an error exception or executes a LEAVE statement.
In either case, SQL passes control out of the LOOP block to the
statement immediately after the LOOP statement.
4 – Examples
Example 1: Executing a loop statement
SQL> create table ENROLLMENTS
cont> (last_name char(20),
cont> first_name char(10),
cont> middle_initial char,
cont> class_name char(10));
SQL>
SQL> begin
cont> declare :n integer default 5;
cont> loop
cont> insert into ENROLLMENTS
cont> values ('Jones', 'Robert', 'A',
cont> 'Class ' || CAST(:n as char(1)));
cont> set :n = :n - 1;
cont> if :n <= 0 then
cont> leave;
cont> end if;
cont> end loop;
cont> end;
SQL>
SQL> select * from ENROLLMENTS;
LAST_NAME FIRST_NAME MIDDLE_INITIAL CLASS_NAME
Jones Robert A Class 5
Jones Robert A Class 4
Jones Robert A Class 3
Jones Robert A Class 2
Jones Robert A Class 1
5 rows selected
SQL>