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

  repeat-statement=

  --+-----------------------+--> REPEAT --------------------+
    +-> <beginning-label>: -+                               |
  +----------------------------<----------------------------+
  +-+-> compound-use-statement --+--> UNTIL --> predicate --+
    +-------------<--------------+                          |
  +----------------------------<----------------------------+
  +---> END REPEAT --+--------------------+----------------->
                     +-> <ending-label> --+

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>