Implementing Concept of Cursor in SQL Server
A Cursor is a database object that represents a result set and is used to manipulate data row by row. When a cursor is opened, it is positioned on a row and that row is available for processing.
Some system stored procedure related with cursor
1) sp_cursor_listà returns a list of cursor currently visible on the connection and their attribute
2) sp_describe_cursoràDescribe the attributes of a cursor such as whether it is forward only or scrolling cursor.
3) sp_describe_cursor_columnsàDescribe the attributes of the columns in the cursor result set.
4) sp_describe_cursor_tablesàDescribe the base tables accessed by the cursor.
Steps for implementing a simple cursor in your program
1) A programmer makes a cursor known as DBMS using declare……cursor statement and assigning the cursor a (compulsory) name.
Syntax for declaring cursor
declare cursor_name cursor for select .... from ...
declareis a keyword used for declare a new cursor
cursor_name is the name of the cursor that you want to use
foris a clause after that you have to told the select statement
2) After declaring the cursor we can access the data. Before accessing the data of the cursor it must be opened by using open statement. Directly following a successful opening, the cursor is positioned before the first row in the result set.
Syntax for opening cursor
3) After opening cursor on a specific row in the result set with the fetch statement. A fetch operation transfers the data of the row into the application. Simply we can say by using fetch statement we read one by one row at a time in forward only by default.
Syntax for fetching cursor
4) Once an application has processed all the rows of a table or the fetch operation is to be positioned on a non-existing row. The final step involves closing the cursor using the close statement.
Example which demonstrate the use of cursor
declare stu_cursor cursor for select * from Student -- A cursor is declared
open stu_cursor -- after this steps cursor is opened.
fetch stu_cursor --Read one row at a time and display the value of row.Again and again executing this statement will read next rows.
close stu_cursor --close the open cursor
Programmers may declare cursor as scrollable or not scrollable. The scrollablity indicates the direction in which a cursor can move. With a non scrollable cursor, also known as forward only can fetch each row at most one.
A programmer may position a scrollable cursor anywhere in the result set using the fetch SQL statement. The keyword scroll must be specified when declaring the cursor. The default is non-scroll cursor.
Syntax for declaring scrollable cursor
declare cursor_name senstivity scroll cursor for select .... from ...
The target position for a scrollable cursor can be specified relative to the current cursor positions or absolute from beginning of the result sets.
Syntax for fetching records in cursor
fetch [next/prior/first/last] from cursor_name
fetch absolute n from cursor_name
fetch relative n from cursor_name
Scrollable cursor can potentially access the same row in the result set multiple times. Thus, data modification (insert, update, delete) operations from other transaction could have an impact on the result set. A cursor can be sensitive or insensitive to such modification.
Example which demonstrate the use of Scrollable cursor
declare cur_std scroll cursor for select * from student --Declare a cursor named cur_std
open cur_std -- Open the cursor
fetch next from cur_std -- Fetch next records in the cursor and display it
fetch absolute 3 from cur_std --Fetch 3 records in the cursor
fetch prior from cur_std --Fetch previous record in the cursor
Note: For efficiency point of view it is advisable not to use the concept of the cursor until you exactly needs for processing single row at a time.