What is Cursor?Purpose of Cursor?Advantage and Disadvantage of Cursor.

Whenever we are processing sql statement SQL allots PRIVATE SQL AREA OR WORK AREA to store porcessed information. Cursor is a pointer to this private sql area.
Oracle automatically creates an implicit cursor for each sql statement.
If you want to handle and control processing of each row of data explicitly you may define explicit cursor.

A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis.

For Example:If you need to insert/update/delete bunch of data one by one, you have to use a cursor with a while loop. It’s not a good behavior to use loops in SQL but sometimes you have to use them. Anyway if you don’t know how to define and use a cursor, you may take a look at the code snippet below. Or maybe you may always forget how to define one like me ;o))

Steps for cursor

1. DECLARE CURSOR

2. OPEN

3. FETCH

4. @@FETCH_STATUS

5. CLOSE

6.DEALLOCATE CURSORS

DisAdvantage of Cursors

A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes. Poorly written cursors can completely deplete available memory. Means it consumes more Resources

Advantage of Cursors

Cursors can be faster than a while loop but they do have more overhead. Another advatage is that it is we can do RowWise validation or in other way you can perform operation on each Row.It is a Data Type which is used to define multivalue variable.

But one factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements. If it's lengthy, look carefully at your cursor logic; see if you can remove columns from the declare statement, and change your where clause in the declare statement to only return rows the cursor needs. If the fetch statements themselves are lengthy or consuming too much IO or CPU, look at the cursor declare statement and ensure you have optimal indexes in place on your base tables or temporary tables.