游标的使用
sql中的游标是什么?怎样用呢?
Create Proc Pr_DeleteTable
as
declare @Table varchar(20)
declare cr_cursor cursor --1.定义游标
for select name from dbo.sysobjects where xtype='U' and status>0
--??????
open cr_cursor --2.打开游标
fetch From cr_cursor into @Table --3.提取游标
while @@fetch_status=0
begin
print @Table --执行打印操作
fetch next From cr_cursor into @Table --?????????
end;
close cr_cursor --4.关闭游标
deallocate cr_cursor --5.释放游标
as
declare @Table varchar(20)
declare cr_cursor cursor --1.定义游标
for select name from dbo.sysobjects where xtype='U' and status>0
--??????
open cr_cursor --2.打开游标
fetch From cr_cursor into @Table --3.提取游标
while @@fetch_status=0
begin
print @Table --执行打印操作
fetch next From cr_cursor into @Table --?????????
end;
close cr_cursor --4.关闭游标
deallocate cr_cursor --5.释放游标
declare cr_cursor cursor --1.定义游标 for select name from dbo.sysobjects where xtype='U' and status>0 --?????? 这里是获取记录 fetch next From cr_cursor into @Table --??这里是用变量@Table保存获取到的select 【name】 from dbo.sysobjects where xtype='U' and status>0 name的值 fetch next From cr_cursor into @Table--这句话的完整意思是 将游标移动到下一条记录并将获取到是name值赋值给变量@Table ---------------------------------------------------------------------- 给你一个例子 和说明 我看来几遍就学会游标了 下面是例子 --------------------------------------------------------------------- 定义游标 Declare MyCursor Cursor For Select Field1,Field2 From MyTable Where (Field1 Like '%123%') And (Field2 = 'qqq') And (Field3 Is Not Null) And ...... Group By Field1,Field2 For Read Only Open MyCursor 移动游标 fetch first from 游标 into 变量列表 取下一条 fetch next from 游标 into 变量列表 取第n条 fetch absolute n from 游标 into 变量列表 例子 日前,因工作需要累加某表里面的某字段的全部值, 比如有个表,内容如下 id,text 1,春花秋月何时了 2,往事知多少 3,小楼昨夜又春风 4,古国不堪回首月明中 ...... 其中id为系列号,text为文本内容,我想使用个sql语句,达到如下效果: 查询text列,查询的值累加,即查询结果显示如下: 春花秋月何时了 往事知多少 小楼昨夜又春风 古国不堪回首月明中 ... 用存储过程+游标实现,示例如下 ALTER PROCEDURE [dbo].[abc] -- Add the parameters for the stored procedure here @p1 int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @ttt varchar(100); declare @bbb varchar(10); set @ttt='' set @bbb='' declare mycur cursor for select meno from test where gid= @p1 open mycur fetch next from mycur into @bbb WHILE @@FETCH_STATUS = 0 BEGIN set @ttt=@ttt+@bbb fetch next from mycur into @bbb end close mycur select @ttt
整个文档需要注意的是,我做完了,但是就是一直没有打印出来一个字段下面所有的字符相加,最后才知道是设置的是变量没有
初始化,要set@莫某='' 最后才能出来
declare @huoqu varchar(20)
declare @quanbu nvarchar(500)
set @huoqu =''
set @quanbu =''
declare cr_cursor cursor
for select id from 单元 where left(id,1)=6 and right (id,1)=1 and id>6600
open cr_cursor
fetch next from cr_cursor into @huoqu
while @@fetch_status=0
begin
set @quanbu =@huoqu+'加'+@quanbu
fetch next from cr_cursor into @huoqu
end
close cr_cursor
deallocate cr_cursor
select @quanbu as '字段合计'
posted on 2010-07-27 22:37 永不言弃program 阅读(331) 评论(0) 编辑 收藏 举报