Page 216
Listing 10.12 contine.sqlSimulating 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.sqlCapturing 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.sqlUsing 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.
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
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.