Managing Result Sets by Using Cursors and Transact -SQL
Cursors are used to take the results of a SELECT statement and assign the output from the recordset to a set of variables , one at a time. This enables you to walk through the recordset one record at a time and use the information in the recordset to do interesting things. Creating a cursor has five steps. First, you have to DECLARE the cursor with the DECLARE CURSOR statement. Next , open the cursor with the OPEN statement. After that, you have to FETCH rows from the cursor, and when you're done, you have to CLOSE the cursor and DEALLOCATE it. Here's an example: DECLARE @Name sysname DECLARE SysObj cursor for SELECT name FROM sysobjects OPEN SysObj FETCH NEXT FROM SysObj INTO @Name WHILE @@FETCH_STATUS = 0 BEGIN PRINT @Name FETCH NEXT FROM SysObj INTO @Name END CLOSE SysObj DEALLOCATE sysobj The first line declares a variable called @Name of type sysname . The sysname data type is a special nvarchar data type that is used to hold the names of different system objects. If you're putting system names into a variable, it's the correct type to use because if the length of names changes from this version of SQL Server to the next, your code will still work. The DECLARE CURSOR line declares what the cursor is going to do. In this case, the cursor is going to return one value from the Name column in sysobjects . You can return multiple fields, and you can filter with a WHERE clause. You can do anything in the SELECT statement that you can do in any other SELECT statement, including joins. The OPEN line opens the cursor for business. Until that OPEN is executed, the cursor is just an idea; the OPEN actually makes the cursor usable by allocating resources for it. The FETCH NEXT fetches the next row from the cursor. Because you haven't fetched any rows from the cursor yet, it fetches the first one. It takes the value returned and places it into the @Name variable. Note that the returned data and the variable have to be the same type, or if they are two different types, they have to convert implicitly. If the cursor specified multiple return values, the additional variables would just be tacked onto the end with commas to separate the variable names. FETCH NEXT automatically sets the global variable @@FETCH_STATUS to if the fetch was successful, and to other values (refer to Table 6.1) for other results. Next up is the WHILE loop. Looking at it from a loop point of view, the preceding FETCH NEXT is the initialization for the loop; the @@FETCH_STATUS = 0 is the test; and the FETCH NEXT that's inside the loop is the iteration. The PRINT statement prints out the value. Normally, you'd want to do a bit more with the value than just print it; you'll see other examples of things to do later in this section. This is just a warmup. After printing, there's another FETCH . Why are there two fetches? Well, the first one initializes the @@FETCH_STATUS variable, so the loop will execute, and this one iterates the loop by changing the @@FETCH_STATUS when the loop is done. This is a technique called a priming read , and it's frequently used to solve the problem of initializing loops . If you didn't do a priming read, you may have some leftover value in there from a previous cursor. Even if you don't have any other cursors, however, you have no guarantee that the value of @@FETCH_STATUS is valid until you set it with a FETCH . After the FETCH is the end of the loop, which then returns up to the test. Although @@FETCH_STATUS doesn't change every time, it changes when the end of the cursor is reached, so the loop doesn't go on forever. The CLOSE and DEALLOCATE are what you use to tell SQL Server that you're finished with the cursor. This closes the cursor, which releases any locks you have, and deallocates the cursor, freeing the memory resources used by the cursor. Now, this was just a basic example. Here's a more real example of what cursors can do: DECLARE @CommaList varchar(8000), @Name sysname SET @CommaList = '' DECLARE SysObj CURSOR FOR SELECT name FROM sysobjects OPEN SysObj FETCH NEXT FROM SysObj INTO @Name WHILE @@FETCH_STATUS = 0 BEGIN SET @CommaList = @CommaList + quotename(@Name) + ', ' FETCH NEXT FROM SysObj INTO @Name END CLOSE SysObj DEALLOCATE sysobj SET @CommaList = SUBSTRING(@CommaList, 1, datalength(@CommaList) - 2) PRINT @CommaList This creates a comma-delimited list out of your SELECT statement. This is a useful thing to do if you are trying to piece together an IN clause. What else can cursors do? You can update data through a cursor. First, you need to create a table to play with: CREATE TABLE foo ( ID int, value varchar(30) ) INSERT INTO foo VALUES (1, 'Fred') INSERT INTO foo VALUES (2, 'Barney') INSERT INTO foo VALUES (3, 'Wilma') INSERT INTO foo VALUES (4, 'Betty') Using that as a basis, there are a few options that you can use in the DECLARE CURSOR statement. First of all, the cursor you've been using so far is the plain, default cursor. It puts a shared lock on rows in the cursor for the duration of the cursor, so they cannot be modified while the cursor is reading them. That's why it is important to close cursors when you are finished with them; otherwise you are holding locks you probably don't need to. To avoid the locking problem, tell SQL Server to make a copy of the data and run the cursor from the copy by using the INSENSITIVE keyword. The cursor takes longer to open because SQL Server actually copies all the data to run the cursor into a temporary table. Another thing you can do with cursors is scroll back and forth through them. This is done by using the SCROLL keyword. Here's an example of both the INSENSITIVE and SCROLL keywords: DECLARE @id int, @Value varchar(30) DECLARE Flintstone INSENSITIVE SCROLL CURSOR FOR SELECT id, value FROM foo ORDER BY 1 OPEN Flintstone FETCH FIRST FROM Flintstone INTO @ID, @Value SELECT @ID ID, @Value Value FETCH LAST FROM Flintstone INTO @ID, @Value SELECT @ID ID, @Value Value FETCH PRIOR FROM Flintstone INTO @ID, @Value SELECT @ID ID, @Value Value FETCH RELATIVE -2 FROM Flintstone INTO @ID, @Value SELECT @ID ID, @Value Value FETCH ABSOLUTE 3 FROM Flintstone INTO @ID, @Value SELECT @ID ID, @Value Value FETCH NEXT FROM Flintstone INTO @ID, @Value SELECT @ID ID, @Value Value CLOSE Flintstone DEALLOCATE flintstone This demonstrates some of the features of scrollable cursors. The first FETCH returns the first row, which is Fred . Then the last row, Betty , is returned. Then the row before the previously fetched row ( Wilma ) is returned. Then the row two rows before the Wilma row is returned, which is back to Fred . Then the third row in the cursor is fetched, which is Wilma again, then the row after that one is returned, which is Betty . Those are the options for scrolling and fetching. Now see what things look like for inserts and updates through a cursor. By default, a cursor is updateable. To prevent updates to a cursor, use the FOR READ ONLY clause, which goes after the SELECT statement in the DECLARE cursor, like this: DECLARE Flintstone SCROLL CURSOR FOR SELECT id, value FROM foo ORDER BY 1 FOR READ ONLY To update through a cursor, you need to tell SQL Server that you're going to update the cursor using the FOR UPDATE clause, which goes in the same place as the FOR READ ONLY in the preceding code sample. To actually update the data, a special form of the UPDATE statement is used, UPDATE WHERE CURRENT OF . It works like this: DECLARE @id int, @Value varchar(30) DECLARE Flintstone cursor for SELECT id, value FROM foo for UPDATE OPEN Flintstone FETCH NEXT FROM Flintstone INTO @ID, @Value UPDATE foo SET Value = 'Fredrick' WHERE CURRENT OF Flintstone CLOSE Flintstone DEALLOCATE flintstone Several rules have to be followed to update through a cursor. First, the cursor cannot be read-only. That's fairly obvious, but it implies that the cursor does not have the INSENSITIVE or SCROLL options turned on, in addition to having the READ ONLY option turned on. Many other options (which are discussed later) cause a cursor to be read-only. The FOR UPDATE in the cursor declaration is optional, but suggested. A cursor defaults to an updateable state, but if you explicitly state that the cursor is going to be updated, your code will be easier to read. It would be even better if the update specified FOR UPDATE OF columnname , because that's the only column that is updated. Everything that has been discussed so far about cursors is part of the ANSI SQL-92 standard, so it's fairly generic and should be portable to any other database management system that is SQL-92-compliant. Table 6.2 lists some Transact-SQL-specific extensions to the cursor syntax that enable you to make performance enhancements for your cursor operations. Table 6.2. Transact-SQL Cursor Extensions
A few notes on the table. First, the default LOCAL or GLOBAL status of a cursor can be changed by changing the server-wide Default To Local Cursor configuration setting with sp_configure . Next, if you specify FORWARD_ONLY , and don't specify STATIC or KEYSET , the cursor behaves as a DYNAMIC cursor. In other words, the cursor sees any records inserted by other connections while the cursor is open. In addition, if you don't use the SCROLL , STATIC , KEYSET , or DYNAMIC options to specify that a cursor should scroll, the cursor will be FORWARD_ONLY . Also, you cannot use FORWARD_ONLY and FAST_FORWARD together. All that said, it pays to specifically spell everything out in your DECLARE statement to make it very obvious what you are attempting to do with your cursor. In other words, if you're doing a forward-only, updateable cursor, you could just use the normal " DECLARE cursor foo for <select> ", but it is better to do something like " DECLARE cursor foo forward_only for <SELECT statement> for UPDATE of <value> ". That way, it is easy to tell exactly what that cursor is going to be used for and what restrictions there are on the use of the cursor. So, when is it proper to use cursors? You should never use a cursor when you can write a better UPDATE statement to avoid using a cursor altogether. Cursors consume a lot of SQL Server resources, and they are nowhere near as fast as just running a single UPDATE statement, or even multiple UPDATE statements. Avoid using cursors. If you use cursors only when you absolutely have to, then you'll be using them properly.
Table 6.3 lists some interesting stored procedures that can be used to find out what cursors are available to the current connection and describes some of their properties. Table 6.3. Cursor Stored Procedures
These stored procedures can help you debug your cursor problems by enabling you to create the cursor and then track down what kind of cursor SQL Server is actually using. For example, if you declare a FORWARD_ONLY cursor, is it dynamic or static? That's more than you'll ever need to know about cursors in real life, and enough to cover what you'll need to know for the exam. REVIEW BREAK: Writing Scripts Using Statements, Comments, and MoreWe're about halfway through the chapter, so it's time for a quick review of some of the key points we've covered.
That's quite a bit of hands-on material, from writing scripts to managing loops and cursors. We still have more to cover in this chapter, including theory on locking and important points about transactions. |