Previous | Table of Contents | Next

Page 216

Listing 10.12 contine.sql—Simulating CONTINUE with GOTO

DECLARE
  j NUMBER := 0;
BEGIN
  DBMS_OUTPUT.enable;
  LOOP       -- print even numbers between 0 and 20
    IF (MOD(j, 2) = 1) THEN  -- skip odd numbers
      GOTO CONTINE;  -- the misspelling is an inside joke
    END IF;  -- skipping odd numbers
    DBMS_OUTPUT.put_line(TO_CHAR(j) || ` is even');
    <<CONTINE>>
    EXIT WHEN j = 20;  -- done
    j := j + 1;  -- don't forget to increment the loop counter!
  END LOOP;  -- print even numbers between 0 and 20
END;
/

The server responds with:

0 is even
2 is even
4 is even
6 is even
8 is even
10 is even
12 is even
14 is even
16 is even
18 is even
20 is even
PL/SQL procedure successfully completed.

Using GOTO in this manner simplifies the logic within a loop.

Iterating with FOR LoopsAnother kind of loop performs a specific number of iterations.

BEGIN
  FOR i IN 1..100 LOOP  -- do nothing for exactly 100 iterations
    NULL;
  END LOOP;  -- done doing nothing
END;

Note the syntax of the FOR loop. The IN clause must specify a range. Now in this case, you do not need to declare the loop control variable (i in this example) because it is created automatically within the scope of the FOR loop. This means you cannot reference it outside the scope of the loop, because it ceases to exist when the loop completes. If you need the value of the loop control variable for something, you must copy it into another variable (see Listing 10.13).

Listing 10.13 lastodd.sql—Capturing the Value of a FOR Loop Control Variable


DECLARE
  j NUMBER;

Page 217

BEGIN
  DBMS_OUTPUT.enable;  -- enable output
  FOR i IN 1..100 LOOP  -- do nothing for exactly 100 iterations
    IF (MOD(i, 2) = 1) THEN  -- must be odd
     j := i;
    END IF;  -- capture odd numbers
  END LOOP;  -- done doing nothing
  DBMS_OUTPUT.put_line(`last odd number was ` || TO_CHAR(j));
END;
/

The server displays:

last odd number was 99

PL/SQL procedure successfully completed.

There is no support for stepping by any value other than one. But you can simulate it! There are a couple of ways; one might be to multiply the loop counter by the step to get the values you want to work with. You can't actually modify the loop control variable because that would be illegal (it's strictly read-only). Another method would be to create another variable and increment it by the desired value; this probably makes more sense with a simple loop, but it's still perfectly valid inside a FOR loop. You might have to adjust the loop range to get the behavior you want. And, of course, you can always use EXIT or EXIT WHEN to prematurely break out of a loop.

You can also do the iteration in reverse, in countdown-wise fashion (see Listing 10.14).

Listing 10.14 countdwn.sql—Using REVERSE in a FOR Loop

BEGIN
  FOR j IN REVERSE 1..10 LOOP  -- countdown
    DBMS_OUTPUT.put(TO_CHAR(j) || `-');
  END LOOP;  -- countdown
  DBMS_OUTPUT.put_line(`Blastoff!');
END;
/

And you get:

10-9-8-7-6-5-4-3-2-1-Blastoff!
PL/SQL procedure successfully completed.

Using Cursors

A cursor is an object that provides row-level control of an SQL statement. The cursor declaration is not that of a variable, but rather a handle to an area of memory used to implement the cursor. The cursor declaration only defines what query will be submitted to the SQL Statement Executor; the management of the query occurs under programmatic control in executable code. The cursor can represent any valid SQL SELECT statement. Cursors are generally the basic building blocks of any PL/SQL application. They provide the looping mechanism for

Page 218

operating on sets of data stored in the database. If you also need to do updates, use the FOR UPDATE clause.

CAUTION
Be aware that using the FOR UPDATE clause will lock all the rows found by the query. All these rows will remain locked until the cursor is closed.

A cursor may return one or more rows, or none at all. The general sequence of operations is

  1. Declare the cursor, as well as a data structure into which to retrieve rows
  2. Open the cursor
  3. Fetch repeatedly from the cursor into the data structure, until the data set is exhausted
  4. Close the cursor

Defining a CursorIn defining a cursor, you will notice a few variations on a theme:

DECLARE
  -- gets all orders in database
  CURSOR get_orders IS
    SELECT * FROM orders;
  -- gets a few columns for a specified order number
  CURSOR get_orditem(Pord_num orders.ord_num%TYPE) IS
    SELECT seq_num, quantity, unit_price, extended_price
    FROM orders
    WHERE ord_num = Pord_num;
  -- gets the whole row for a particular item#
  CURSOR get_items(Pitem_no items.item_no%TYPE) IS
  SELECT * FROM items WHERE item = Pitem
  RETURN items%ROWTYPE;
  -- gets the item name for a particular item#
  CURSOR get_item_name(Pitem_no items.item_no%TYPE)
  RETURN items.item_name%TYPE IS
  SELECT item_name FROM items WHERE item_no = Pitem_no;

Even though there are quite a few variants, there are just two basic patterns. A cursor can take parameters, or not. You supply the values for the parameters when you open the cursor. You can define the return type, or not. The return type can be a user-defined record, a database table row type, or individual variables. Whichever way, the columns defined in the SELECT clause must match one for one whatever you use to receive the return values.

Once declared, the cursor can be opened, rows fetched, its state examined, and closed when no longer needed.

A cursor can have any name; in these examples the convention is to suffix the cursor's name with _cur(sor) or _loop, or prefix it with get_ (depending on my mood). Use the name of the table in the cursor name, or otherwise try to make it meaningful (without being long-winded), as in get_addresses or employee_by_dept_cur.

Be aware that the parameter list can only input values, never output them. That is why the parameter flow is not needed nor allowed. The input parameters must also be scalar values.

Previous | Table of Contents | Next