The scrollability indicates the direction in which a cursor can move.
With a non-scrollable (or forward-only) cursor, you can each row at most once, and the cursor automatically moves to the next row.
To get started let's do the following: Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, co-founder and Baltimore SSUG co-leader.
In computer science, a database cursor is a control structure that enables traversal over the records in a database.
When your application requires consistent data for the duration of the transaction, not reflecting changes by other transactions, you can achieve transaction-level read consistency by using explicit locking, read-only transactions, serializable transactions, or by overriding default locking.
Row locking at transactional level can be achieved with “SELECT FOR UPDATE” statement.
Regardless of your stand on cursors they probably have a place in particular circumstances and not in others.
So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not.
The following example show you how to lock single rows: IS /*"order_row" is a variable to store the row found by the cursor select statement */ order_row order_queue%rowtype; cursor c1 is SELECT * FROM order_queue WHERE id=id_number FOR UPDATE NOWAIT; BEGIN /* first of all you need to open the cursor */ OPEN c1; /* then you need to fetch the content in the variable */ LOOP FETCH c1 INTO order_row; /* the lock will be released after the commit */ IF (c1%found) THEN UPDATE order_queue SET processed=1, user=system_user where CURRENT OF c1; COMMIT; END IF; /* then you need close the cursor */ END LOOP; CLOSE c1; END LOCK_ORDER_ENTRY; The “select for update” statement has two possible variants – FOR UPDATE NO WAIT, that triggers an error if the row is locked by another user.
– FOR UPDATE SKIP LOCKED, that fastens the execution by skipping the already locked rows If you need to lock a single row in the database you don´t need a loop.
In this scenario, a cursor enables the rows in a result set to be processed sequentially.
In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis.
Cursors are usually closed automatically at the end of a transaction, i.e.