Oracle PL/SQL Programming: A Developer's Workbook

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 ...