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 repeat-statement= --+-----------------------+--> REPEAT --------------------+ +-> : -+ | +----------------------------<----------------------------+ +-+-> compound-use-statement --+--> UNTIL --> predicate --+ +-------------<--------------+ | +----------------------------<----------------------------+ +---> END REPEAT --+--------------------+-----------------> +-> --+ 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>