Repetitively executes one or more SQL statements in a compound
loop until an end condition is met.
1 – Environment
You can use the REPEAT control statement in a compound statement
of a multistatement procedure:
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[1mrepeat-statement= [m [1m [m [1mqqwqqqqqqqqqqqqqqqqqqqqqqqwqq> [1;4mREPEAT[m[1m qqqqqqqqqqqqqqqqqqqqk [m [1m mq> <beginning-label>: qj x [m [1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqwq> compound-use-statement qqwqq> [1;4mUNTIL[m[1m qq> predicate qqk [m [1m mqqqqqqqqqqqqq<qqqqqqqqqqqqqqj x [m [1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqqq> [1;4mEND[m[1m [1;4mREPEAT[m[1m qqwqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqq> [m [1m mq> <ending-label> qqj [m
3 – Arguments
3.1 – beginning-label:
Assigns a name to the REPEAT statement. A beginning label used
with the LEAVE statement lets you perform a controlled exit from
a repeat loop. A named repeat loop is called a labeled repeat
loop statement. A beginning label must be unique within the
procedure in which the label is contained.
3.2 – END REPEAT ending-label
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 ending-label argument makes multistatement
procedures easier to read, especially in complex multistatement
procedure blocks.
3.3 – REPEAT compound-use-statement
Repeatedly executes a block of SQL statements until an end
condition is met, as specified by the UNTIL predicate clause.
3.4 – UNTIL predicate
Specifies a condition that controls how many times SQL can
execute the statements embedded within its REPEAT . . . UNTIL
block (collectively referred to as its compound statement). SQL
executes the compound statement once and then evaluates the UNTIL
condition. If it evaluates to false or NULL (unknown) and does
not encounter an error exception, SQL executes the compound
statement again. Each time the search condition evaluates
to false or NULL, the REPEAT statement executes the compound
statement. If the UNTIL condition evaluates to true, SQL bypasses
the compound statement and passes control to the statement after
the END REPEAT statement.
4 – Example
Example 1: Using a REPEAT Statement to List Files in the Current
Directory
SQL> SET VERIFY;
SQL> ATTACH 'FILE SCRATCH';
SQL> CREATE DOMAIN file_name VARCHAR(255);
SQL> CREATE PROCEDURE find_file
cont> (IN :FILESPEC file_name BY DESCRIPTOR,
cont> INOUT :RESULTANT_FILESPEC file_name BY DESCRIPTOR,
cont> INOUT :CONTEXT INTEGER BY REFERENCE);
cont> EXTERNAL NAME LIB$FIND_FILE
cont> LOCATION 'SYS$LIBRARY:LIBRTL.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> COMMENT IS
cont> 'DCL HELP: LIB$FIND_FILE '
cont> / 'The Find File routine is called with a wildcard file'
cont> / 'specification for which it searches. LIB$FIND_FILE '
SQL> CREATE PROCEDURE Find_file_end
cont> (IN :CONTEXT INTEGER BY REFERENCE);
cont> EXTERNAL
cont> NAME LIB$FIND_FILE_END
cont> LOCATION 'SYS$LIBRARY:LIBRTL.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> COMMENT IS
cont> 'DCL HELP: LIB$FIND_FILE_END '
cont> / 'The End of Find File routine is called once'
cont> / 'after each sequence of '
cont> / 'calls to LIB$FIND_FILE. LIB$FIND_FILE_END deallocates'
cont> / 'any saved Record Management Service (RMS) context and'
cont> / 'deallocates the virtual memory used to hold the'
cont> / 'allocated context block.';
SQL> SET FLAGS 'TRACE';
SQL> BEGIN
cont> -- This procedure performs a call to an external
cont> -- routine to list files located in the current
cont> -- default directory
cont> DECLARE :done, :context integer = 0;
cont> DECLARE :search_string FILE_NAME = '*.SQL';
cont> DECLARE :file_spec FILE_NAME;
cont> REPEAT
cont> -- Ask the OpenVMS routine for the next name
cont> CALL find_file (:search_string, :file_spec, :context);
cont> IF POSITION ('*' in :file_spec) = 0
cont> AND POSITION ('%' in :file_spec) = 0
cont> AND POSITION ('...' in :file_spec) = 0
cont> THEN
cont> -- Display the name (there are no wildcards)
cont> TRACE :file_spec;
cont> ELSE
cont> SET :done = 1;
cont> END IF;
cont> -- Exit when we have no more file names
cont> UNTIL :done = 1
cont> END REPEAT;
cont> -- Clean up search context
cont> CALL find_file_end (:context);
cont> END;
~Xt: RDBVMS:[USER.V71]CREATE_ROLES.SQL;1
~Xt: RDBVMS:[USER.V71]TEST.SQL;1
SQL>