When you declare and open an insensitive cursor, a worktable is created and fully populated with the cursor result set. Locks on the base table are released, and only the worktable is used for fetching.
To declare cursor CI as an insensitive cursor, enter:
declare CI insensitive scroll cursor for select emp_id, fname, lname from emp_tb where emp_id > 2002000 open CI
The scrolling worktable is now populated with the data shown in Table 19-4. To change the name “Sam” to “Joe,” enter:
..... update emp_tab set fname = "Joe" where fname = "Sam"
Now four “Sam” rows in the base table emp_tab disappear, replaced by four “Joe” rows.
fetch absolute 2 CI
The cursor reads the second row from the cursor result set, and returns Row 2, “2002020, Sam, Clarac.” Because the cursor is insensitive, the updated value is invisible to the cursor, and the value of the returned row—“Sam,” rather than “Joe”—is the same as the value of Row 2 in Table 19-4.
This next command inserts one more qualified row (that is, a row that meets the query condition in declare cursor) into table emp_tab, but the row membership is fixed in an cursor, so the added row is not visible to cursor CI. Enter:
insert into emp_tab values (2002101, "Sophie", "Chen", .., ..., ...)
The following fetch command scrolls the cursor to the end of the worktable, and reads the last row in the result set, returning the row value “2002100, Sam, West.” Again, because the cursor is insensitive, the new row inserted in emp_tab is not visible in cursor CI’s result set.
fetch last CI
