Loop processing in Oracle PL/SQL

Oracle PL/SQL loop processing today is the loop iteration processing in Oracle, because many netizens have searched FOR the keyword "SQL FOR loop" from their blog statistics ", so I want to explain my understanding here. Www.2cto.com PL/SQL is also the same as our commonly used programming languages. It provides While, For, and other loops. Let's create several examples to illustrate. The first is the While loop: www.2cto.com -- while loop procedure loop_while (start_value in number, end_value in number) is current_value number: = start_value; begin while current_value <= end_value loop limit ('now number: '| current_value); current_value: = current_value + 1; end loop; end loop_while; after specifying the initial and end values of the loop, we can see that a row of numbers between the two values is printed out; of course, as long as the while LOOP condition evaluates to true, the loop will continue, if the evaluate condition is false or null, the loop ends. This loop condition is first judged before every execution of the loop body, so the while LOOP cannot guarantee that the loop body will be executed. Therefore, if we cannot predict the number of inspection times in advance, we can use While for loop processing. There are two types of For loops: Numeric FOR loop and cursor FOR loop: -- numeric For loop procedure loop_num_for (lowest in number, highest in number) isbegin FOR even_number in lowest .. highest -- Ascending loop -- processing non-smooth growth index if mod (even_number, 2) = 0 then dbms_output.put_line ('now number: '| even_number); end if; end loop; end loop_num_for; this type of loop knows the number of cycles at the beginning. Note that you do not need to declare the loop index here, PL/SQL automatically and implicitly uses an integer-type local variable as its cyclic index. To perform a descending loop, the reverse keyword must be added, and the upper and lower boundaries of the loop must be smooth. No need to change: FOR even_number in reverse lowest .. highest loop dbms_output.put_line ('now number: '| even_number); end loop; In a numeric FOR loop, indexes are always incremented or decreased in units of 1, so if our loop condition is not so ideal for smooth growth, we must use some logic code or techniques to achieve our goal. If we need to process many rows of records, we can use the cursor type FOR loop: -- The cursor type For loop procedure loop_cursor_forisbegin declare cursor userinfo_cur is select * from userinfo_table; begin FOR userinfo_rec in userinfo_cur loop dbms_output.put_line ('username is: '| userinfo_rec.user_name); end loop; end loop_cursor_for; after all records in the cursor are obtained, the FOR loop is automatically terminated. The OPEN and CLOSE cursors are not displayed here, And the PL/SQL engine automatically processes them. The preceding loop statements can use EXIT or exit when to terminate the loop, but it is best not to do so, because this may cause problems in the loop logic, eventually, SQL code is difficult to trace and debug. The SQL statement used for testing IS attached: create or replace package body LOOP_TEST_DEMO is -- while loop procedure loop_while (start_value in number, end_value in number) IS current_value number: = start_value; begin while current_value <= end_value loop dbms_output.put_line ('now number: '| current_value); current_value: = current_value + 1; end loop; end loop_while; -- numeric For loop procedure loop_num_for (lowest in number, highest in number) is begin FOR even_number in lowest .. highest -- Ascending loop -- dbms_output.put_line (even_number); -- processing non-smooth growth index if mod (even_number, 2) = 0 then dbms_output.put_line ('now number: '| even_number ); end if; end loop; -- descending order FOR even_number in reverse lowest .. highest loop evaluate ('now number: '| even_number); end loop; end loop_num_for; -- cursor type For loop procedure loop_cursor_for is begin declare cursor userinfo_cur is select * from greenet_user_info; begin FOR userinfo_rec in userinfo_cur loop dbms_output.put_line ('username is: '| userinfo_rec.user_name); end loop; end loop_cursor_for; end LOOP_TEST_DEMO; Source: http://maoniu602.cnblogs.com/