How to use cursor in SQL?

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

Example

 DECLARE EESAL CURSOR
STATIC FOR 
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 :

  FETCH NEXT FROM 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 2 : inserting the record:

INSERT INTO EE_SALARY (PRSN_INTN_ID, [EMPL_STAT_CD],SALARY)
    VALUES (1199667, 'ACTIVE',159978)

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
STATIC FOR 
SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM
EE_SALARY WHERE
    EMPL_STAT_CD='ACTIVE'
 
OPEN EESAL
IF @@cursor_rows >
0
BEGIN
      FETCH NEXT FROM 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
 
FETCH NEXT FROM 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.

interview

How to use cursor in SQL?

Can you answer this question?

1 Answers