MASTERING SQL SERVER 2000- P7

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
39
lượt xem
4
download

MASTERING SQL SERVER 2000- P7

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p7', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P7

  1. 270 CHAPTER 8 • ADVANCED TRANSACT-SQL Committing a nested transaction does not write the changes from that transaction permanently to the database; it merely makes them available to the outer transaction. Suppose you have the following SQL batch: BEGIN TRANSACTION UPDATE titles SET price = 20.00 WHERE title_id = ‘TC7777’ BEGIN TRANSACTION UPDATE titles SET type = ‘potboiler’ WHERE title_id = ‘TC7777’ COMMIT TRANSACTION ROLLBACK TRANSACTION In this case, the COMMIT TRANSACTION statement tells SQL Server that you’re finished with the second transaction that you started. However, the ROLLBACK TRANSACTION then rolls back all the work since the first BEGIN TRANSACTION, including the inner nested transaction. Although transaction names appear to offer increased readability for your code, they interact poorly with nested transactions. In fact, you can refer to a transaction by name only if it’s the outermost transaction in a batch. Our recommendation is to avoid naming transactions if you plan to ever nest transactions. COMMIT TRANSACTION The syntax of COMMIT TRANSACTION is very similar to that of BEGIN TRANSAC- TION. There’s also an alternative statement with the same purpose: COMMIT TRANS[ACTION] [transaction_name | @name_variable] COMMIT [WORK] When you issue a COMMIT TRANSACTION statement, the most recent transaction you started is marked as ready to commit. When you commit the outermost in a series of nested transactions, the changes are written back to the database. Of course, if there’s only one transaction open, the changes are written immediately. It’s your responsibility to make sure you’ve made all the changes you want before issuing a COMMIT TRANSACTION statement. Once a transaction has been commit- ted, it can’t be rolled back. Although you can use a name in the COMMIT TRANSACTION statement, SQL Server makes no attempt to match this to a name in a BEGIN TRANSACTION state- ment. The name’s purely for your convenience in making your code more readable.
  2. TRANSACTIONS 271 COMMIT, with or without the optional keyword WORK, is exactly synonymous to COMMIT TRANSACTION with no transaction name. This form of the statement is ANSI SQL-92 compatible. ROLLBACK TRANSACTION ROLLBACK TRANSACTION also comes in two forms: ROLLBACK TRANS[ACTION] [transaction_name | @name_variable | savepoint_name | @savepoint_variable] ROLLBACK [WORK] ROLLBACK TRANSACTION throws away all changes since the most recent BEGIN TRANSACTION. Again, you can supply a transaction name as either a constant or a PA R T variable, but SQL Server ignores this name. You can also roll back part of a transaction by supplying a savepoint name. We’ll II talk about savepoints in the next section. If a transaction is a distributed transaction (one that affects databases on multiple servers), you can’t roll back to a savepoint. ROLLBACK, with or without the optional WORK keyword, is the SQL-92 compli- ant form of the statement. However, in this form, you can’t roll back only one of a set of nested transactions. ROLLBACK WORK always rolls back to the outermost (first) Transact-SQL transaction in a batch. WA R N I N G ROLLBACK WORK rolls back all nested transactions and sets @@TRANCOUNT to zero. If you call ROLLBACK TRANSACTION as part of a trigger, subsequent SQL state- ments in the same batch are not executed. On the other hand, if you call ROLLBACK TRANSACTION in a stored procedure, subsequent SQL statements in the same batch are executed. SAVE TRANSACTION The SAVE TRANSACTION statement lets you partially commit a transaction, while still being able to roll back the rest of the transaction: SAVE TRANS[ACTION] {savepoint_name | @savepoint_variable}
  3. 272 CHAPTER 8 • ADVANCED TRANSACT-SQL Note that when you issue SAVE TRANSACTION, you must name it. This name pro- vides a reference point for a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. An example will make the use of SAVE TRANSACTION more clear. Consider the following T-SQL batch: BEGIN TRANSACTION UPDATE titles SET price = 20.00 WHERE title_id = ‘TC7777’ SAVE TRANSACTION pricesaved UPDATE titles SET type = ‘potboiler’ WHERE title_id = ‘TC7777’ ROLLBACK TRANSACTION pricesaved COMMIT TRANSACTION In this case, the ROLLBACK TRANSACTION statement removes the effects of the update to the type column, while leaving the update to the price column ready to be committed. Then the COMMIT TRANSACTION statement commits the part of the transaction that wasn’t rolled back (in this case, the change to the price column). @@TRANCOUNT The @@TRANCOUNT system global variable tells you the number of nested transac- tions that are currently pending. If no transactions are pending, this variable will con- tain zero. This is useful for determining whether a trigger, for example, is executing in the middle of a transaction already started by a T-SQL batch. @@ERROR The @@ERROR system global variable holds the most recent error number from any T-SQL statement. Whenever a statement is executed that does not cause an error, this variable will contain zero. That is, it’s reset to zero every time you successfully execute a statement. So if you want to check at some later point whether a statement has caused an error, you need to save the value of @@ERROR to a local variable. A Transaction Example Let’s end this section with a more complex T-SQL batch that will illustrate the transaction-processing statements: DECLARE @price_err int, @type_err int BEGIN TRANSACTION
  4. TRANSACTIONS 273 UPDATE titles SET price = 20.00 WHERE title_id = ‘TC7777’ SET @price_err = @@ERROR SAVE TRANSACTION pricesaved UPDATE titles SET type = ‘potboiler’ WHERE title_id = ‘TC7777’ SET @type_err = @@ERROR IF @type_err 0 ROLLBACK TRANSACTION pricesaved IF @price_err = 0 AND @type_err = 0 BEGIN COMMIT TRANSACTION PRINT ‘Changes were successful’ PA R T END II ELSE ROLLBACK TRANSACTION Here’s a blow-by-blow account of this batch: 1. The DECLARE statement sets up two local variables. Transact-SQL 2. The BEGIN TRANSACTION statement starts a transaction. 3. The first UPDATE statement makes a change to the price column. 4. The first SET statement is used to save the value of @@ERROR so that you can check later whether the first UPDATE statement was successful. Note that this statement must immediately follow the UPDATE statement. 5. The SAVE TRANSACTION statement sets a savepoint. 6. The second UPDATE statement makes a change to the type column. 7. The second SET statement is used to save the value of @@ERROR so you can tell whether the second UPDATE statement succeeded. 8. If there was an error on the second UPDATE statement, the first ROLLBACK TRANSACTION statement undoes the transaction back to the savepoint. 9. If there are no errors at all, the transaction is committed, and a message is printed. Note the use of BEGIN and END to group two T-SQL statements into one logical statement. This is necessary because the IF statement refers only to the following statement.
  5. 274 CHAPTER 8 • ADVANCED TRANSACT-SQL 10. If there are any errors, the second ROLLBACK TRANSACTION statement undoes all of the work. Distributed Transactions So far, we’ve been discussing local transactions: those that make changes in a single database. SQL Server also supports distributed transactions: transactions that make changes to data stored in more than one database. These databases need not be SQL Server databases; they can be databases on other linked servers. NOTE For more information on linked servers, see Chapter 6. A distributed transaction can be managed in code using exactly the same SQL statements as you’d use for a local transaction. However, when you issue a COMMIT TRANSACTION on a distributed transaction, SQL Server automatically invokes a pro- tocol called two-phase commit (sometimes referred to as 2PC). In the first phase, SQL Server asks every database involved to prepare the transaction. The individual data- bases verify that they can commit the transaction and set aside all the resources nec- essary to do so. It’s only if every involved database tells SQL Server that it’s OK to commit the transaction that the second phase starts. In this phase, SQL Server tells every involved database to commit the transaction. If any of the databases involved are unable to commit the transaction, SQL Server tells all of the databases to roll back the transaction instead. Microsoft DTC Distributed transactions are managed by a SQL Server component called the Distrib- uted Transaction Coordinator (DTC). This is a separate service that’s installed at the same time as SQL Server. If you’re going to use distributed transactions, you should set this service to autostart. Figure 8.1 shows this service selected in the SQL Server Service Manager.
  6. TRANSACTIONS 275 FIGURE 8.1 Checking the status of the Microsoft DTC service BEGIN DISTRIBUTED TRANSACTION PA R T You can tell SQL Server explicitly to start a distributed transaction with the BEGIN DISTRIBUTED TRANSACTION statement: II BEGIN DISTRIBUTED TRANS[ACTION] [transaction_name | @name_variable] The only difference between this statement and the regular BEGIN TRANSACTION statement is the inclusion of the DISTRIBUTED keyword. Transact-SQL Local transactions are automatically escalated to distributed transactions if you change data on a remote server during the transaction. For example, if you execute an INSERT, UPDATE, or DELETE statement on a remote server, or call a remote stored procedure, while you’re in the midst of a transaction, that transaction will become a distributed transaction. Transaction Tips Transactions consume resources on the server. In particular, when you change data within a transaction, that data must be locked to ensure that it’s available if you com- mit the transaction. So, in general, you need to make transactions efficient to avoid causing problems for other users. Here are a few points to consider: • Don’t do anything that requires user interaction within a transaction, because this can cause locks to be held for a long time while the application is waiting for the user. • Don’t start transactions for a single SQL statement. • Change as little data as possible when in a transaction.
  7. 276 CHAPTER 8 • ADVANCED TRANSACT-SQL • Don’t start a transaction while the user is browsing through data. Wait until they’re actually ready to change the data. • Keep transactions as short as possible. Rowset Functions Rowset functions are functions that return an object that can be used in place of a table in another SQL statement. For example, as you saw in Chapter 7, some rowset func- tions can be used to provide the rows to be inserted with an INSERT statement. There are five rowset functions in SQL Server 2000: • CONTAINSTABLE • FREETEXTTABLE • OPENQUERY • OPENROWSET • OPENDATASOURCE CONTAINSTABLE The CONTAINSTABLE statement lets you construct a virtual table from the results of a complex full-text search. This statement’s syntax is a bit more complicated than that of most of the statements we’ve examined so far: CONTAINSTABLE (table_name, {column_name | *}, ‘’ [,top_n]) ::= { | | | | } | {() {AND | AND NOT | OR} […n] }
  8. ROWSET FUNCTIONS 277 ::= FORMSOF(INFLECTIONAL, [,…n]) ::= {“word*” | “phrase*”} ::= { | } {{NEAR | ~} { | }} […n] ::= word | “phrase” ::= ISABOUT ( PA R T {{ II | | | } Transact-SQL [WEIGHT (weight_value)] } [,…n]) TI P You can use CONTAINSTABLE only on a table that’s been enabled for full-text indexing. For more on full-text indexing, see Chapter 6. If you work carefully through that syntax, you’ll see that the basic idea of CON- TAINSTABLE is to allow you to do a “fuzzy” search, which returns items that might not match entirely. Some further syntactical notes: • Using the asterisk (*) to specify columns tells CONTAINSTABLE to search all columns that have been registered for full-text searching, which might not be all the columns in the table. • Weight values are numbers between zero and one that specify how important each match is considered to be in the final virtual table.
  9. 278 CHAPTER 8 • ADVANCED TRANSACT-SQL • You can limit the number of results returned by specifying an integer in the top_n parameter. This is useful when you’re searching a very large source table and want to see only the most important matches. The CONTAINSTABLE statement returns a virtual table containing two columns, always named KEY and RANK. For example, consider the following statement: SELECT * FROM CONTAINSTABLE(Products, ProductName, ‘ISABOUT(mix WEIGHT(.8), sauce WEIGHT(.2))’) Assuming that you’ve enabled the Product table in the Northwind sample database for full-text searching on the ProductName column, this statement returns the results shown in Figure 8.2. The ISABOUT search condition here specifies that results con- taining the word mix should be rated as more important than those containing the word sauce. FIGURE 8.2 Using CONTAINSTABLE to generate a virtual table The KEY column will always contain values from the column that you identified as the primary key to the full-text indexing service. To make this statement more useful, you’ll probably want to use this column to join back to the original table. Figure 8.3 shows the results of the following statement: SELECT ProductName, RANK FROM CONTAINSTABLE(Products, ProductName, ‘ISABOUT(mix WEIGHT(.8), sauce WEIGHT(.2))’)
  10. ROWSET FUNCTIONS 279 AS C INNER JOIN Products ON Products.ProductID = C.[KEY] FIGURE 8.3 Using CONTAINSTABLE joined to the original search table PA R T II Transact-SQL N OTE The virtual table needs to be aliased to be included in a join, and you must include the square brackets around the joining name because KEY is a SQL Server keyword. FREETEXTTABLE Like CONTAINSTABLE, FREETEXTTABLE generates a virtual table based on full-text indexing information. However, the syntax of FREETEXTTABLE is a good deal simpler: FREETEXTTABLE (table_name, {column_name | *}, ‘freetext’ [,top_n]) TI P You can use FREETEXTTABLE only on a table that’s been enabled for full-text indexing. For more on full-text indexing, see Chapter 6.
  11. 280 CHAPTER 8 • ADVANCED TRANSACT-SQL You can think of FREETEXTTABLE as being like a black-box version of CONTAINS- TABLE. Internally, SQL Server breaks the freetext string up into words, assigns a weight to each word, and then looks for similar words. For example, the following statement could be used to retrieve items whose description looks somehow similar to mixed sauces: SELECT ProductName, RANK FROM FREETEXTTABLE(Products, ProductName, ‘mixed sauces’) AS C INNER JOIN Products ON Products.ProductID = C.[KEY] Just like CONTAINSTABLE, FREETEXTTABLE returns a virtual table with KEY and RANK columns. Figure 8.4 shows the result of this particular statement. FIGURE 8.4 Using FREETEXTTABLE to locate products TI P FREETEXTTABLE is probably more useful than CONTAINSTABLE when the search term is being input by a user, who might not understand the exact syntax SQL Server uses for full-text searches.
  12. ROWSET FUNCTIONS 281 OPENQUERY The OPENQUERY statement lets you use any query (SQL statement that returns rows) on a linked server to return a virtual table. The syntax of OPENQUERY is as follows: OPENQUERY(linked_server, ‘query’) NOTE For more information on creating linked servers, see Chapter 6. Figure 8.5 shows in SQL Server Enterprise Manager that the MOOCOW server knows about a linked server named BIGREDBARN, which is also a Microsoft SQL Server. If you connected to the BIGREDBARN server directly, you could run a query like the following: SELECT * FROM Northwind.dbo.Customers PA R T II FIGURE 8.5 Inspecting properties for a linked server Transact-SQL This query would return all of the rows in the Customers table owned by dbo in the Northwind database. So far, there’s no need for OPENQUERY. However, suppose
  13. 282 CHAPTER 8 • ADVANCED TRANSACT-SQL you want to join the Customers table from the BIGREDBARN server to the Orders table from the MOOCOW server. In this case, you might connect to the MOOCOW server and run the following statement instead: SELECT CompanyName, OrderID, OrderDate FROM OPENQUERY(BIGREDBARN, ‘SELECT * FROM Northwind.dbo.Customers’) AS Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY OrderID Note that the original query that retrieved the records in the context of the BIGREDBARN server has been incorporated as one of the parameters of the OPEN- QUERY statement. OPENQUERY is the easiest tool that you can use to perform distributed queries using SQL Server. By using OPENQUERY, you can join any number of tables from dif- ferent data sources. These data sources don’t even need to be SQL Server tables; as long as they’re data sources that you can represent as linked servers (basically, any data source that you have an OLE DB provider to connect with), you can use them with OPENQUERY. OPENROWSET OPENROWSET also provides a way to use data from a different server in a SQL Server statement. In the case of OPENROWSET, you supply the information needed to con- nect via OLE DB directly: OPENROWSET (‘provider_name’, ‘datasource’;’user_id’;’password’, ‘query’) OPENROWSET is useful when you haven’t already created a linked server for a par- ticular data source. Instead of using a linked server name, this statement takes the necessary information to connect to a data source via OLE DB directly. For example, suppose that the BIGREDBARN server has a user named sa with a blank password. In that case, you could use the following OPENROWSET statement to retrieve the exact same results as the OPENQUERY statement in the previous section: SELECT CompanyName, OrderID, OrderDate FROM OPENROWSET(‘SQLOLEDB’, ‘BIGREDBARN’;’sa’;’’, ‘SELECT * FROM Northwind.dbo.Customers’) AS Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY OrderID
  14. ROWSET FUNCTIONS 283 TI P Some of the arguments in OPENROWSET are separated by semicolons instead of commas. Figure 8.6 shows the results of running this particular statement. FIGURE 8.6 Using OPENROWSET PA R T II Transact-SQL OPENDATASOURCE The OPENDATASOURCE statement provides a more flexible way (compared to OPEN- ROWSET) to make a temporary connection to an OLE DB data source. This statement does this by taking an entire OLE DB connection string as one of its parameters: OPENDATASOURCE(provider_name, connection_string) OPENDATASOURCE is more flexible than OPENROWSET in that OPENDATA- SOURCE can be used in place of a linked server name, so it need not refer to any par- ticular database or table on the other server. You can use OPENDATASOURCE to refer to any table.
  15. 284 CHAPTER 8 • ADVANCED TRANSACT-SQL For example, you could perform the same query that was shown in the OPEN- ROWSET example with the following OPENDATASOURCE statement: SELECT CompanyName, OrderID, OrderDate FROM OPENDATASOURCE(‘SQLOLEDB’, ‘Data Source=BIGREDBARN;User ID=sa;Password=’ ).Northwind.dbo.Customers AS Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY OrderID TI P OPENROWSET and OPENDATASOURCE should be used only for data sources that you need to query on an infrequent basis. If you need to regularly connect to a particular data source, it’s more efficient to use a linked server for that connection. Cursors Traditionally, SQL provides a set-oriented look for your data. For example, when you execute a SELECT statement, it returns a set of rows. This set is all one thing, not a selection of individual rows. Although this is a useful view for many traditional batch-processing applications, it’s less appealing for interactive applications where a user might want to work with rows one at a time. What Are Cursors? SQL Server’s solution to this problem is to introduce cursors. If you’ve worked with recordsets in a product such as Access or Visual Basic, you can understand cursors as a server-side recordset. A cursor is a set of rows together with a pointer that identifies a current row. T-SQL provides statements that allow you to move the pointer and to work with the current row. In the remainder of this section, you’ll learn about the fol- lowing statements: • DECLARE CURSOR • OPEN • FETCH • CLOSE • DEALLOCATE
  16. CURSORS 285 DECLARE CURSOR The DECLARE CURSOR statement is used to set aside storage for a cursor and to set the basic properties of the cursor. Actually, there are two different forms of the DECLARE CURSOR statement. The first form is the ANSI standard DECLARE CURSOR: DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,…n]]}] In this form of the DECLARE CURSOR statement: • The DECLARE and CURSOR keywords are required to declare a cursor. • The cursor_name is an arbitrary SQL identifier that will identify this cursor in subsequent T-SQL statements. • INSENSITIVE tells SQL Server to establish a temporary table just for this cursor. Modifications that other users make while the cursor is open will not be PA R T reflected in the cursor’s data, and you won’t be able to make any modifications through the cursor. II • SCROLL specifies that all of the options of the FETCH statement should be sup- ported. If you omit SCROLL, only FETCH NEXT is supported. • The select_statement argument is a standard T-SQL SELECT statement that sup- plies the rows for the cursor. This statement cannot use the COMPUTE, COM- Transact-SQL PUTE BY, FOR BROWSE, or INTO options. • READ ONLY prevents any updates through the cursor. By default, the cursor will allow updating (unless it was opened with the INSENSITIVE option). • UPDATE specifies explicitly that the cursor should allow updating. If you use UPDATE OF with a list of column names, only data in those columns can be updated. There’s also an extended form of DECLARE CURSOR that is not ANSI SQL compatible: DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,...n]]] In this form of the DECLARE CURSOR statement: • The DECLARE and CURSOR keywords are required to declare a cursor.
  17. 286 CHAPTER 8 • ADVANCED TRANSACT-SQL • The cursor_name is an arbitrary SQL identifier that will identify this cursor in subsequent T-SQL statements. • The LOCAL keyword limits the use of the cursor to the batch, stored procedure, or trigger where it was created. • The GLOBAL keyword makes the cursor available to any statement on the cur- rent connection. • FORWARD_ONLY specifies that only the NEXT option of the FETCH statement is supported. • SCROLL specifies that all of the options of the FETCH statement should be sup- ported. If you specify SCROLL, you cannot specify FAST_FORWARD. • STATIC causes the cursor to return a set of rows that reflects the state of the database when the cursor is opened and that is never updated. You can’t make changes through a static cursor. • KEYSET specifies that the cursor should be updateable, both by the connection and by other users. However, new rows added by other users won’t be reflected in the cursor. • DYNAMIC specifies that the cursor should be fully updateable and that it should reflect new rows. • READ_ONLY specifies that the cursor should be read-only. • SCROLL_LOCKS specifies that updates or deletions made through the cursor should always succeed. SQL Server ensures this by locking the rows as soon as they’re read into the cursor. • OPTIMISTIC uses optimistic locking when you attempt to change a row through the cursor. • TYPE_WARNING tells SQL Server to send a warning if the selected cursor options can’t all be fulfilled. • The select_statement argument is a standard T-SQL SELECT statement that sup- plies the rows for the cursor. This statement cannot use the COMPUTE, COM- PUTE BY, FOR BROWSE, or INTO options. • FOR UPDATE specifies explicitly that the cursor should allow updating. If you use UPDATE OF with a list of column names, only data in those columns can be updated.
  18. CURSORS 287 OPEN and @@CURSOR_ROWS The OPEN statement is used to populate a cursor with the records to which it refers: OPEN {{[GLOBAL] cursor_name} | cursor_variable_name} You must use the GLOBAL keyword if you’re referring to a cursor declared with the GLOBAL keyword. You can use either the name of a cursor directly or the name of a cursor variable (one declared with the DECLARE statement and set equal to a cursor with the SET statement). Of course, the cursor must be declared before you issue the OPEN statement. If the cursor was declared with the INSENSITIVE or STATIC keywords, the OPEN statement will create a temporary table in the tempdb database to hold the records. If the cursor was declared with the KEYSET keyword, the OPEN statement will create a temporary table in the tempdb database to hold the keys. You don’t need to worry about these tables; SQL Server will delete them when the cursor is closed. Once a cursor has been opened, you can use the @@CURSOR_ROWS global vari- PA R T able to retrieve the number of rows in this cursor. For example, consider the following II T-SQL batch: DECLARE customer_cursor CURSOR LOCAL SCROLL STATIC FOR SELECT * FROM Customers Transact-SQL OPEN customer_cursor PRINT @@CURSOR_ROWS As you can see in Figure 8.7, the PRINT statement shows that all 91 rows of the Customers table are in the cursor. FIGURE 8.7 Counting rows in a cursor
  19. 288 CHAPTER 8 • ADVANCED TRANSACT-SQL WAR N I N G The @@CURSOR_ROWS variable always refers to the most recently opened cursor. You may want to store the value of this variable directly after the OPEN statement so that you can refer to it later. You need to be a bit cautious about using @@CURSOR_ROWS, because under some circumstances, it won’t reflect the actual number of rows in the cursor. That’s because SQL Server might decide to fetch data into the cursor asynchronously, so that process- ing can continue while the cursor is still being populated. SQL Server will fill a cursor asynchronously if the cursor is declared with the STA- TIC or KEYSET parameters and SQL Server estimates that the number of rows will be greater than a certain threshold value. You can set this value with the sp_configure system stored procedure; the name of the option is cursor threshold. By default, the value is set to –1, which tells SQL Server to always populate cursors synchronously. NOTE See Chapter 14 for more information on sp_configure. Depending on the circumstances, @@CURSOR_ROWS might return one of the fol- lowing values: • A negative number indicates that the cursor is being populated asynchronously and shows the number of rows retrieved so far. The value –57, for example, indi- cates that the cursor has 57 rows, but that SQL Server has not finished populat- ing the cursor. • The value –1 is a special case that’s always returned for dynamic cursors. Because other users can be adding or deleting data, SQL Server can’t be sure about the number of rows in a dynamic cursor, or whether it’s fully populated. • Zero indicates that there isn’t an open cursor. • A positive number indicates that the cursor is fully populated with that number of rows. FETCH and @@FETCH_STATUS The FETCH statement is used to retrieve data from a cursor to variables so that you can work with the data. This statement has a number of options: FETCH [[ NEXT | PRIOR | FIRST | LAST
  20. CURSORS 289 | ABSOLUTE {n | @n_variable} | RELATIVE {n | @n_variable} ] FROM ] {{[GLOBAL] cursor_name} | @cursor_variable_name} [INTO @variable_name [,…n]] If you keep in mind that a cursor is a set of records with a pointer to a particular record, it’s pretty easy to understand the FETCH statement. FETCH is used to move the record pointer. • NEXT is the default option and fetches the next row in the cursor. If FETCH NEXT is the first statement issued, it fetches the first row from the cursor. • PRIOR fetches the previous row in the cursor. • FIRST fetches the first row in the cursor. PA R T • LAST fetches the last row in the cursor. II • ABSOLUTE fetches the particular record specified. For example, ABSOLUTE 5 fetches the fifth record. If you use a variable to hold the number, the variable must be of type int, smallint, or tinyint. • RELATIVE fetches a record ahead or behind the current record by the specified Transact-SQL amount. For example, RELATIVE 5 fetches the record five past the current record, and RELATIVE –5 fetches the record five before the current record. If you use a variable to hold the number, the variable must be of type int, smallint, or tinyint. • INTO lets you specify variables that will hold the fetched data. You must supply enough variables to hold all the columns from the cursor. The variables will be filled in column order, and the datatypes must match those in the cursor or be datatypes that can be implicitly converted from those in the cursor. Not all FETCH options are supported by all cursors, depending on how the cursor was declared. Here are the rules: • If the cursor was declared with SQL-92 syntax without SCROLL, only NEXT is supported. • If the cursor was declared with SQL-92 syntax with SCROLL, all options are sup- ported. • If the cursor was declared with SQL Server syntax with FORWARD_ONLY or FAST_FORWARD, only NEXT is supported.
Đồng bộ tài khoản