구글와이드(336x280)_상단 2개


(영문) Using a Cursor DataBase(MS SQL)

November 19, 2003
T-SQL Programming Part 3 - Processing Sequentially Through a Set of Records

By Gregory A. Larsen
At some point you will have some business logic that will require you to process sequentially through a set of records one record at a time. For example you may have a list of databases, and for each database you may want to build a command that will perform some process against each database. Or you might have a set of records where you want to process through each record one at a time, so you can select additional information from another table based on the information contained in each record. This article will discuss two different ways to process through a set of records one record at a time.

Using a Cursor

The first method I will discuss uses a cursor to process through a set of records one record at a time. A cursor is basically a set of rows that you define based on a record set returned from a query. A cursor allows applications a mechanism to process through a result set one row at a time. With a cursor an application is allowed to position itself to a specific row, scroll back and forth, and a number of other things. It would take a series of articles to describe all the functionality of a cursor. For the purpose of this article I'm only going to focus on how to use the default scrolling functionality of a cursor. This default functionality will only read from the first row to the last row in a cursor, one row at a time. I will leave additional cursor topics to another article series.
To define a cursor the DECLARE CURSOR statement is used. Here is the basic format for the simple cursor topic I will be discussing in this article.
DECLARE cursor_name CURSOR      FOR select_statement
The cursor_name is the name you want to associate with the cursor. The select_statement is the query that will determine the rows that make up the cursor. Note there are other parameters/options associated with the DECLARE CURSOR statement that help define more complicated cursor processing than I will be covering in this article. For these additional options please read Microsoft SQL Server Books Online.
Let's review a fairly simple cursor example. This example will define a cursor that contains the top 5 Customer_Id's in the Customer table in the Northwind database. It will then process through each record displaying a row number and the CustomerID for each. Here is the code to do this.
    declare @CustId nchar(5)
        declare @RowNum int
        declare CustList cursor for
        select top 5 CustomerID from Northwind.dbo.Customers
        OPEN CustList
        FETCH NEXT FROM CustList 
        INTO @CustId
        set @RowNum = 0 
        WHILE @@FETCH_STATUS = 0
        BEGIN
          set @RowNum = @RowNum + 1
          print cast(@RowNum as char(1)) + ' ' + @CustId
          FETCH NEXT FROM CustList 
            INTO @CustId
        END
        CLOSE CustList
        DEALLOCATE CustList
Here are the results that are generated from the print statement when I run it against my Northwind Database.
       1 ALFKI
        2 ANATR
        3 ANTON
        4 AROUT
        5 BERGS
Let's look at the above code in a little more detail. I first declared a cursor called "CustList". The "CustList" cursor is populated using a SELECT statement that uses the TOP clause to return only the top 5 CustomerId's. Next the cursor is opened. Each record in the "CustList" cursor is retrieved, one record at a time, using the "FETCH NEXT" next statement. The "FETCH NEXT" statement populates the local variable @CustID with the CustomerID of the current record being fetched. The @@FETCH_STATUS variable controls whether the WHILE loop is executed. @@FETCH_STATUS is set to zero when a record is successfully retrieved from the cursor "CustList". Inside the WHILE loop the @RowNum variable is incremented by 1 for each record processed. The calculated Row Number and @CustId are then printed out. Lastly, a "FETCH NEXT" statement is used to retrieve the next row before the next cycle of the WHILE loop. This process continues one record at a time until all records in cursor "CustList" have been processed.

Using a Select Statement

You can also use a SELECT statement to process through a set of records one record at a time. To do this I will issue an initial SELECT statement that will return the first row, then a series of follow on SELECT statements where each SELECT statement retrieves the next row. This is done by using the "TOP 1" clause of the SELECT statement, and a WHERE statement.
I will use the same example as above and only return the top 5 CustomerID's from the Northwind database Customers table. In this code I will use two different "SELECT TOP 1" statements and a WHILE loop to return all 5 records. Each record will be processed one at a time.
        declare @CustId nchar(5)
        declare @RowNum int
        select top 1 @CustId=CustomerID from Northwind.dbo.Customers
        set @RowNum = 0 
        WHILE @RowNum < 5
        BEGIN
          set @RowNum = @RowNum + 1
          print cast(@RowNum as char(1)) + ' ' + @CustId
          select top 1 @CustId=CustomerID from Northwind.dbo.Customers
                       where CustomerId > @CustID
        END
Here you can see the first SELECT statement selects only the first CustomerID. This ID is placed in the local variable @CustID. The WHILE loop is controled by the local variable @RowNum. Each time through the WHILE loop, the Row Number and CustomerID are printed out. Prior to returning to the top of the WHILE loop I used another "SELECT TOP 1" statement to select the next CustomerID. This SELECT statement uses a WHERE clause on the SELECT statement to select the first CustomerID that is greater than the CustomerID that was just printed. The WHILE loop is process 5 times, allowing the SELECT TOP 1 method to retrieve the top 5 CustomerID's one records at a time. This example produces the same printed output as my prior CURSOR example.

Conclusion

Hopefully this article has given you some ideas on how to use a CURSOR, and a SELECT statement to process through a set of records. I use both of these methods, although I find using a SELECT statement to be a little simpler to code. You will need to decide which solution makes the most sense in your environment.

null



바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페
http://cafe.daum.net/sisa-1

바보들의 영문법 다음 카페
http://cafe.daum.net/babo-edu/

티스토리 내 블로그
http://earthly.tistory.com/

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.


가가챗창

flag_Visitors

free counters