plsql中的循环

plsql的循环有三种类型:

• The simple or infinite loop
• The FOR loop (numeric and cursor)
• The WHILE loop

 

The simple loop
It’s called simple for a reason: it starts simply with the LOOP keyword and ends
with the END LOOP statement. The loop will terminate if you execute an EXIT,
EXIT WHEN, or RETURN within the body of the loop (or if an exception is raised):

PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
)
IS
l_current_year PLS_INTEGER := start_year_in;
BEGIN
LOOP
EXIT WHEN l_current_year > end_year_in;
display_total_sales (l_current_year);
l_current_year := l_current_year + 1;
END LOOP;
END display_multiple_years;

 

当不知道循环的范围，且至少循环一次时可用。

Property	Description
How the loop is terminated	The simple loop is terminated when an EXIT statement is executed in the body of the loop. If this statement is not executed, the simple loop becomes a true infinite loop.
When the test for termination takes place	The test takes place inside the body of the loop, and then only if an EXIT or EXIT WHEN statement is executed. Therefore, the body—or part of the body—of the simple loop always executes at least once.
Reason to use this loop	Use the simple loop when: • You are not sure how many times you want the loop to execute. • You want the loop to run at least once.

 

The FOR loop
Oracle offers a numeric and cursor FOR loop. With the numeric FOR loop, you
specify the start and end integer values, and PL/SQL does the rest of the work for
you, iterating through each intermediate value, and then terminating the loop:

对于numeric for loop 

PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
)
IS
BEGIN
FOR l_current_year IN start_year_in .. end_year_in
LOOP
display_total_sales (l_current_year);
END LOOP;
END display_multiple_years;

当知道循环范围时可用，循环变量在loop范围内有效，为number类型，plsql隐式定义，会为其自动加1.

Property	Description
How the loop is terminated	The numeric FOR loop terminates unconditionally when the number of times specified in its range scheme has been satisfied. You can also terminate the loop with an EXIT statement, but this is not recommended.
When the test for termination takes place	After each execution of the loop body, PL/SQL increments (or decrements if REVERSE is specified) the loop index and then checks its value. When it exceeds the upper bound of the range scheme, the loop terminates. If the lower bound is greater than the upper bound of the range scheme, the loop never executes its body.
Reason to use this loop	Use the numeric FOR loop when you want to execute a body of code a fixed number of times and do not want to halt that looping prematurely.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

当要从游标或select语句取出全部的记录时，可用。循环变量类型为cursor_name%rowtype，plsql隐式定义。

用cursor for loop即简洁又清晰,如:

DECLARE
CURSOR occupancy_cur IS
SELECT pet_id, room_number
FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
occupancy_rec occupancy_cur%ROWTYPE;
BEGIN
OPEN occupancy_cur;
LOOP
FETCH occupancy_cur INTO occupancy_rec;
EXIT WHEN occupancy_cur%NOTFOUND;
update_bill
(occupancy_rec.pet_id, occupancy_rec.room_number);
END LOOP;
CLOSE occupancy_cur;
END;

 而用cursor for loop,会省掉open cursor,fentch cursor,if cursor%notfound,close cursor一系列操作，都由plsql隐式完成了。游标的开关在循环中隐式完成。如:

DECLARE
CURSOR occupancy_cur IS
SELECT pet_id, room_number
FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
BEGIN
FOR occupancy_rec IN occupancy_cur
LOOP
update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);
END LOOP;
END;

Property	Description
How the loop is terminated	The cursor FOR loop terminates unconditionally when all of the records in the associated cursor have been fetched. You can also terminate the loop with an EXIT statement, but this is not recommended.
When the test for termination takes place	After each execution of the loop body, PL/SQL performs another fetch. If the %NOTFOUND attribute of the cursor evaluates to TRUE, then the loop terminates. If the cursor returns no rows, then the loop never executes its body.
Reason to use this loop	Use the cursor FOR loop when you want to fetch and process every record in a cursor.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The WHILE loop
The WHILE loop is very similar to the simple loop; a critical difference is that it
checks the termination condition up front. It may not even execute its body a single
time:

PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
)
IS
l_current_year PLS_INTEGER := start_year_in;
BEGIN
WHILE (l_current_year <= end_year_in)
LOOP
display_total_sales (l_current_year);
l_current_year := l_current_year + 1;
END LOOP;
END display_multiple_years;

while loop和simple loop差不多，只是根据具体的条件进行循环。

Property	Description
How the loop is terminated	The WHILE loop terminates when the Boolean expression in its boundary evaluates to FALSE or NULL.
When the test for termination takes place	The test for termination of a WHILE loop takes place in the loop boundary. This evaluation occurs prior to the first and each subsequent execution of the body. The WHILE loop, therefore, is not guaranteed to execute its loop even a single time.
Reason to use this loop	Use the WHILE loop when: • You are not sure how many times you must execute the loop body. • You will want to conditionally terminate the loop. • You don’t have to execute the body at least one time.