SQLServer 实用语法大全
- WBOYOriginal
- 2016-06-07 15:51:401286browse
欢迎进入Windows社区论坛，与300万技术人员互动交流 >>进入 14、游标 /* * 声明游标: * DECLARE 游标名 CURSOR FOR T_SQL; * *打开游标: *OPEN 游标名 * *关闭游标: *CLOSE 游标名 * *删除游标: *DEALLOCATE 游标名 * *游标读取数据： *FETCH NEXT FROM 游标
欢迎进入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]
