Using Cursors

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:4

0
33
lượt xem
5
download

Using Cursors

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

Using Cursors When you execute a SELECT statement, all the rows are returned in one go. This might not always be appropriate. For example, you might want to take some action based on the column values retrieved for a particular row

Chủ đề:
Lưu

Nội dung Text: Using Cursors

  1. Using Cursors When you execute a SELECT statement, all the rows are returned in one go. This might not always be appropriate. For example, you might want to take some action based on the column values retrieved for a particular row. To do this, you can use a cursor to process rows retrieved from the database one row at a time. A cursor allows you to step through the rows returned by a particular SELECT statement. You follow these steps when using a cursor: 1. Declare variables to store the column values from the SELECT statement. 2. Declare the cursor, specifying your SELECT statement. 3. Open your cursor. 4. Fetch the rows from your cursor. 5. Close your cursor. You'll learn the details of these steps in the following sections. Step 1: Declare Variables to Store the Column Values from the SELECT Statement These variables must be compatible with the column types for the retrieved rows. For example, you'll want to use an int variable to store the value from an int column, and so on. The following example declares three variables to store the ProductID, ProductName, and UnitPrice columns from the Products table: DECLARE @MyProductID int DECLARE @MyProductName nvarchar(40) DECLARE @MyUnitPrice money Step 2: Declare the Cursor A cursor declaration consists of a name that you assign to the cursor and the SELECT statement that you want to execute. This SELECT statement is not actually run until you open the cursor. You declare your cursor using the DECLARE statement. The following example declares a cursor named ProductCursor with a SELECT statement that retrieves the ProductID, ProductName, and UnitPrice columns for the first 10 products from the Products table: DECLARE ProductCursor CURSOR FOR
  2. SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID
  3. You'll notice that the condition @@FETCH_STATUS = 0 is used in the WHILE loop to check that the FETCH statement successfully returned a row. When this condition is no longer true, the loop ends. Tip You can get the number of rows stored in a cursor using the @@CURSOR_ROWS function. You'll learn more about functions later in the "Using Functions" section. Step 5: Close the Cursor Close your cursor using the CLOSE statement. The following example closes ProductCursor: CLOSE ProductCursor You should also remove the reference to your cursor using the DEALLOCATE statement. This frees the system resources used by your cursor. The following example removes the reference to ProductCursor using the DEALLOCATE statement: DEALLOCATE ProductCursor The following section shows a complete example script that you may run using Query Analyzer. This script contains all five steps for using a cursor. Complete Example: ProductCursor.sql Listing 4.1 shows the ProductCursor.sql script. You can load this file into Query Analyzer and run it. Listing 4.1: USING CURSORS /* ProductCursor.sql uses a cursor to display the ProductID, ProductName, and UnitPrice columns from the Products table */ USE Northwind -- step 1: declare the variables DECLARE @MyProductID int DECLARE @MyProductName nvarchar(40) DECLARE @MyUnitPrice money -- step 2: declare the cursor
  4. DECLARE ProductCursor CURSOR FOR SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID
Đồng bộ tài khoản