Explain the use of cursor. What are the steps involves in using cursors? Illustrate the steps with an example.
A cursor is used to access the result set stored in the memory on execution of a query. It is a special programming construct that allows data to be manipulated on a row-by-row basis. They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.
Steps:1. Declare a cursor
Example:Declare samplecursor cursor
For
Select * from employee
2. Open the cursor in order to use it
Example:Open samplecursor
3. Fetch first row from cursor and loop records until specifed criteria is met
Example: Declare @username varchar(100)
Declare @password varchar(100)
Fetch next from cursor into @username, @password
While @@fetch_status =0
Begin
Check if appropriate row was found and process
Else
Fetch next row
End
4. Close and deallocate the cursor
Example: Close samplecursor
Deallocate samplecurosr
Explain the properties of cursors in MySQL.
Asensitive – If the property is asensitive, the server may or may not make of the result table. This indicates that the data retrieved by this cursor is independent of any updates from other cursors.
Read only - These cursors cannot be updated.
Non-scrollable - These cursors can only be traversed in one direction and cannot skip rows.
What are the restrictions on Server-Side Cursors?
1. Serer side cursors generate results set on the server but transfer only those rows as requested by client.
2. Cursors are asensisitve.
3. Cursors are non holdable.
4. Cursors are not named.
5. One statement can open only one cursor.
6. Cursors are read only.
7. Cursors are non scrollable.
