Here we will learn about SQL Cursor. Cursor is a database object to retrieve data from a result set one row at a time. Cursor is also used when the data needs to be updated row by row.
Lets understand the life cycle of Cursor.
A cursor is declared by defining the SQL select statement that returns a result set. This operation initialize the memory for cursor.
A Cursor is opened by executing the SQL select statement defined by the cursor. This operation Allocates memory for cursor based on select statement used in cursor declaration
On this stage a single row is fetched from select statement and assigns value into given variables.
Once the row is fetched into declared variables, you can use your data manipulations or any operations on fetched data.
Loop till last row of select statement
Close the Cursor to release fetched rows
Release memory by deallocating cursor
Understanding life cycle with Syntax
Syntax to Declare a Cursor
DECLARE cursor_name CURSOR FOR select_statement;
To Declare a Cursor provide the Name of Cursor after
DECLARE keyword with
CURSOR as data type. For declaration of Cursor, we have to provide select statement which returns one or more columns. Select statement can be passed after
Syntax to Open Cursor
Opening the cursor will execute select statement which is provided with Declaration. This execution will allocate memory for the Cursor
Syntax to Fetch Data
Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. This statement returns a row cells into specified variables
FETCH NEXT FROM cursor INTO variable_list;
Fetch statement reads only a single row into variables. To read all the rows, we have to run while loop with Fetch Statement and get all the rows one by one.
WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cursor_name; END;
@@FETCHSTATUS returns 0, meaning the
FETCH statement was successful.
Syntax to Close Cursor
This statement closes the cursor explicitly
Syntax to Deallocate Cursor
DEALLOCATE statement delete the cursor and release all the resources. Deallocated Cursor can not be reopened.
In this example we will read the CustomerOrder table. We will read today’s orders and verify the information provided by customer.
DECLARE @OrderID int, @CustomerName varchar(Max),@OrderAmount decimal(18,2); DECLARE cursor_Orders CURSOR FOR SELECT OrderID,CustomerName,OrderAmount FROM CustomerOrders where cast(orderdate as date)= cast(getdate() as date);
After declaration lets read all the rows and do manipulation on every row.
FETCH NEXT FROM cursor_Orders INTO @OrderID, @CustomerName, @OrderAmount; WHILE @@FETCH_STATUS = 0 BEGIN -- Here we can do any operations on the fetched row data. FETCH NEXT FROM cursor_Orders INTO @OrderID, @CustomerName, @OrderAmount; END;
Once use of the cursor is completed, close it and deallocate it.
Close cursor_Orders ; DEALLOCATE cursor_Orders ;
This way we can use CURSOR to read every row and do manipulation on every single row’s data.
Write down your questions in comment section if you have any.