Full Learning Guide for pl/SQL 3 large cycles

Full Learning Guide for pl/SQL 3 large loops 1 loop overview each loop consists of two parts: the loop boundary and the loop body. The cyclic boundary consists of pl/SQL reserved words. Code located outside the loop body should not know the internal work of the loop. However, loop is a double-edged sword, and the performance of the program is easily located in the loop. 2 loop type 2.1 simple loop Syntax: [SQL] end loop statement that can be executed by loop; www.2cto.com attribute: attribute description why 1) cannot determine how many times the loop will be executed 2) it is required that the loop should be executed at least once when the when exit or exit when occurs. how to end through exit or exit when. Note: 1) If exit or exit when is exceeded, a simple loop is an endless loop. 2) use Cases of exit when and exit: when there is only one conditional expression to determine whether the loop should end, use exit when [SQL] loop balance_remaining: = account_balance (account_id); exit when balance_remaining <1000; apply_balance (account_id, balance_remaining); end loop; when you need to set the "Return Value" based on different exit conditions, in the case statement, use exit [SQL] loop case www.2cto.com when salary> = 10000 and salary <= 20000 then give_bonus (employee_id, 1500); exit; when salary> 2000 and salary <40000 then give_bonus (employee_id, 1000); exit; when salary> = 40000 then give_bonus (employee_id, 500); exit; else give_bonus (employee_id, 0); exit; end case; end loop; www.2cto.com 2.2 while loop Syntax: [SQL] while condition loop executable statement end loop; attribute: attribute description why 1) the number of cycles cannot be determined beforehand. 2) You want to terminate the loop by using the condition. 3) the loop body does not have to be executed. when the condition is determined, it occurs at the loop boundary, each time before entering the loop body, the Boolean expression that determines the how loop boundary is evaluated as false and null. Note: 1) while loops depend on conditions, if the condition is false or null, the control is not delivered to the loop body. Example: [SQL] while mask_index <= mask_count loop begin retval: = to_date (value_in, fmts (mask_index )); date_converted: = true; exception www.2cto.com when OTHERS then mask_index: = mask_index + 1; end loop; 2.3 for loop 2.3.1 numeric for loop Syntax: [SQL] for loop_index in [reverse] lowest_number .. highest_number loop: the end loop statement that can be executed. attribute: Description of the why attribute. If you only want to execute the loop for a limited number of times, but I don't want to exit the when loop too early. If the index exceeds the upper boundary of the loop, how numeric for loop ends unconditionally as long as it reaches the number of loops specified in the range area. Note: 1) loop_index is automatically declared by pl/SQL engine with an int type local variable. 2) the expression used in the range section is evaluated only once when the loop starts, then valid for the entire lifecycle. 3) the variable used to change the range expression in the loop body does not have any effect on the loop boundary. 4) do not change the loop_index or range Boundary Value in the loop body, this is a pretty bad example of programming habits: [SQL] for loop_index in 1 .. 100 loop if mod (loop_index, 2) = 0 then calc_values (loop_index); end if; www.2cto.com end loop; 2.3.2 cursor type for loop Syntax: [SQL] for record in {cursor_name | select clause} loop executable statement end loop; attributes: attribute description why when each row of records in a cursor needs to be retrieved and processed in sequence, the pl/SQL engine will execute a Data fetch operation after each cycle body is executed, if the % NOTFOUND attribute in the cursor is TRUE, the loop ends. how when all records in the cursor are taken out, the cursor type for loop ends unconditionally. Note: 1) record is declared by the pl/SQL engine hermit. Do not declare a record with the same name as a loop index again. 2) If declare cursor, one column in the select clause is an expression, the alias must be specified for this expression. 3) the specific value of the access cursor record in the loop body is expressed in the sentence notation. 4) the number of cycles is the number of records obtained by the cursor. Example: -- Dynamic index reconstruction [SQL] declare cursor ind is select index_name from user_indexes; begin www.2cto.com for cur in ind loop execute immediate 'alter Index' | cur. index_name | 'rebuilt'; end loop; end; 3. loop label definition: loop alias Syntax: <tag_name> function: 1) tag can be used to explicitly bind the beginning and end of a loop to improve the readability of nested loops. 2) Make the index_loop of the loop more standardized, whether it is a record or a value example: [SQL] <year_loop> for year_number in 2012 .. 2014 loop <month_loop> for month_number in 1 .. 12 loop if year_loop.year_number = 2012 then .. www.2cto.com end if; end loop month_loop; end loop year_loop; 4 cycle Tips 1) index_loop use self-explanatory names 2) Example of obtaining cyclic execution information from closed cursors: [SQL] declare book_count number: = 0 for book_rec in book_cur (author_in => 'Think, water') loop .. process data .. book_count: = book_cur % ROWCOUNT; end loop; if book_count> 10 then ..