Page 219
When a cursor is opened, the SQL is executed and the corresponding data set computed. However, no rows have actually been returned to the program. You use FETCH to get one row at a time. The row fetched remains the current row until another fetch is performed. You can only fetch in the forward direction; there is no control to move back in the data set.
Cursor AttributesCursors have the attributes as described in Table 10.7.
Table 10.7 Cursor Attributes
Attribute | Return Value | Description |
ISOPEN | TRUE/FALSE | Indicates whether a cursor is open or closed |
FOUND | TRUE/FALSE | Indicates whether a row was found |
NOTFOUND | TRUE/FALSE | Indicates whether a row was not found |
ROWCOUNT | NUMBER | The ordinal value of each row retrieved (1st, 2nd, 3rd, ) |
Here are some examples of their use:
IF (orders_cur%FOUND) THEN -- got an order OPEN items_cur(orders_cur.orderno); -- open a related cursor LOOP EXIT WHEN items_cur%NOTFOUND; -- break out when done -- show how many rows were processed so far DBMS_OUTPUT.put_line(`On Row #' || TO_CHAR(items_cur%ROWCOUNT)); END LOOP; END IF; -- got an order IF (items_cur%ISOPEN) THEN -- close the cursor CLOSE items_cur; END IF; -- close an open cursor
Cursor FOR LoopsA cursor FOR loop is the simplest way to use cursors. The opening, fetching and closing of the cursor are handled automatically within the scope of the FOR loop. The return record variable is also defined implicitly for you, and cannot be referenced outside the scope of the loop. The cursor can take parameters. You can either declare the cursor in the declaration section or in the body of the cursor FOR loop itself (see Listing 10.15).
Listing 10.15 cfor1.sqlSimple Use of Cursor FOR Loop
DECLARE CURSOR get_tables IS SELECT * FROM user_tables; BEGIN FOR get_tables_cur IN get_tables LOOP DBMS_OUTPUT.put_line(get_tables_cur.table_name); END LOOP; END; /
Page 220
If you run this as scott/tiger, you would get the following:
BONUS DEPT EMP SALGRADE PL/SQL procedure successfully completed.
Lines 2_3 define the cursor.
Line 5 defines the cursor record variable to be used with the cursor.
Line 6 uses dot notation to refer to the column specified in the cursor.
As Listing 10.16 shows, you can be even more concise and yet still get the same results:
Listing 10.16 cfor2.sqlA Simple FOR Loop, Simpler
BEGIN FOR get_tables_cur IN (SELECT * FROM user_tables) LOOP DBMS_OUTPUT.put_line(get_tables_cur.table_name); END LOOP; END; /
The difference is that the cursor is also defined locally to the loop. This is fine for simple programs.
You can also take a parameter and search for a specific table (see Listing 10.17).
Listing 10.17 cfor3.sqlSimple FOR Loop Cursor Takes Parameters
DECLARE CURSOR get_tables(Powner all_tables.owner%TYPE) IS SELECT * FROM all_tables WHERE owner = Powner; local_owner all_tables.owner%TYPE := `DEMO'; -- search criteria BEGIN FOR get_tables_cur IN get_tables(local_owner) LOOP DBMS_OUTPUT.put_line(get_tables_cur.table_name); END LOOP; END; /
Notice the good use of based variables, especially in the cursor definition, and how the parameter was passed into the cursor. This time you get (because now you're looking at only those tables in the schema DEMO that were made visible to scott):
CUSTOMER DEPARTMENT EMPLOYEE
Page 221
JOB LOCATION SALARY_GRADE SALES_ORDER PL/SQL procedure successfully completed.
For larger, more complex programs, define your cursor within the scope of the enclosing block (as in the preceding), and control the cursor yourself.
Opening, Fetching, and Closing a CursorThe longer winded approach, starting with the last example, looks like Listing 10.18.
Listing 10.18 ofc.sqlControlling the Cursor Yourself
DECLARE -- cursor definitions CURSOR get_tables(Powner all_tables.owner%TYPE) IS SELECT * FROM all_tables WHERE owner = Powner; -- record variable definitions get_tables_rec get_tables%ROWTYPE; -- local variables local_owner all_tables.owner%TYPE := `DEMO'; -- search criteria BEGIN OPEN get_tables(local_owner); -- compute rows to return LOOP -- find all DEMO tables available to SCOTT FETCH get_tables INTO get_tables_rec; -- try to get a row EXIT WHEN get_tables%NOTFOUND; -- no more rows DBMS_OUTPUT.put_line(get_tables_rec.table_name); END LOOP; CLOSE get_tables; -- done with this cursor END; /
You get exactly the same set as before. This time you defined the record variable to receive the rows as based on the cursor. You could have defined a record variable yourself (using TYPE RECORD IS ). In fact, you could have defined individual variables to FETCH into, but basing a variable on the cursor return type is simpler and easier to maintain. Now if you change the column list for the cursor, you won't have to modify anything else involving the record variable.
After opening the cursor, which computes the rows that the cursor will return (although it hasn't actually returned anything yet), you loop through all rows, fetching one at a time. You need to test for end of set and break out of the loop, as in line 14. When the loop is terminated, line 17 closes the loop. In order to reuse the cursor, you must open it again, perhaps with a new parameter this time.
Suppose you only are interested in one row. Listing 10.19 illustrates the simplest solution.
Page 222
Listing 10.19 onerow.sqlExplicitly Fetching One Row
DECLARE -- cursor definitions CURSOR get_tables(Powner all_tables.owner%TYPE) IS SELECT * FROM all_tables WHERE owner = Powner; -- record variable definitions get_tables_rec get_tables%ROWTYPE; -- local variables local_owner all_tables.owner%TYPE := `DEMO'; -- search criteria BEGIN OPEN get_tables(local_owner); -- compute rows to return FETCH get_tables INTO get_tables_rec; -- try to get a row IF (get_tables%FOUND) THEN -- got a row DBMS_OUTPUT.put_line(get_tables_rec.table_name); END IF; CLOSE get_tables; -- done with this cursor END; /
This time, you get:
CUSTOMER PL/SQL procedure successfully completed.
Notice that the cursor attribute %FOUND was used to check if you actually fetched something. If nothing had been returned, the record variable would have the same value as it did before the FETCH. In fact, let's prove it (see Listing 10.20).
Listing 10.20 badfetch.sqlFETCH Only Overwrites a Record Variable on Success
DECLARE -- cursor definitions CURSOR get_tables(Powner all_tables.owner%TYPE) IS SELECT * FROM all_tables WHERE owner = Powner; -- record variable definitions get_tables_rec get_tables%ROWTYPE; -- local variables local_owner all_tables.owner%TYPE := `BLICK'; -- unknown owner BEGIN get_tables_rec.table_name := `GARBAGE'; -- initialize to something OPEN get_tables(local_owner); -- compute rows to return FETCH get_tables INTO get_tables_rec; -- try to get a row IF (get_tables%NOTFOUND) THEN -- got a row DBMS_OUTPUT.put_line(get_tables_rec.table_name); END IF; CLOSE get_tables; -- done with this cursor END; /