Xem mẫu
Chapter 29: An Introduction to PL/SQL 523
RADIUS AREA ---------- ----------
3 28.27 4 50.27 5 78.54 6 113.1
Because the area value for a Radius value of 6 exceeds 100, no further Radius values are processed and the PL/SQL block completes.
Simple Cursor Loops
You can use the attributes of a cursor—such as whether or not any rows are left to be fetched— as the exit criteria for a loop. In the following example, a cursor is executed until no more rows are returned by the query. To determine the status of the cursor, the cursor’s attributes are checked. Cursors have four attributes you can use in your program:
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
A record can be fetched from the cursor.
No more records can be fetched from the cursor.
The cursor has been opened.
The number of rows fetched from the cursor so far.
The %FOUND, %NOTFOUND, and %ISOPEN cursor attributes are Booleans; they are set to either TRUE or FALSE. Because they are Boolean attributes, you can evaluate their settings without explicitly matching them to values of TRUE or FALSE. For example, the following command will cause an exit to occur when rad_cursor%NOTFOUND is TRUE:
exit when rad_cursor%NOTFOUND;
In the following listing, a simple loop is used to process multiple rows from a cursor:
declare
pi constant NUMBER(9,7) := 3.1415927; area NUMBER(14,2);
cursor rad_cursor is
select * from RADIUS_VALS; rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor; loop
fetch rad_cursor into rad_val; exit when rad_cursor%NOTFOUND;
area := pi*power(rad_val.radius,2);
insert into AREAS values (rad_val.radius, area); end loop;
close rad_cursor; end;
/
524 Part IV: PL/SQL
The loop section of the PL/SQL block uses values from the RADIUS_VALS table as its input. Instead of basing the exit criteria on the Area value, the cursor’s %NOTFOUND attribute is checked. If no more rows are found in the cursor, then %NOTFOUND will be TRUE—and, therefore, the loop will be exited. The commented version of the loop is shown in the following listing:
loop
/* Within the loop, fetch a record. */ fetch rad_cursor into rad_val;
/* If the fetch attempt reveals no more */ /* records in the cursor, then exit the loop. */
exit when rad_cursor%NOTFOUND;
/* If the fetch attempt returned a record, */ /* then process the Radius value and insert */ /* a record into the AREAS table. */ area := pi*power(rad_val.radius,2);
insert into AREAS values (rad_val.radius, area); /* Signal the end of the loop. */
end loop;
When the preceding PL/SQL block is executed, every record in the RADIUS_VALS table will be processed by the loop. So far, the RADIUS_VALS table only contains one record—a Radius value of 3.
NOTE
Prior to executing the PL/SQL block for this section, add two new Radius values—4 and 10—to the RADIUS_VALS table.
The following listing shows the addition of the new records to the RADIUS_VALS table:
insert into RADIUS_VALS values (4); insert into RADIUS_VALS values (10); commit;
select *
from RADIUS_VALS order by Radius;
RADIUS ----------
3 4 10
Once the new records have been added to the RADIUS_VALS table, execute the PL/SQL block shown earlier in this section. The output of the PL/SQL block is shown in the following listing:
select * from AREAS
order by Radius;
Chapter 29: An Introduction to PL/SQL 525
RADIUS AREA ---------- ----------
3 28.27 4 50.27 10 314.16
The query of the AREAS table shows that every record in the RADIUS_VALS table was fetched from the cursor and processed. When there were no more records to process in the cursor, the loop was exited and the PL/SQL block completed.
FOR Loops
A simple loop executes until an exit condition is met, whereas a FOR loop executes a specified number of times. An example of a FOR loop is shown in the following listing. The FOR loop’s start is indicated by the keyword for, followed by the criteria used to determine when the processing is complete and the loop can be exited. Because the number of times the loop is executed is set when the loop is begun, an exit command isn’t needed within the loop.
In the following example, the areas of circles are calculated based on Radius values ranging from 1 through 7, inclusive.
delete from AREAS;
declare
pi constant NUMBER(9,7) := 3.1415927; radius INTEGER(5);
area NUMBER(14,2); begin
for radius in 1..7 loop
area := pi*power(radius,2);
insert into AREAS values (radius, area); end loop;
end; /
The steps involved in processing the loop are shown in the following commented listing:
/* Specify the criteria for the number of loop */ /* executions. */
for radius in 1..7 loop
/* Calculate the area using the current Radius */ /* value. */ area := pi*power(radius,2);
/* Insert the area and radius values into the AREAS */ /* table. */ insert into AREAS values (radius, area);
/* Signal the end of the loop. */ end loop;
Note that there is no line that says
radius := radius+1;
526 Part IV: PL/SQL
in the FOR loop. Because the specification of the loop specifies
for radius in 1..7 loop
the Radius values are already specified. For each value, all the commands within the loop are executed (these commands can include other conditional logic, such as if conditions). Once the loop has completed processing a Radius value, the limits on the for clause are checked, and either the next Radius value is used or the loop execution is complete.
Sample output from the FOR loop execution is shown in the following listing:
select * from AREAS
order by Radius;
RADIUS AREA ---------- ----------
1 3.14 2 12.57 3 28.27 4 50.27 5 78.54 6 113.1 7 153.94
7 rows selected.
Cursor FOR Loops
Whereas a FOR loop executes a specified number of times, a Cursor FOR loop uses the results of a query to dynamically determine the number of times the loop is executed. In a Cursor FOR loop, the opening, fetching, and closing of cursors is performed implicitly; you do not need to explicitly specify these actions.
The following listing shows a Cursor FOR loop that queries the RADIUS_VALS table and inserts records into the AREAS table:
delete from AREAS;
declare
pi constant NUMBER(9,7) := 3.1415927; area NUMBER(14,2);
cursor rad_cursor is
select * from RADIUS_VALS; begin
for rad_val in rad_cursor loop
area := pi*power(rad_val.radius,2);
insert into AREAS values (rad_val.radius, area); end loop;
end; /
Chapter 29: An Introduction to PL/SQL 527
In a Cursor FOR loop, there is no open or fetch command. The command
for rad_val in rad_cursor
implicitly opens the rad_cursor cursor and fetches a value into the rad_val variable. When no more records are in the cursor, the loop is exited and the cursor is closed. In a Cursor FOR loop, there is no need for a close command. Note that rad_val is not explicitly declared in the block.
The loop portion of the PL/SQL block is shown in the following listing, with comments to indicate the flow of control. The loop is controlled by the existence of a fetchable record in the rad_ cursor cursor. There is no need to check the cursor’s %NOTFOUND attribute—that is automated via the Cursor FOR loop.
/* If a record can be fetched from the cursor, */ /* then fetch it into the rad_val variable. If */ /* no rows can be fetched, then skip the loop. */
for rad_val in rad_cursor
/* Begin the loop commands. */ loop
/* Calculate the area based on the Radius value */ /* and insert a record into the AREAS table. */
area := pi*power(rad_val.radius,2);
insert into AREAS values (rad_val.radius, area);
/* Signal the end of the loop commands. */ end loop;
Sample output is shown in the following listing; for this example, the RADIUS_VALS table has three records, with Radius values of 3, 4, and 10:
select *
from RADIUS_VALS order by Radius;
RADIUS ----------
3 4 10
The execution of the PL/SQL block with the Cursor FOR loop will generate the following records in the AREAS table:
select * from AREAS
order by Radius;
RADIUS AREA ---------- ----------
3 28.27 4 50.27 10 314.16
...
- tailieumienphi.vn
nguon tai.lieu . vn