当前位置:Gxlcms > mysql > SQLServer实用语法大全

SQLServer实用语法大全

时间:2021-07-01 10:21:17 帮助过:50人阅读

欢迎进入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 <= pageCount

  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]

人气教程排行