MySQL cursor

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.
MySQL triggers
MySQL triggers - What are triggers? What are they used for?, Creating triggers and Dropping Triggers, What are the types of triggers?, How are triggers created in MySQL?, What are the levels of triggers supported by MySQL?
MySQL transactions
MySQL transactions - COMMIT and ROLLBACK statements to manage transaction processing, purpose of SAVEPOINTS
MySQL performance
MySQL performance - steps to optimize and improve performance
Post your comment

You may like