【SQL Server学习笔记】T-SQL中的流程控制、游标

来源：互联网发布：iphonep图软件推荐编辑：程序博客网时间：2024/05/17 22:28

1、流控制

--一直到11:07:00才运行--waitfor time '11:07:00'declare @temp table(schematable varchar(100))declare @tablename varchar(100)insert into @temp(schematable)select s.name + '.' + t.namefrom sys.tables tinner join sys.schemas s        on t.schema_id = s.schema_idwhere t.type_desc = 'User_table'--while循环结构while (select COUNT(*) from @temp ) > 0beginselect top (1) @tablename = schematablefrom @tempexec sp_spaceused @tablename;  --输出当前数据库中每个表的空间使用情况delete from @tempwhere schematable = @tablename--if-else分支结构if( (select COUNT(*) from @temp)=0 )begin        --延迟10秒    waitfor delay '00:00:10'    exec sp_spaceused;         --输出当前数据库的空间使用情况        break;    end    else    begin        continue;    endend

2、游标

set nocount ondeclare @session_id smallint--1.定义游标declare session_cur cursorlocal                     --作用范围：局部游标(LOCAL,GLOBAL)forward_only              --滚动方式：向前(FORWARD_ONLY,SCROLL)fast_forward              --获取数据方式:快速向前(STATIC,KEYSET,DYNAMIC,FAST_FORWARD)read_only                 --只读 (READ_ONLY,SCROLL_LOCKS,OPTIMISTIC)for select session_id    from sys.dm_exec_requests    where status in ('runnable','sleeping','running')          and session_id >50--2.打开游标open session_cur--3.取下一条fetch next from session_cur into @session_id--4.开始循环while @@FETCH_STATUS = 0beginprint 'spid #:' + str(@session_id);exec('dbcc outputbuffer(' + @session_id + ')') ;fetch next from session_curinto @session_idendclose session_cur       --5.关闭游标deallocate session_cur  --6.释放游标占用的资源