Đang chuẩn bị liên kết để tải về tài liệu:
Hướng dẫn học Microsoft SQL Server 2008 part 63

Trong vòng con trỏ sau Ví dụ, một hàng được chèn và xóa để bạn có thể kiểm tra dữ liệu thay đổi như thế nào ảnh hưởng đến các loại khác nhau của con trỏ: Sử dụng AdventureWorks2008; - thiết lập mẫu hàng để kiểm tra hàng đã xóa con trỏ DELETE WHERE Production.Location Tên như '% Paul'; INSERT Production.Location | Part IV Developing with SQL Server In the following example cursor loop a row is inserted and deleted so that you can test how data changes affect the different types of cursors Use AdventureWorks2008 -- setup sample row for deleted row cursor test DELETE Production.Location WHERE Name like Paul INSERT Production.Location Name CostRate Availability VALUES PaulsTest 1 1 -- set-up variables DECLARE LocationID SMALLINT LocationName VARCHAR 50 CostRate SMALLMONEY Availability DECIMAL 8 2 -- Step 1 Declare the Cursor -- alternative cursor types - STATIC KEYSET DYNAMIC FAST_FORWARD DECLARE cLocation CURSOR STATIC FOR SELECT LocationID Name CostRate Availability FROM Production.Location ORDER BY Name -- Step 2 Open the Cursor OPEN cLocation -- Step 3 Prime the Cursor FETCH cLocation INTO LocationID LocationName CostRate Availability -- set-up the print output PRINT Fetch_Status LocationID Name PRINT -------------- ------------- ------------------------ WHILE @@Fetch_Status -1 -- 0 BEGIN -- while loop -- perform some work with the data -- but only if row actually found IF @Fetch_Status 0 BEGIN PRINT CAST @Fetch_Status as CHAR 10 CAST LocationID as CHAR 10 582 www.getcoolebook.com Kill the Cursor 22 @LocationName END IF @Fetch_Status -2 PRINT Hit Deleted Row -- Step 3 Iterating through the cursor FETCH cLocation INTO LocationID LocationName @CostRate Availability -- Insert and delete rows during cursor run -- for cursor type testing IF LocationID 40 BEGIN INSERT Production.Location Name CostRate Availability VALUES PaulsINSERT 1 1 DELETE Production.Location WHERE Name PaulsTest END END -- while loop PRINT PRINT Final @ Fetch_Status Cast @ Fetch_Status as char 2 -- Step 4 Close CLOSE cLocation -- Step 5 Deallocate DEALLOCATE cLocation Watching the cursor In SQL Server 2008 there are three ways to observe the cursor in action Step through the cursor WHILE loop using the new T-SQL debugger. The T-SQL debugger is covered in Chapter 6 Using Management Studio and there s a .