Previous | Table of Contents | Next

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.sql—Simple 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.sql—A 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.sql—Simple 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.sql—Controlling 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.sql—Explicitly 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.sql—FETCH 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;
/

Previous | Table of Contents | Next