Processing Sequentially Through a Set of Records
2024-07-21 02:06:56
供稿：網友
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 intdeclare custlist cursor forselect top 5 customerid from northwind.dbo.customersopen custlistfetch next from custlist into @custidset @rownum = 0 while @@fetch_status = 0begin set @rownum = @rownum + 1 print cast(@rownum as char(1)) + ' ' + @custid fetch next from custlist into @custidendclose custlistdeallocate custlist
here are the results that are generated from the print statement when i run it against my northwind database.
1 alfki2 anatr3 anton4 arout5 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 intselect top 1 @custid=customerid from northwind.dbo.customersset @rownum = 0 while @rownum < 5begin set @rownum = @rownum + 1 print cast(@rownum as char(1)) + ' ' + @custid select top 1 @custid=customerid from northwind.dbo.customers where customerid > @custidend
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.
