欢迎进入windows社区论坛，与300万技术人员互动交流 >>进入
14、游标
/*
* 声明游标:
* declare 游标名 cursor for t_sql;
*
*打开游标:
*open 游标名
*
*关闭游标:
*close 游标名
*
*删除游标:
*deallocate 游标名
*
*游标读取数据：
*fetch next from 游标名
*(或者)fetch first from 游标名
*/
--声明游标
declare deletetable cursor for
select ltrim(rtrim(name)) from sysobjects where name like '%000079'
--打开游标
open deletetable
--关闭游标
close deletetable
--删除游标
deallocate deletetable
--读取数据
fetch next from deletetable into @value1,@value2
--或则 fetch first from mycursor
--判断游标是否存在 deletetable:游标名
if cursor_status('global','deletetable')=-3 and cursor_status('local','deletetable')=-3
print '不存在'
else
print '存在'
create proc proc_ea
as
begin
declare eamcmt4 cursor for
select top 545 userid,createtime from ea_mcmt4 order by createtime desc
declare @userid nchar(50), @datetime smalldatetime, @id int
set @id = 560
open eamcmt4
while @@fetch_status = 0
begin
fetch next from eamcmt4 into @userid,@datetime
update ea set userid=@userid,[datetime]= @datetime where id=@id
set @id = @id + 1
end
close eamcmt4
deallocate eamcmt4
end
go
--清空日志
dump transaction dbname with no_log
--收缩数据库文件
dbcc shrinkfile('dazhou_log',1)
/*
*由此推导出sqlserver分页语句
*pagesize: 每页显示数据条数
*tablename：查询表名
*pageindex：分页索引(默认为1,即首页)
*pagecount: 总页数
*/
if pageindex > 0 and pageindex
begin
select top pagesize * from tablename tn where tn.id not in(
select top (pageindex-1)*pagesize tn.id from tablename tn order by tn.id asc)
order by tn.id asc
end
else
begin
select top pagesize * from tablename
end
[1] [2] [3]