Processing Sequentially Through a Set of Records

原创 2004年10月29日 01:02:00
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.

版权声明:本文为博主原创文章,未经博主允许不得转载。

NOTE FOR PTA WEEK(小题)

WEEK2 判断 1-1 For a sequentially stored linear list of length NN, the time complexities for delet...
  • xiaobai_f
  • xiaobai_f
  • 2017年01月08日 21:49
  • 983

OCP-1Z0-053-V12.02-392题

392.To clean up old records that are in a Flashback Data Archive and are past the retention period, ...
  • rlhua
  • rlhua
  • 2013年10月08日 14:34
  • 3557

视频推荐:Dimensions - A walk through mathematics

    刚在sdyy那儿看到了这个好东西。影片Dimensions长约2个小时,共分为9章,谈论了维度、射影、复数等有趣的数学话题。下面是一个4分钟长的预告片。完整的视频可以在这里下载。 ...
  • matrix67
  • matrix67
  • 2008年07月10日 03:34
  • 364

Oracle 修改字符集

oracle安装的时候,根据安装推荐,选了字符集:AL32UTF8, 之后安装了PLSQL Developer,打开就提示以下信息:     Database characte...
  • daxiang52
  • daxiang52
  • 2016年01月22日 16:04
  • 150

Codeforces Round #346 (Div. 2) 解题报告

Codeforces Round #346 (Div. 2) 解题报告
  • loy_184548
  • loy_184548
  • 2016年04月08日 15:36
  • 467

Android总结之Animation

Android中存在三种动画,分别为Tween动画、Frame动画以及属性动画。 通过对比Tween动画与属性动画的一些不同点,加深对动画Api的记忆: 1、每种动画都可以通过代码或者xml实现 ...
  • peidonghui
  • peidonghui
  • 2015年07月11日 17:27
  • 1020

poj 3041 Asteroids 二分图的最大匹配==最小覆盖点。

题目的意思是在一个网格中有若干个点,每一次可以一下子清楚一行或者一列,问多少次可以讲网格中的点全部清除。    分析:将行做表看作一个集合的点,列坐标看作一个集合的点,每个点就连接两个集合的边,求出最...
  • forpro_yang
  • forpro_yang
  • 2011年08月01日 15:03
  • 1084

Kafka 学习笔记(2) - Topic 和 Log

A topic is a category or feed name to which messages are published. 每个Partition都是一个排序的和不可变的消息队列,新消...
  • jlaky
  • jlaky
  • 2015年10月16日 11:17
  • 753

transaction coordinator (increase MaxNoOfConcurrentOperations)' from ndbcluster

mysql> TRUNCATE TABLE `user_ourproperty`; ERROR 1205 (HY000): Lock wait timeout exceeded; try restar...
  • bayaci
  • bayaci
  • 2007年09月08日 23:10
  • 2361

A Walk Through the Forest(最短路径+DFS)

A Walk Through the Forest Time Limit: 2000/1000 MS (Java/Others) Memory Limit: 65536/32768 ...
  • qq_33096883
  • qq_33096883
  • 2017年02月05日 13:45
  • 421
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Processing Sequentially Through a Set of Records
举报原因:
原因补充:

(最多只允许输入30个字)