Full Learning Guide for PL/SQL 3 large cycles

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]
View plaincopyprint?

1. Loop
2. Executable statements
3. End loop;

The end loop statement that can be executed by the loop;

Attribute:

Attribute	Description
Why	1) the number of times the loop will be executed cannot be determined. 2) At least one loop is required.
When	End when exit or exit when occurs
How	End with exit or exit when

Note:

1) if exit or exit when is removed, a simple loop is an endless loop.

2) Use Cases of exit when and exit:

When only one conditional expression determines whether the loop should end, use exit when

[SQL]
View plaincopyprint?

1. Loop
2. Balance_remaining: = account_balance (account_id );
3. Exit when balance_remaining <1000;
   
4. Apply_balance (account_id, balance_remaining );
5. End loop;

     loop     balance_remaining := account_balance(account_id);     exit when balance_remaining<1000;     apply_balance(account_id,balance_remaining);     end loop;

When the return value needs to be set based on different exit conditions, in the case statement, Use Exit

[SQL]
View plaincopyprint?

1. Loop
2. Case
3. When salary> = 10000
   And salary <= 20000
4. Then
5. Give_bonus (employee ID, 1500 );
6. Exit;
7. When salary> 2000
   And salary <1, 40000
8. Then
9. Give_bonus (employee ID, 1000 );
10. Exit;
11. When salary> = 40000
    
12. Then
13. Give_bonus (employee ID, 500 );
14. Exit;
15. Else
16. Give_bonus (employee_id, 0 );
17. Exit;
18. End case;
    
19. End loop;

     loop     case     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;

2.2 While Loop

Syntax:

[SQL]
View plaincopyprint?

1. While Condition
2. Loop
3. Executable statement
4. End loop;

While condition loop can execute the end loop statement;

Attribute:

Attribute	Description
Why	1) the number of cycles cannot be determined beforehand 2) You want to terminate the loop through conditions 3) The loop body is not mandatory.
When	Condition judgment occurs at the Circular Boundary. This judgment must be performed before each entry to the circular body.
How	The Boolean expression of the Circular Boundary evaluates to 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]
View plaincopyprint?

1. While mask_index <= mask_count
2. Loop
3. Begin
4. Retval: = to_date (value_in, fmts (mask_index ));
5. Date_converted: = true;
   
6. Exception
7. When others
8. Then
9. Mask_index: = mask_index + 1;
10. End;
11. End loop;

  while mask_index <= mask_count  loop    begin    retval :=to_date(value_in,fmts(mask_index));    date_converted :=true;    exception    when OTHERS    then      mask_index :=mask_index+1;    end;  end loop;

2.3 For Loop

2.3.1 numeric For Loop

Syntax:

[SQL]
View plaincopyprint?

1. For loop_index in [reverse] lowest_number... highest_number
   
2. Loop
3. Executable statement
4. End loop;

For loop_index in [reverse] lowest_number... highest_number loop executable statement end loop;

Attribute:

Attribute	Description
Why	If you only want a limited number of execution cycles, but do not want to exit too early
When	When the loop index exceeds the upper limit of the loop
How	A numeric for loop ends unconditionally as long as it reaches the number of cycles specified in the range.

Note:

1) loop_index is automatically declared by PL/SQL engines with an int type local variable.
2) the expression used in the range section is evaluated only once when the loop starts, and then valid throughout the lifecycle.
3) The variable used by the loop body to change the range expression 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 very bad programming habit.

Example:

[SQL]
View plaincopyprint?

1. For loop_index in 1 .. 100
   
2. Loop
3. If Mod (loop_index, 2) = 0
4. Then
5. Calc_values (loop_index );
6. End if;
7. End loop;

  for loop_index in 1..100  loop    if mod(loop_index,2)=0    then      calc_values(loop_index);    end if;  end loop;

2.3.2 cursor type for Loop

Syntax:

[SQL]
View plaincopyprint?

1. For record in {cursor_name |
   Select clause}
2. Loop
3. Executable statement
4. End loop;

For record in {cursor_name | select clause} loop executable statement end loop;

Attribute:

Attribute	Description
Why	To retrieve and process each row of records in a cursor in sequence
When	After each loop body is executed, the PL/SQL engine performs a Data fetch operation. 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 PL/SQL engine hermit. Do not declare a record with the same name as a loop index again.
2) if a column in the select clause is an expression when declare cursor is used, the alias must be specified for this expression.
3) the specific value of the access cursor record in the loop body is expressed by a period.
4) The number of loop executions is the number of records retrieved by the cursor

Example:
-- Dynamic Index Reconstruction

[SQL]
View plaincopyprint?

1. Declare
2. Cursor IND is
   Select index_name from user_indexes;
   
3. Begin
4. For cur in ind
   
5. Loop
6. Execute immediate
7. 'Alter Index' | cur. index_name | 'rebuilt ';
   
8. End loop;
9. End;

  declare    cursor ind is select index_name from user_indexes;  begin    for cur in ind    loop      execute immediate      'alter index '||cur.index_name||' rebuild';    end loop;  end;

3. Cyclic labels

Definition: loop alias

Syntax: <tag_name>

Purpose:
1) tag can be used to explicitly bind the beginning and end of a loop, improving the readability of nested loops.
2) Make the index_loop of the loop more normalized, whether it is a record or a value

Example:

[SQL]
View plaincopyprint?

1. <Year_loop>
2. For year_number in 2012... 2014
   
3. Loop
4. <Month_loop>
5. For month_number
   In 1 .. 12
6. Loop
7. If year_loop.year_number = 2012
8. Then ..
9. End if;
10. End loop month_loop;
11. End loop year_loop;
    

<<year_loop>>for year_number in 2012..2014loop  <<month_loop>>  for month_number in 1..12  loop    if year_loop.year_number=2012     then ..    end if;  end loop month_loop;end loop year_loop;

4. Loop skills

1) Use a self-explanatory name for index_loop
2) Obtain cyclic execution information from closed cursors

Example:

[SQL]
View plaincopyprint?

1. Declare
2. Book_count number: = 0
3. For book_rec in book_cur (author_in =>
   'Think, water ')
4. Loop
5. ... Process data ..
6. Book_count: = book_cur % rowcount;
7. End loop;
8. If book_count> 10
9. Then ..

   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 ..

Address: http://blog.csdn.net/linwaterbin/article/details/7885718