Managing Result Sets by Using Cursors and Transact-SQL


Managing Result Sets by Using Cursors and Transact -SQL

  • Manage result sets by using cursors and T-SQL. Considerations include locking models and appropriate usage.

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

Extension

Description

LOCAL

This is the optional state for a cursor. It means the cursor is available for only the current batch and the current connection. To change the default behavior, set the Default to Local Cursor database option.

GLOBAL

"Global" in this case means "Global to the current connection." Declaring a cursor as global makes it available to subsequent batches or stored procedures that are run by the connection. The cursor is not available to other connections, even if the connection is from the same user .

FORWARD_ONLY

This tells SQL Server that the cursor is going to run only from the beginning of the recordset to the end of the recordset. The cursor is not allowed to go back-ward or skip around. The only fetch that works is FETCH NEXT . This is an optimization; it allows SQL Server to consume less overhead for the cursor.

STATIC

This does the same thing as the INSENSITIVE keyword in the SQL-92 syntax.

KEYSET

If you use this, your cursor will not be able to access data inserted by other users after the cursor is opened, and if a row is deleted by another user, an @@FETCH_STATUS of -2 (row is missing) will be returned if you attempt to fetch a deleted row. This type of cursor has less overhead than a DYNAMIC cursor, but (unless FORWARD_ONLY is also specified) all the different FETCH options are available.

DYNAMIC

A DYNAMIC cursor is the opposite of a KEYSET cursor. All inserts and deletes done by users are immediately available to the cursor. However, FETCH ABSOLUTE does not work with a dynamic cursor because the underlying data may change the position of the records.

FAST_FORWARD

This is a cursor that has all the properties of a FORWARD ONLY and READ_ONLY cursor, and is designed to go forward quickly with little overhead.

READ_ONLY

Does not allow updates to the cursor.

SCROLL_LOCKS

This causes SQL Server to exclusively lock each row that is touched by the cursor as they are read in, to prevent other users from updating the record.

OPTIMISTIC

This causes SQL Server to not lock any rows during the scrolling of the cursor, and you have to just hope that none of the rows being changed by the cursor is simultaneously being changed by somebody else. Attempting to change a row through the cursor results in an error.

TYPE_WARNING

If somehow your cursor changes type implicitly, a warning is issued.

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.

IN THE FIELD: THE PROPER USE OF CURSORS

We tend not to be very nice to the people we are interviewing for a database administrator position. One of the things that happens during the process is having the candidate stand up at the whiteboard and tell the interviewers how he'd approach different database problems. One favorite is to ask the candidate to write the syntax for using a cursor on the board.

If they get it right it's a big negative mark. We don't want to hire someone who is too good with cursors, because that means that they use them too much.

If you approach every single problem with the attitude that it can be solved without a cursor, you will find a way to solve it without a cursor, and it will probably run faster and cause fewer problems than a solution that uses a cursor.

Use a cursor like a carpenter uses a sledgehammer. It's out in his truck, it's really heavy, and if there's any other way to solve the problem that doesn't involve walking all the way out to the truck and lugging a 20- pound hammer up into a house, he'll use that instead.

Sometimes a sledgehammer is the right tool for the job. But not very often.

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

Procedure

Description

sp_cursor_list

Returns a list of all the declared cursors available to the current connection with some of their properties.

sp_describe_cursor

Returns the properties of the cursor, such as FORWARD ONLY and READ_ONLY .

sp_describe_cursor_columns

Returns the columns that are found in a cursor and their data types.

sp_describe_cursor_tables

Returns the tables used by a cursor, which you'll need to have for an UPDATE WHERE CURRENT OF statement.

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 More

We're about halfway through the chapter, so it's time for a quick review of some of the key points we've covered.

  • Programming in SQL Server is done in the T-SQL language by writing scripts that are made up of batches, which are groups of statements.

  • Variables are local objects that can be used to store temporary values, such as counters.

  • Comments are used to make T-SQL batches easier to understand and to temporarily disable statements for debugging.

  • The IF...ELSE construct can be used to conditionally execute statements, and the WHILE construct can be used to repeat statements.

  • Cursors are used to work on rowsets one row at a time. They are useful, but not as efficient as direct statements on sets.

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.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net