求需要游标来实现需求的sql例子

[plaimm]

用sql server 已经很长时间了，还没有用过游标，好像不管什么需求我好像都能用普通sql实现，希望哪位达人给几个只能用游标实现的例子，可能的话多给几个应用场合。好像有点贪心,^_^

[liangCK]

http://blog.csdn.net/wudiwushen/archive/2007/10/14/1824664.aspx
游标全面解说

[tim_spac]

游标是一种循环遍历的机制。在循环过程中，根据游标取出来的数据，结合业务逻辑(通常是一些复杂的业务逻辑)，进行相应的数据处理。
declare @id int, @flow_type int, @step_type int, user_id int

declare cur_flowStep cursor for
select id, flow_type, step_type, user_id
from flow_step
where over=0
open cur_flowStep
fetch next from cur_flowStep into @id, @flow_type, @step_type, @user_id
while @@fetch_status!=-1 begin
if @flow_type=1 and @step_type=1 begin
if dbo.fn_hasrule(@user_id,'审批')=1
insert into flow_step (flow_type, step_type, user_id) values (@flow_type, 2, dbo.fn_leaderOf(@user_id))
else
update flow_step set over=1 where id=@id
end else if @flow_type=1 and @step_type=2 begin
-- ...
end
fetch next from cur_flowStep into @id, @flow_type, @step_type, @user_id
end
close cur_flowStep
deallocate cur_flowStep

[plaimm]

游标的机制，语法我都知道，也写过，不过感觉没有必要，不用游标，我的需求也能实现，我是想知道一些不用游标就解决不了的问题

[planebus]

declare contact_cursor scroll cursor
for
select lastName,firstName from person.contact
order by lastName,firstName

--open cursor
open contact_cursor

--get data
fetch last from contact_cursor

fetch prior from contact_cursor

fetch absolute 2 from contact_cursor

fetch relative 3 from contact_cursor

fetch relative -2 from contact_cursor

--close cursor
close contact_cursor
--deallocate cursor
deallocate contact_cursor

[-狙击手-]

使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
   @message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR 
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor 
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " + 
      @au_fname + " " + @au_lname

   PRINT @message

   -- Declare an inner cursor based   
   -- on au_id from the outer cursor.

   DECLARE titles_cursor CURSOR FOR 
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   -- Variable value from the outer cursor

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

   IF @@FETCH_STATUS <> 0 
      PRINT "         <<No Books>>"     

   WHILE @@FETCH_STATUS = 0
   BEGIN
      
      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title
   
   END

   CLOSE titles_cursor
   DEALLOCATE titles_cursor
   
   -- Get the next author.
   FETCH NEXT FROM authors_cursor 
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------
 
----- Books by Author: Anne Ringer
         The Gourmet Microwave
         Is Anger the Enemy?
 
----- Books by Author: Albert Ringer
         Is Anger the Enemy?
         Life Without Fear

[tim_spac]

总结ing:
1) 处理复杂逻辑
-- 现在可以在中间件等业务层完成。
2) 输出复杂报表
-- 可以在前台应用中完成。
..(待续)

[wzy_love_sly]

比如一个结果集
想用每一条和上一条比较，你怎么做？

[JL99000]

我写的这段代码的作用是查看sysobjects表，取出其中的视图的名称给游标，遍历，根据视图名称，查看视图中记录的数目
declare @View_Name nvarchar(128)
declare @RowCount int
declare @sql nvarchar(4000)
declare @View_Details table(ViewName nvarchar(128),ViewRowCount int)

declare cur cursor for
select name from sysobjects where xtype='v'
open cur
fetch next from cur into @View_Name
while @@fetch_status=0
begin
set @sql='select count(1) from '+@View_Name
exec sp_executesql @sql,N'@RowCount int output',@RowCount output
insert @View_Details values(@View_Name,@RowCount)
fetch next from cur into @View_Name
end
close cur
deallocate cur

其实游标就相当于高级语言中的for语句，他的作用就是提供给我们一个遍历整个数据集的工具，通过他，我们就可以对结果集一个一个的进行操作