Sample PL/SQL Cursor Loops
A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.
Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.
You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.
Simple loop with local variables ↓
This simple loop example uses a static cursor and local variables that map to each column returned by the cursor. It uses the %TYPE to anchor local variables to the data dictionary.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare local variables that are anchored to column data types. lv_title item.item_title%TYPE; lv_subtitle item.item_subtitle%TYPE; lv_rating item.item_rating%TYPE; -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the three local variables. FETCH c INTO lv_title , lv_subtitle , lv_rating; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variables on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||lv_title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||lv_rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
Simple loop with a local record structure variable ↓
This simple loop example uses a static cursor, a local record structure data type, and a local variable of the local record structure data type. The local record structure maps to the columns returned by the cursor. It uses explicit data types that match those of the table. You could also use the %TYPE to anchor the elements of the structure in the local data type, like the prior example. With explicit data types, you must modify the program when the definition of the table changes otherwise your program may fail at runtime.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a local record data type, with explicit data types (you could use %TYPE here too). TYPE title_type IS RECORD ( title VARCHAR2(60) , subtitle VARCHAR2(60) , rating VARCHAR2(8)); -- Declare a local variable of the local record structure data type. item_record TITLE_TYPE; -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the local record structure variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
Simple loop with a local cursor structure variable ↓
This simple loop example uses a static cursor, a local variable that inherits its record structure from the local cursor. It does so by using the %ROWTYPE against the cursor. Often the %ROWTYPE is only applied when you return a structure that maps to the complete table definition. Sometimes I think using cursor_name%ROWTYPE is the only real purpose for shared cursors but I know that’s not really true.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the local record structure variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
For loop with an implicit record structure variable ↓
This FOR loop example uses a static cursor. When a FOR loop uses a cursor it becomes a cursor FOR loop, and the iterator i becomes an implicit cursor record structure. You should note that this is a very compact program because a cursor FOR loop manages opening and closing the cursor, and handling the loop exit implicitly. Many programmers default to this approach whenever it fits because it is simple and easy to implement.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start a cursor FOR loop block. FOR i IN c LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||i.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||i.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; END; / |
For loop without a declaration block ↓
This FOR loop example uses a static cursor. Like the prior example, this FOR loop uses a cursor but it is defined inside the actual FOR loop structure. That approach eliminates the need for the declaration block. It’s a nice feature that some may call a trick. As a rule, you should really avoid this style because formally defining your cursor is a good practice and improves code maintainability. Naturally, this is probably the most compact program because everything is managed implicitly including the cursor assignment to the loop structure.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SET SERVEROUTPUT ON SIZE 1000000 BEGIN -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start a cursor FOR loop block with the static cursor in the definition. FOR i IN (SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i) LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||i.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||i.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; END; / |
WHILE loop with a cursor guard on entry statement ↓
This WHILE loop example uses a static cursor, like the prior examples. Unlike the prior example, the WHILE loop is a guard on entry loop. The previous loops were guard on exit loops. This has much the same structure as the simple loop with a cursor record structure variable but differs on two key points.
Point one is that you must have two FETCH statements because the guard on entry condition checks whether any records are found in the cursor. The first FETCH statement checks whether at least a one row is returned. When true or false, it initializes the cursor attributes, like %FOUND. The second FETCH statement handles the second row returned to last row returned from the cursor.
Point two is that you don’t have an EXIT WHEN cursor_name%NOTFOUND inside the loop because the guard condition stops the loop when it fails to find at least one record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Fetch the first record to put into context the cursor attributes, like %FOUND. FETCH c INTO item_record; -- Start the simple loop block with a guard on entry condition. WHILE (c%FOUND) LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); -- Fetch the second and subsequent rows of the cursor and assign it to a local variables. FETCH c INTO item_record; END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
The WHILE loop as presented is complex because of the pre-loop FETCH statement, and the internal FETCH statement. It’s may appear better to convert it to a pseudo-infinite loop. You do that by setting the condition in a WHILE loop to a TRUE constant, like this example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block with a guard on entry condition. WHILE (TRUE) LOOP -- Fetch the record set into a user-defined variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
The preceding example behaves much like a simple loop, and you have to ask what is the benefit of WHILE (TRUE) LOOP over LOOP. Generally, it appears that the WHILE loop syntax is slighly longer to type.
I’m sure this will help my students and hope it helps somebody else.
Subscribe via RSS