首先，先看一下简单的游标，此游标在循环一个表，然后我们看一下如何不使用游标来达到相同的目标。
使用游标：
if exists (select * from sysobjects where name = N'prcCursorExample')
drop procedureprcCursorExample
go
CREATE PROCEDURE prcCursorExample
AS
/*
**put your comment here
**
*/
SET NOCOUNT ON
-- declare all variables!
DECLARE @iRowId int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)
-- declare the cursor
DECLARE Customer CURSOR FOR
SELECT iRowId,
vchCustomerNmbr,
vchCustomerName
FROM CustomerTable
OPEN Customer
FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
-- This is where you perform your detailed row-by-row
-- processing.
-- Get the next row.
FETCH Customer INTO@iRowId,
@vchCustomerNmbr,
@vchCustomerName
END
CLOSE Customer
DEALLOCATE Customer
RETURN
不使用游标：
if exists (select * from sysobjects where name = N'prcLoopExample')
drop procedureprcLoopExample
go
CREATE PROCEDURE prcLoopExample
AS
/*
**put your comment here
*/
SET NOCOUNT ON
-- declare all variables!
DECLARE @iReturnCode int,
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)
@chProductNumber nchar(30)
-- Initialize variables!
SELECT @iLoopControl= 1
SELECT @iNextRowId = MIN(iRowId)
FROM CustomerTable
-- Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT 'No data in found intable!'
RETURN
END
-- Retrieve the first row
SELECT @iCurrentRowId = iRowId,
@vchCustomerNmbr =vchCustomerNmbr,
@vchCustomerName =vchCustomerName
FROM CustomerTable
WHERE iRowId =@iNextRowId
-- start the main processing loop.
WHILE @iLoopControl= 1
BEGIN
-- This is where you perform your detailed row-by-row
-- processing.
-- Reset looping variables.
SELECT @iNextRowId = NULL
-- get the next iRowId
SELECT @iNextRowId = MIN(iRowId)
FROM CustomerTable
WHERE iRowId> @iCurrentRowId
-- did we get a valid next row id?
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @iCurrentRowId = iRowId,
@vchCustomerNmbr = vchCustomerNmbr,
@vchCustomerName = vchCustomerName
FROM CustomerTable
WHERE iRowId = @iNextRowId
END
RETURN
我们现在来看一下上面的游标，一般来说，为了性能的因素，我们的表上都有一个类似于RowID的列，此列可以被用来做循环，并得到相关的数据。一般来说此列是IDENTITY列，主键并有clustered索引。
但是很多情况下，我们的表中并不包括可以被用来循环的行ID，比如，可能在一个具有uniqueindentifier属性的列上创建了主键索引，这时候可以为这个表增加一个自增列并创建相应的索引，来实现此功能。
上面例子使用了MIN函数和”>” 来得到下一行我们需要的数据，当然我们也可以使用MAX函数然后使用”<”达到相同的功能。
例子：
SELECT @iNextRowId =MAX(iRowId)
FROM CustomerTable
WHERE iRowId <@iCurrentRowId
有一个比较重要的地方需要注意： 要在取得下一次要循环的行ID之前设置此ID为NULL，是因为当此循环实现所有的行循环后SELECT语句并不会为此ID设置为NULL，从而造成一个死循环。当循环变量为NULL后，意味着这个循环已经完成了它需要实现的功能，此时我们可能使用BREAK来退出WHILE循环，当然也有其它的途径来推出循环。
你可以在上面的存储过程中的如下注释处加上自己的基于行的操作。
-- This is where you perform your detailed row-by-row
-- processing.
可以看出，基于行的操作对性能非常有影响。举例来说，如果你有一个非常复杂的任务需要进行嵌套的循环，此时你会使用嵌套的游标，内层的游标根据外层游标的条件进行相应的操作，这时候如果我们使用游标来进行处理的话对服务器会有非常大的压力。
例子：
if exists (select * from sysobjects where name = N'prcNestedLoopExample')
drop procedureprcNestedLoopExample
go
CREATE PROCEDURE prcNestedLoopExample
AS
/*
**put your comment here
*/
SET NOCOUNT ON
-- declare all variables!
DECLARE @iReturnCode int,
@iNextCustRowId int,
@iCurrentCustRowId int,
@iCustLoopControl int,
@iNextProdRowId int,
@iCurrentProdRowId int,
@vchCustomerName nvarchar(255),
@chProductNumber nchar(30),
@vchProductName nvarchar(255)
-- Initialize variables!
SELECT @iCustLoopControl =1
SELECT @iNextCustRowId =MIN(iCustId)
FROM Customer
-- Make sure the table has data.
IF ISNULL(@iNextCustRowId,0) = 0
BEGIN
SELECT 'No data in found intable!'
RETURN
END
-- Retrieve the first row
SELECT @iCurrentCustRowId = iCustId,
@vchCustomerName =vchCustomerName
FROM Customer
WHERE iCustId =@iNextCustRowId
-- Start the main processing loop.
WHILE@iCustLoopControl = 1
BEGIN
-- Begin the nested(inner) loop.
-- Get the first product id for current customer.
SELECT @iNextProdRowId = MIN(iProductId)
FROM CustomerProduct
WHERE iCustId= @iCurrentCustRowId
-- Make sure the product table has data for
-- current customer.
IF ISNULL(@iNextProdRowId,0) = 0
BEGIN
SELECT 'No products found forthis customer.'
END
ELSE
BEGIN
-- retrieve the first full product row for
-- current customer.
SELECT @iCurrentProdRowId = iProductId,
@chProductNumber = chProductNumber,
@vchProductName = vchProductName
FROM CustomerProduct
WHERE iProductId = @iNextProdRowId
END
WHILE ISNULL(@iNextProdRowId,0) <> 0
BEGIN
-- Do the inner loop row-level processing here.
-- Reset the product next row id.
SELECT @iNextProdRowId = NULL
-- Get the next Product id for the current customer
SELECT @iNextProdRowId =MIN(iProductId)
FROM CustomerProduct
WHERE iCustId =@iCurrentCustRowId
AND iProductId> @iCurrentProdRowId
-- Get the next full product row for current customer.
SELECT @iCurrentProdRowId = iProductId,
@chProductNumber = chProductNumber,
@vchProductName = vchProductName
FROM CustomerProduct
WHERE iProductId = @iNextProdRowId
END
-- Reset inner loop variables.
SELECT @chProductNumber = NULL
SELECT @vchProductName = NULL
SELECT @iCurrentProdRowId = NULL
-- Reset outer looping variables.
SELECT @iNextCustRowId = NULL
-- Get the next iRowId.
SELECT @iNextCustRowId = MIN(iCustId)
FROM Customer
WHERE iCustId > @iCurrentCustRowId
-- Did we get a valid next row id?
IF ISNULL(@iNextCustRowId,0) = 0
BEGIN
BREAK
END
-- Get the next row.
SELECT @iCurrentCustRowId = iCustId,
@vchCustomerName = vchCustomerName
FROM Customer
WHERE iCustId = @iNextCustRowId
END
RETURN
在上面的例子中，我们从一个表中进行循环，对于每个ID，我们从再从产品表中得到客户的相关产品信息。
SQL Server游标提供了一下非常有用并且强大的功能来实现基于行的操作，但是这种强大功能是依性能为代价的。上面我们列举了一种不使用游标来实现类似于游标。这在很多场合可以有效提高性能。
2304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
