导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

plaimm 2008-01-14 02:38:46
用sql server 已经很长时间了,还没有用过游标,好像不管什么需求我好像都能用普通sql实现,希望哪位达人给几个只能用游标实现的例子,可能的话多给几个应用场合。好像有点贪心,^_^
...全文
57 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
JL99000 2008-01-14
我写的这段代码的作用是查看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语句,他的作用就是提供给我们一个遍历整个数据集的工具,通过他,我们就可以对结果集一个一个的进行操作
回复
wzy_love_sly 2008-01-14
比如一个结果集
想用每一条和上一条比较,你怎么做?
回复
tim_spac 2008-01-14
总结ing:
1) 处理复杂逻辑
-- 现在可以在中间件等业务层完成。
2) 输出复杂报表
-- 可以在前台应用中完成。
..(待续)
回复
-狙击手- 2008-01-14
使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。

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

回复
planebus 2008-01-14

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
回复
plaimm 2008-01-14
游标的机制,语法我都知道,也写过,不过感觉没有必要,不用游标,我的需求也能实现,我是想知道一些不用游标就解决不了的问题
回复
tim_spac 2008-01-14
游标是一种循环遍历的机制。在循环过程中,根据游标取出来的数据,结合业务逻辑(通常是一些复杂的业务逻辑),进行相应的数据处理。
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
回复
liangCK 2008-01-14
http://blog.csdn.net/wudiwushen/archive/2007/10/14/1824664.aspx
游标全面解说
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告