Set Oriented Operations
Before understanding cursor, we should know that T-SQL performs set oriented operations upon table. It means that SQL operation is performed on all rows of the table considering rows as set. For example, if you use RAND function to insert random value in a column of table then all the rows of the column will have the same random value. This is because of set oriented operation upon the table. The set oriented operation upon table returns a set of rows known as result set. Look at the following query in this regard.
SELECT FIRST_NAME [First Name], RAND() [Random Value]
FROM Employees
The output is as follows.
To work with individual row of table and process data of a row at a time we use cursor.
What is cursor?
A cursor is a data structure which represents a set of rows together with cursor pointer that points to the current row of cursor. A cursor is server side result set. We can declare a variable of cursor data type using CURSOR keyword. For example, DECLARE c CURSOR statement declares c as cursor variable. This c variable can store a result set. Note that we neither use @ symbol with cursor variable unlike other T-SQL variables nor use AS keyword before CURSOR keyword.
When you declare a variable with its data type, memory is allocated for the variable according to the data type. It means that size of memory depends upon data type declared for the variable. Similarly, when we declare a variable of cursor type then specific size memory is allocated for variable of cursor data type.
Now we look at different types of cursors which are used in T-SQL. We use some keywords e.g. local and global etc to qualify a type of cursor.
Qualifiers after CURSOR keyword
Now look at the following code.
DECLARE c CURSOR LOCAL
After CURSOR we have used LOCAL keyword which implies that cursor is local. Local cursor will be available only in the current batch, stored procedure or trigger in which it is declared. To avail a cursor in any statement of the current connection, we use GLOBAL keyword. This will create global cursor. Local or global keyword with cursor is used to define the scope of cursor in the program.
A cursor can be local and static at the same time. So, multiple keywords are used after CURSOR keyword to define all the attributes of the cursor. For example,
DECLARE c CURSOR LOCAL STATIC
We use different keywords such as static, dynamic, read only, forward only, optimistic etc to qualify a cursor and denote its type. Compound keyword will be joined by underscore character. These keywords describe the nature of result set in cursor and how the result set will be handled. Local cursor variable will be available only in the current the batch, stored procedure or trigger in which it is created. Global Cursor will be available to any statement on the current connection. Static cursor is not updated after opening the cursor. Read only cursor cannot be updated. Cursor can be forward only or optimistic.
Populate Cursor
Till now we have learnt how we can declare a cursor and how we can specify the nature of the cursor but the cursor is still not initialized. Now we will learn how we can initialize a cursor.
Use FOR keyword followed by SELECT query to get result set into the cursor. This will populate cursor. Look at the following.
DECLARE c CURSOR LOCAL STATIC
FOR SELECT * FROM Employees
OPEN c
PRINT @@CURSOR_ROWS
The above code implies that cursor c is populated with SELECT query result set.
Now when the data is populated into the cursor variable, next task is to process the cursor data. Remember that cursor is used to process one row at a time. We use cursor pointer to point to a row in cursor which will be processed. The cursor pointer moves to a row of the cursor to process that row.
To process cursor data we must, first of all, open the cursor. To open the cursor, we use open statement. Open keyword is followed by cursor name. When cursor is opened, the cursor pointer is set to the top of the row of the cursor. Top of the cursor can be imagined as table header row.
Now the question is how to move the cursor pointer to a row to process the row data.
If the cursor is forward only then cursor pointer can move only in forward direction. To fetch next row, we use FETCH statement. This statement uses Next, Prior, First, or Last keyword after FETCH keyword and ends with FROM keyword. The Fetch Next will point the cursor pointer to next row and will fetch the next row to process its data. We must specify the cursor name after FETCH NEXT FROM clause. Remember that multiple cursor variables can be declared in a batch or stored procedure. So, cursor name must be specified in FETCH statement, else error will occur.
DECLARE c CURSOR LOCAL STATIC
FOR SELECT * FROM Employees
OPEN c
FETCH NEXT FROM c
The above script will return first row of the Employee table. Instead of FETCH NEXT FROM c, try FETCH FIRST FROM c and FETCH LAST FROM c clause in the script.
You can fetch a row based on absolute or relative position of the cursor pointer. Use an integer number, positive or negative, to fetch a row. Look at the following example.
DECLARE c CURSOR LOCAL STATIC
FOR SELECT * FROM Employees
OPEN c
FETCH ABSOLUTE 4 FROM c
The above script will return first row of the Employee table. Instead of FETCH ABSOLUTE 4 FROM c, try FETCH ABSOLUTE -3 FROM c and FETCH RELATIVE 3 FROM c clause in the script.
FETCH NEXT FROM clause fetches a row FROM the cursor into the cursor pointer. Use INTO clause to initialize the declared variables with the data of the cursor pointer. Look at the following example in this regard.
DECLARE @firstname as nvarchar(30)
DECLARE @lastname as nvarchar(30)
DECLARE c CURSOR LOCAL STATIC
FOR SELECT first_name, last_name FROM Employees
OPEN c
FETCH NEXT FROM c
INTO @firstname, @lastname
PRINT @firstname + ' ' + @lastname
Note that all the columns of record set of cursor must be given in INTO clause plus their data type must match. So, in the above example, we have only used first name and last name of Employee table to get result set with two columns only.
You can use FETCH_STATUS system global variable to get the status of current fetch into cursor pointer. FETCH_STATUS will be 0 has long as fetch operation is successful i.e. data is brought into the cursor pointer from cursor. We can use this variable to loop through result set. Look at the following example in this regard.
DECLARE @firstname as nvarchar(30)
DECLARE @lastname as nvarchar(30)
DECLARE c CURSOR STATIC STATIC
FOR SELECT first_name, last_name FROM Employees
OPEN c
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM c
INTO @firstname, @lastname
PRINT @firstname + ' ' + @lastname
END
CLOSE c
DEALLOCATE c
Close statement is used to empty cursor pointer. Thereafter cursor data cannot be brought into cursor pointer. This does not destroy the cursor itself just the pointer is gone.
To destroy the cursor variable, deallocate statement is used which will free up the memory allocated for the cursor.
Now we solve the problem of generating random numbers using cursor.
DECLARE @firstname as nvarchar(30)
DECLARE @lastname as nvarchar(30)
DECLARE c CURSOR STATIC STATIC
FOR SELECT first_name, last_name FROM Employees
OPEN c
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM c
INTO @firstname, @lastname
PRINT @firstname + ' ' + @lastname + ' > '+ cast( rand() as nvarchar)
END
CLOSE c
DEALLOCATE c
Now we sum up what we have learnt till now.
- First of all, use DECLARE statement to declare CURSOR type variable.
- This variable is initialized using a SELECT query.
- Thereafter, cursor pointer is created by using OPEN statement.
- Fetch statement is used, thereafter, to bring a data of a row of cursor variable into the cursor pointer.
- Use FETCH_STATUS system global variable to check whether data is available in the cursor pointer or not. Use WHILE loop to loop rows of the cursor data.
- Use CLOSE statement to destroy the cursor pointer.
- Use DEALLOCATE statement to destroy the cursor variable and free up the memory allocated to the cursor.
Practical Example
DECLARE @TableName NVARCHAR(100)
DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo';
OPEN tableCursor;
FETCH NEXT FROM tableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = 'DROP TABLE ' + QUOTENAME(@TableName);
EXEC sp_executesql @DynamicSQL;
FETCH NEXT FROM tableCursor INTO @TableName;
END
CLOSE tableCursor;
DEALLOCATE tableCursor;
No comments:
Post a Comment