Semi-sensitive scrollable cursors are like insensitive cursors in that they use a worktable to hold the result set for scrolling purposes. But in semi_sensitive mode, the cursor’s worktable materializes as the rows are fetched, rather than when you open the cursor. The membership of the result set is fixed only after all the rows have been fetched once.
To declare cursor CSI semi-sensitive and scrollable, enter:
declare CSI semi_sensitive scroll cursor for select emp_id, fname, lname from emp_tab where emp_id > 2002000 open CSI
The initial rows of the result set contain the data shown in Table 19-4. Because the cursor is semi-sensitive, none of the rows are copied to the worktable when you open the cursor. To fetch the first record, enter:
fetch first CSI
The cursor reads the first row from emp_tab and returns 2002010, Mari, Cazalis. This row is copied to the worktable. Fetch the next row by entering:
fetch next CSI
The cursor reads the second row from emp_tab and returns 2002020, Sam, Clarac. This row is copied to the worktable. To replace the name “Sam” with the name “Joe,” enter:
...... update emp_tab set fname = "Joe" where fname = "Sam"
The four “Sam” rows in the base table emp_tab disappear, and four “Joe” rows appear instead. To fetch only the second row, enter:
fetch absolute 2 CSI
The cursor reads the second row from the result set and returns Employee ID 2002020, but the value of the returned row is “Sam,” not “Joe.” Because the cursor is semi-sensitive, this row was copied into the worktable before the row was updated, and the data change made by the update statement is invisible to the cursor, since the row returned comes from the result set scrolling worktable.
To fetch the fourth row, enter:
fetch absolute 4 CSI
The cursor reads the fourth row from the result set. Since Row 4, (2002040, Sam, Burke) was fetched after “Sam” was updated to “Joe,” the returned Employee ID 2002040 is Joe, Burke. The third and fourth rows are now copied to the worktable.
To add a new row, enter:
insert into emp_tab values (2002101, "Sophie", "Chen", .., ..., ...)
One more qualified row is added in the result set. This row is visible in the following fetch statement, because the cursor is semi-sensitive and because we have not yet fetched the last row. Fetch the updated version by entering:
fetch last CSI
The fetch statement reads 2002101, Sophie, Chen in the result set.
After using fetch with the last option, you have copied all the qualified rows of the cursor CSI to the worktable. Locking on the base table, emp_tab, is released, and the result set of cursor CSI is fixed. Any further data changes in emp_tab do not affect the result set of CSI.
Locking schema and transaction isolation level also
affect cursor visibility. The above example is based on the default
isolation level, level 1.
