11.12 LOOP

[ Team LiB ]

11.12 LOOP PL/SQL supports the following loop constructs: Infinite LOOP with an exit condition FOR LOOP WHILE LOOP 11.12.1 DO UNTIL LOOP The DO UNTIL LOOP always performs some piece of work, until an exit condition occurs. This LOOP can terminate with an EXIT WHEN statement or an IF condition with an EXIT. If no termination condition occurs then this becomes an infinite loop. As with any loop, an exception, such as divide by zero, can break the loop. Figure 11-5 shows the DO-UNTIL LOOP construct. Figure 11-5. DO UNTIL LOOP. The DO UNTIL LOOP always makes at least one pass through the loop block. Following each pass is the test for continuing. The syntax templates are: A loop that exits with an IF statement: LOOP <statements> IF (expression) THEN EXIT; END IF; END LOOP; A loop that exits with an EXIT WHEN statement: LOOP <statements> EXIT WHEN <boolean expression> END LOOP; This decrements a count and uses EXIT WHEN to terminate the loop at zero. DECLARE counter INTEGER := 10; BEGIN LOOP dbms_output.put_line(counter); counter := counter - 1; EXIT WHEN counter = 0; END LOOP; END; The same functionality is implemented with an IF condition. The difference between the previous block and the following is style. DECLARE counter INTEGER := 10; BEGIN LOOP dbms_output.put_line(counter); counter := counter - 1; IF (counter = 0) THEN EXIT; END IF; END LOOP; END; The following PL/SQL block decrements a variable that has a POSITIVE datatype. POSITIVE type variables must be greater than zero. When COUNTER is decremented to zero, the exception is raised. The exception handler catches the exception. This relies on neither an EXIT nor an IF condition to terminate the loop. DECLARE counter POSITIVE := 10; BEGIN LOOP BEGIN dbms_output.put_line(counter); counter := counter - 1; EXCEPTION WHEN VALUE_ERROR THEN EXIT; END; END LOOP; END; 11.12.2 WHILE LOOP The WHILE LOOP tests for a condition prior to any logic execution. The number of passes through the logic may be zero. The WHILE LOOP construct is shown in Figure 11-6. Figure 11-6. WHILE LOOP. The syntax for this construct is WHILE (condition is true) LOOP <statements> END LOOP; The condition test must be a Boolean expression that evaluates to TRUE or FALSE. The following loop generates a lower case alphabet list. DECLARE ascii_char INTEGER := 97; alphabet VARCHAR2(26); BEGIN WHILE (NVL(LENGTH(alphabet),0) < 26) LOOP alphabet := alphabet || chr(ascii_char); ascii_char := ascii_char + 1; END LOOP; dbms_output.put_line(alphabet); END; Evaluation of the WHILE expression should not include a NULL because NULL cannot evaluate to TRUE or FALSE. A NULL length for a string is used to illustrate this. The length of a string, which has no assignment, has no length. That is, if we declare a string as follows: alphabet VARCHAR2(26); The length of that string, prior is any assignment, is null. The following expression does not return 0 or a number, but NULL. LENGTH(alphabet) Had the PL/SQL WHILE LOOP been written as follows, the LOOP would never make a single pass because the WHILE condition would never evaluate to TRUE. WHILE (LENGTH(alphabet) < 26) LOOP Loop logic END LOOP; 11.12.3 FOR LOOP The FOR LOOP is a form of WHILE LOOP and has the following syntax. FOR C IN [REVERSE] A . . B LOOP <statements> END LOOP; From a first appearance, it seems that a FOR LOOP always makes at least one pass. This is not always the case. The symbols A and B in the preceding syntax can be literals or expressions that evaluate to an integer value. If, upon entry into the loop, A is greater than B, then the statements in the loop will not execute. In summary: Upon initial loop entry A < B Statements execute several times depending on values for A and B. A = B Statements execute ONCE�only one pass through the loop. A > B The loop is skipped�no iteration of the loop. The loop construct for the FOR LOOP always includes an initial evaluation of (B-A), which will exit when this is less than C. Figure 11-7 shows the FOR LOOP construct. Figure 11-7. FOR LOOP. The following block contains a FOR LOOP where, upon initial entry, (B-A) is negative. Therefore there will be no DBMS_OUTPUT from this block except the message that the code never entered the loop: DECLARE str VARCHAR2(10); x integer := 11; y integer := 10; BEGIN FOR i in x..y LOOP str := str || 'abc'; END LOOP; dbms_output.put_line(NVL(str, 'Never entered the loop.')); END; The FOR LOOP counter is shown in the syntax as the letter C. FOR C IN [REVERSE] A . . B LOOP <statements> END LOOP; This counter is an implicitly declared variable. You choose the name of this variable. You can duplicate the name of another variable�this will make the code confusing to read, but it will work. The following declares a variable and a loop counter by the same name, which is confusing to the reader. DECLARE my_counter INTEGER := 100; BEGIN FOR my_counter in 1..2 LOOP dbms_output.put_line(my_counter); END LOOP; dbms_output.put_line(my_counter); END; The loop counter, MY_COUNTER, has scope only inside the loop. Hence the DBMS_OUTPUT from within the loop will print 1 and 2. The PL/SQL block variable MY_COUNTER has scope over the entire block. The last DBMS_OUTPUT prints 100. To keep code simple, use simple, nonduplicating names for loop counters. Loop counters only have scope within the loop. The following will not compile because MY_COUNTER is referenced out of scope. BEGIN -- THIS WILL NOT COMPILE FOR my_counter in 1..2 LOOP dbms_output.put_line(my_counter); END LOOP; dbms_output.put_line(my_counter); END; You can always capture the counter by copying it into another variable. The counter is implicitly declared, but it can be referenced like any IN mode variable. You can read it but you cannot change it. The PL/SQL with a counter to the left of an assignment operator will not compile. The following block captures the counter and prints the number of iterations made during the loop. DECLARE number_of_passes INTEGER; BEGIN FOR ctx in 1..2 LOOP number_of_passes := ctx; END LOOP; dbms_output.put_line('counter='||number_of_passes); END; 11.12.4 DO-WHILE-DO LOOP The DO-WHILE-DO LOOP construct, shown in Figure 11-8, is very common in applications. In this construct the code performs some task and then tests a condition. It continues with a second task if the test is successful. A common scenario is the use of an explicit cursor. This loop fetches a row and tests for an end-of-cursor. The second task processes the fetched row. Figure 11-8. DO-WHILE-DO LOOP. The termination of the loop can be with an EXIT WHEN of IF THEN EXIT statement. The syntax for this construct is the following. The first loop uses an IF THEN EXIT to terminate the loop. LOOP <statements> IF (expression) THEN EXIT; END IF; <statements> END LOOP; This loop terminates with an EXIT WHEN. LOOP <statements> EXIT WHEN <boolean expression> <statements> EXIT LOOP; The following is an explicit cursor loop that fetches student names from the STUDENTS table. The first task is to fetch the row. The EXIT condition is based on the success or failure of fetching another row. The second task in the loop is to print the student name. DECLARE CURSOR C1 IS SELECT student_name FROM students; cursor_record C1%ROWTYPE; BEGIN OPEN C1; LOOP FETCH C1 INTO cursor_record; EXIT WHEN C1%NOTFOUND; dbms_output.put_line(cursor_record.student_name); END LOOP; END; 11.12.5 Encapsulating the Logic of the Loop The logic within a loop can be extensive and difficult to read, especially if it spans pages. Portions of the logic can be broken out into separate procedures; these procedures often form the basis for new application packages. By partitioning the code into small modules, the loop is short and simple. The loop body contains a few procedure and function calls. The following is a template for a loop that must read and process records from a file. LOOP get next record -- this would be a procedure call. if end of file then exit �- uses a function. process record -- this would be a procedure call. END LOOP This loop can be written with all the detail logic coded directly in the loop. This could result in a loop that spans a page or two. An alternative approach is to encapsulate major components of the loop body into specific procedures and functions. The loop body calls these subprograms. The following illustrates this strategy. For this application, a building block will be a subprogram that simply returns a record from a file. Once we write that subprogram, we can use it in the loop body. This is illustrated in Figure 11-9. Figure 11-9. Get Next Record. The function to fetch a record must have a FILE argument as an IN parameter and a VARCHAR2 as an out parameter. The GET_NEXT_RECORD is designed to encapsulate any exception handling and return a TRUE/FALSE indicator for a successful read. The code for GET_NEXT_RECORD is. CREATE OR REPLACE FUNCTION get_next_record (FILE IN utl_file.file_type, text OUT VARCHAR2) RETURN BOOLEAN IS BEGIN utl_file.get_line(FILE, text); RETURN false; EXCEPTION WHEN no_data_found THEN return true; END get_next_record; The application loop continues processing based on a successful or unsuccessful read. There is no exception handling in the loop. The exception is encapsulated in the function GET_NEXT_RECORD. This is a DO-WHILE-DO loop. The core part of the application code is the following. LOOP end_of_file := get_next_record(FILE, text); exit when end_of_file; dbms_output.put_line(text); END LOOP; The core of an application program can be easily muddled with the exceptions exported by application programs such as UTL_FILE. The function GET_NEXT_RECORD can form a basis for encapsulating an interface to a package such as UTL_FILE. This layered interface allows applications to write their core logic based on simple constructs such as loops controlled by conditional expressions. A complete PL/SQL block that uses the GET_NEXT_RECORD function but also contains the UTL_FILE call to open the file is the following. DECLARE FILE utl_file.file_type; text VARCHAR2(120); end_of_file BOOLEAN := FALSE; BEGIN FILE := utl_file.fopen('C:/TEST,'test.txt','r'); LOOP end_of_file := get_next_record(FILE, text); exit when end_of_file; dbms_output.put_line(text); END LOOP; utl_file.fclose(FILE); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN dbms_output.put_line('a'); WHEN UTL_FILE.INVALID_MODE THEN dbms_output.put_line('b'); WHEN UTL_FILE.INVALID_OPERATION THEN dbms_output.put_line('c'); END; A more desirable application for the aforementioned PL/SQL block would be the following architecture in Figure 11-10. Figure 11-10. Package with Get Next Record. Following the model in Figure 11-10 the application loop acquires a style as shown next. DECLARE token our_package.token_type; EOF BOOLEAN; str VARCHAR2(200); LOOP end_of_file := our_package.get_next_record(token, str); exit when end_of_file; process file record returned. END LOOP;

[ Team LiB ]