1  REPEAT_Control
   Repetitively executes one or more SQL statements in a compound
   loop until an end condition is met.
 

2  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)0repeat-statement=                                            
                                                              
 qqwqqqqqqqqqqqqqqqqqqqqqqqwqq> REPEAT qqqqqqqqqqqqqqqqqqqqk  
   mq> <beginning-label>: qj                               x  
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj  
 mqwq> compound-use-statement qqwqq> UNTIL qq> predicate qqk  
   mqqqqqqqqqqqqq<qqqqqqqqqqqqqqj                          x  
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj  
 mqqq> END REPEAT qqwqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqq>  
                    mq> <ending-label> qqj                    

 

2  Arguments
 

3  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  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  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  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.
 

2  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>