HOW TO USE CURSOR IN SQL?

Abhishek Srivasatava

Total Post:70

Points:350
Posted by  Abhishek Srivasatava
 399  View(s)
Ratings:
Rate this:
  1. Abhishek Srivasatava

    Post:70

    Points:350
    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.

      Modified On Oct-03-2016 03:49:17 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!