Cursor can be defined as the table’s object which is used to retrieve the data from the result set one row at a time. We use it when there is a need to update records in a table row by row.
Declare cursor:
It is declared by defining the SQL statement that returns a result set.
Example
DECLARE EESAL CURSOR STATICFOR SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM EE_SALARY WHERE EMPL_STAT_CD='ACTIVE'
Open:
It is opened and populated by executing the SQL statement defined by the cursor.
Syntax:
OPEN cursor_name;
Fetch:
When it is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Example :
FETCHNEXTFROM EESAL INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARY
Close:
It is used to close by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned
Syntax: CLOSE cursor_name;
De-allocate:
It is used to removes a cursor reference and released all recourses.
Syntax : DEALLOCATE cursor_name.
How to use Cursor in SQL for mass update?
Let’s take an example to update the salary for all ACTIVE employee of a company by 1000Rs.
Step 1: Creating a table:
CREATE TABLE EE_SALARY ( PRSN_INTN_ID int NOT NULL PRIMARY KEY , EMPL_STAT_CD char(20) NOT NULL , SALARY int NOT NULL, );
Step 3 :Here, I am explaining about the cursor program with help of small example:
DECLARE @PRSN_INTN_ID int, @EMPL_STAT_CD VARCHAR(30),@SALARY INT DECLARE EESAL CURSOR STATICFOR SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM EE_SALARY WHERE EMPL_STAT_CD='ACTIVE' OPEN EESAL IF@@cursor_rows> 0 BEGIN FETCHNEXTFROM EESAL INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARY WHILE@@fetch_status= 0 BEGIN PRINT'PRSN_INTN_ID : '+convert(varchar(20),@PRSN_INTN_ID)+', EMPL_STAT_CD:'+@EMPL_STAT_CD +',SALARY : '+convert(varchar(20),@SALARY) UPDATE EE_SALARY SET SALARY=SALARY+1000 WHERE PRSN_INTN_ID=@PRSN_INTN_ID FETCHNEXTFROM EESAL INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARY END END CLOSE EESAL DEALLOCATE EESAL
We are using cursor to update the record row by row which is very useful when we
connect SQL to console, form application or web application.
Liked By
Write Answer
How to use cursor in SQL?
Join MindStick Community
You have need login or register for voting of answers or question.
Abhishek Srivasatava
03-Oct-2016Cursor in SQL
Cursor can be defined as the table’s object which is used to retrieve the data from the result set one row at a time. We use it when there is a need to update records in a table row by row.
Declare cursor:
It is declared by defining the SQL statement that returns a result set.
Open:
It is opened and populated by executing the SQL statement defined by the cursor.
Fetch:
When it is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Example :
Close:
It is used to close by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned
De-allocate:
It is used to removes a cursor reference and released all recourses.
How to use Cursor in SQL for mass update?
Let’s take an example to update the salary for all ACTIVE employee of a company by 1000Rs.
Step 1: Creating a table:
Step 2 : inserting the record:
Step 3 :Here, I am explaining about the cursor program with help of small example:
We are using cursor to update the record row by row which is very useful when we
connect SQL to console, form application or web application.