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.

updating multiple rows without cursor-8updating multiple rows without cursor-23updating multiple rows without cursor-84

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.

Comments