O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Expert

Q:

10-18.

The call to DBMS_OUTPUT.PUT_LINE shows the following:

FALSE - 32

for each row fetched (a total of 32 lines). How can this be? Here’s a somewhat strange situation. You execute an implicit cursor (UPDATE), and then after that you open and process another cursor—the SELECT embedded in the cursor FOR loop.

Even though you have not declared a cursor by name for this SELECT, it is still not an implicit cursor; it is simply unnamed. Any reference, therefore, to the SQL% cursor attributes reflects the results of the UPDATE statement, not the query.

Q:

10-19.

This block displays a single line of output:

TRUE

You are now using a declared, explicit cursor, so you can reference that cursor with these attributes. This block displays output only when you have fetched the first row, and the cursor is open (which is always the case inside the cursor FOR loop).

Q:

10-20.

When you use the FOR UPDATE clause in a query, you can then specify:

WHERE CURRENT OF
cursor

as the WHERE clause of DML statements inside the FOR loop. The runtime engine automagically updates/inserts/deletes the current row in the cursor’s result set. You can also add a WHERE clause to minimize data queried back to the PL/SQL block and then rejected with the IF statement. Here is the rewrite:

DECLARE
   CURSOR upd_all_cur
      IS SELECT * FROM employee
          WHERE commission IS NOT NULL
         FOR UPDATE;
BEGIN
   FOR rec IN upd_all_cur
   LOOP
      UPDATE employee
         SET commission = commission * 2
       WHERE CURRENT OF upd_all_cur;
   END LOOP;
END;

You should also see ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required