dating in ithaca - Updating multiple rows without cursor
I worry about how ETL tools apply updates (did you know Data Stage applys updates singly, but batches inserts in arrays? The two most common forms of Bulk Updates are: Case 1 is uninteresting.
I spend an inordinate proportion of design time of an ETL system worrying about the relative proportion of rows inserted vs updated.
That allows us to perform operations on every record on a one-by-one basis.
The DECLARE statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor.
Listing A shows an example from Oracle's HR sample schema: There is more than one employee with the last name King, so the script fails.
DECLARE l_employee_last_name employees.last_name%TYPE; l_employee_full_name VARCHAR2(52); BEGIN l_employee_last_name := 'King'; SELECT last_name || ', ' || first_name INTO l_employee_full_name FROM employees WHERE last_name = l_employee_last_name; DBMS_OUTPUT.
The interesting thing about this method is that it performs a context-switch between PL/SQL and SQL for every FETCH; this is less efficient.