Cursors in Sql

Vikas Mishra

14y2.2kSQL

Hi Friends......

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? Why we use it?

Thanks..........

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Chintan Rathod14y ago
Accepted answer
Def : Cursors give you facility to select group of data and group of records called recordset.
Use : To save query's result for later use.
Created From : result set of a select query.
Why? : if repeated use of set of records required in your applcation, cursor gives facility to reuse it several times instead of repeating query every time.
Type :
1) Implicit Cursor : A SQL (implicit) cursor is opened by the database to process each SQL statement that is not associated with an explicit cursor.
2) Explicit Cursor : An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.
Advantages :
1) Cursors are best used when performing row-by-row operations.
2) Multiple time use of same query result.
Disadvantages :
1) A cursor is a memory resident set of pointers. It occupies memory from your system that may be available for other processes.
2) The number of rows and columns brought into the cursor affect cursor speed. Time how long it takes to open your cursor and fetch statements.
Vikas Mishra14y ago
Thanks... Chintan
Satyapriya Nayak14y ago
Hi Vikas,

Cursor is a database object used by applications to manipulate data in a set on a row by
row basis, instead of the typical SQL commands that operate on all the rows in the set at
one time.
In order to work with a cursor we need to perform some steps in the following order:
• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven,Implicit,Explicit.

1. Implicit cursor:- means predefine cursor. Its attributes
starting with the sign % like that %ROWCOUNT, %ISOPEN

2. Explicit cursor:- means created by the user or
programmer. Its attributes starting with the cursor name
then % sign lick that cursorname%ROWCOUNT, cursorname%ISOPEN

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors.

Thanks