Explain the cursor types.

DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn’t reflects changes happened on the table while scrolling through the row. 
KEYSET: In this kind, new record is not reflected, but data modification can be seen.

Define the cursor lock types.

Three types of locks

READ ONLY: This prevents any updates on the table. 
SCROLL LOCK: This allows you to make changes to the table.
OPTIMISTIC: This checks if any change in the table record since the row fetched before updating. 
If there is no change, the cursor can update.

Explain in brief the cursor optimization tips.

Close cursor when it is not required.
You shouldn’t forget to deallocate cursor after closing it.
You should fetch least number of records.
You should use FORWARD ONLY option when there is no need to update rows.

Explain the disadvantages/limitation of the cursor.

Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
While data processing, it issues locks on part of the table, or on the whole table.

Define scrollable cursor.

You can use keyword SCROLL to make cursor Scrollable.
It can scroll to any row and can access the same row in the result set multiple times.
A non-scrollable cursor is also known as forward-only and each row can be fetched at most once.