Cursors in Sql

Question

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

Chintan Rathod · 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 Mishra · Answer

Thanks... Chintan

Satyapriya Nayak · Answer

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-drivenImplicitExplicit. 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