1. Declaration of Cursor:- This section is used to declare the cursor object
2. Sql Statement for fetching the records:- This section is used to define the SQL query used for fetching the record set
3. Open Cursor:- This section is used to Open the Cursor. Also this statement allocates the memory to the cursor to hold the record set return by the execution of the Sql Statement.
4. Fetch statement:- This section is used to fetch a single row from the record set get by the execution of the Sql Query and assigning the values to the respective variable.
5. @@Fetch_status:- This is the System variable and it is used to know that whether the Fetch statement is returning rows or not. This system variable returns the value of 0 if the Fetch statement is successful, -1 if the Fetch statement is failed and -2 if the row fetched is missing. While loop is used compare the value of the @@Fetch_status with the 0.
6. Begin......End:- This section is used to write the Sql code used inside the Cursor to manipulates the data of the rows fetch by the Fetch Statement
7. Close Cursor:- This statement is used to close the cursor.
8. Deallocate Cursor:- This section is used to deallocated the memory used by the cursor.

Syntax for defining the Cursor:-


  Declare @Cursor_name cursor For
    Sql query                                                        // Select statement to Fetch rows
 Open @Cursor_name
  Fetch next from @Cursor_name  into {@Variable1,@variable2 etc)
  While (@@fetch_status=0)
   Begin


      // Sql Block  sql statements to process the data of the row return by the cursor.


 Fetch next from @Cursor_name  into {@Variable1,@variable2 etc)
 End
 Close @Cursor_name
 Deallocate @Cursor_name


Example:-
Suppose we have two tables named customer and customerTransaction whose structure is given below:-

Create Procedure Updatecustamount
  As
Begin
 Declare @custid as Int
 Declare @amount as Int
 Declare @addamt as Int
 Declare cur_cust Cursor For
   Select customerid,amount
   From customer

  FETCH Next From cur_cust Into @custid,@amount
 While(@@fetch_status=0)
  Begin
    Set @addamt=(20*@amount)/100
    Update customer set amount=amount+@addamt where customerid=@custid
 
    Insert into  customerTransaction(custid,Transactionamt,mode,trandate )
                   Values(@custid,@addamt,'c',Getdate())
   Fetch Next From cur_cust Into @custid,@amount
  End
CLOSE cur_cust
DEALLOCATE cur_cust
End


Summary:-
We can say that cursor is a very useful database object if we needs to manipulates the rows on a row by row basic. But there are two camps, one which oppose the use of cursor since it sometimes slow down the performance by occupying a lot of memory to hold the rows return by the query and the other camp which advocates the use of cursor since it gives the readability and simplicity to our code. We can replace the cursor with the help of the While loop or sometimes with the help of the Select case statements.One can also used the cursor if the need is to execute the Sql statements in a serialized manner.Also we can use the cursor for doing many Administrative tasks.