Whenever we are processing sql statement SQL allots PRIVATE SQL AREA OR WORK AREA to store processed 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 behaviour to use loops in SQL but sometimes you have to use them.
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 advantage is that it is we can do Row-wise validation or in other way you can perform operation on each Row.
It is a Data Type which is used to define multi value 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 I/O or CPU,
look at the cursor declare statement and ensure you have optimal indexes in place on your base tables or temporary tables.
Cheers
Rajani
No comments:
Post a Comment