存储过程中使用游标

    以下例子完整的表述了一个存储过程中使用游标、变量及整个存储过程建立、关闭操作的流程。
    特别提醒：不要深究sql语句内容，本文只讲述流程及使用方法。实际语句为工作中的环境直接复制而来并且删除了大部分内容，所以sql语句内容并不连贯。

--首先创建存储过程
CREATE PROCEDURE [dbo].[pro_CURSOR]
	--定义输入变量
    @bbqr varchar(8),
	@bbzr varchar(8),
	@xb   varchar(3),
	@comgrade int,
	@dwbm	varchar(6) 
as –关键字
begin  --存储过程包围开始

--定义统计用变量
declare
		@f1 int,@f2 int,@f3 int,@f4 int,@f5 int,@f6 int,@f7 int,@f8 int,
		@f9 int,@f10 int,@f11 int,@f12 int,@f13 int,@f14 int,@f15 int,@f16 int,
		@f17 int,@f18 int,@f19 int,@f20 int,@f21 int,@f22 int,@f23 int,@f24 int,
		@f25 int,@f26 int,@f27 int,@f28 int,@f29 int,@f30 int,@f31 int,
		@f32 int,@f33 int

--赋初值
select @f1=0,@f2=0,@f3=0,@f4=0,@f5=0,@f6=0,@f7=0,@f8=0,
		@f9=0,@f10=0,@f11=0,@f12=0,@f13=0,@f14=0,@f15=0,@f16=0,
		@f17=0,@f18=0,@f19=0,@f20=0,@f21=0,@f22=0,@f23=0,@f24=0,
		@f25=0,@f26=0,@f27=0,@f28=0,@f29=0,@f30=0,@f31=0,
		@f32=0,@f33=0
--定义临时变量
declare 
		@sq_bbzr varchar(10), 
		@ljlb varchar(5),
		@sjbm varchar(5),
		@sylb varchar(5),
		@rs int,
		@lb varchar(4),
		@ttrq varchar(8),
		@bhfs varchar(2)  
--临时变量赋值
set @sq_bbzr=dbo.get_day_add(@bbqr,-1)
set @sq_bbzr=dbo.get_day_add(@bbqr,-1)

--统计变量赋值
set @f1=(select count(*) from da_crj a join da_jbxx b on (a.bh=b.bh  and b.xb like @xb) where dbo.get_nl(@sq_bbzr,b.csrq)<18 and a.drrq<@bbqr and (a.ljrq='' or a.ljrq>=@bbqr))

--定义游标
declare cur_qnzj cursor for 
	select a.sylb,count(*)
		from da_crj a join da_jbxx b on a.bh=b.bh and b.xb like @xb
		where a.drrq between @bbqr and @bbzr and dbo.get_nl(a.drrq,b.csrq)<18
		group by a.sylb
open cur_qnzj    --打开游标
fetch cur_qnzj into @sylb,@rs  --将游标值存入指定变量中
while @@fetch_status = 0	  --0为正常
	begin
		set @f2=@f2+@rs  
		if @sylb<='1'    
			set @f3=@f3+@rs
		else
			begin
				set @f4=@f4+@rs 
				if @sylb='4' 
					set @f32=@f32+@rs
			end 
		fetch cur_qnzj into @sylb,@rs	--与上面的相同语句配合使用
	end 
close cur_qnzj			--关闭游标
deallocate cur_qnzj    --清空游标资源

--统计变量结果
select ' ' as dw,'' as bbsj,' ' as tbr,' ' as tbsj,@f1 as f1,@f2 as f2,@f3 as f3,@f4 as f4,@f5 as f5,@f6 as f6,@f7 as f7,@f8 as f8,@f9 as f9,@f10 as f10,@f11 as f11,@f12 as f12,@f13 as f13,@f14 as f14,@f15 as f15,@f16 as f16,@f17 as f17,@f18 as f18,@f19 as f19,@f20 as f20,@f21 as f21,@f22 as f22,@f23 as f23,@f24 as f24,@f25 as f25,@f26 as f26,@f27 as f27,@f28 as f28,@f29 as f29,@f30 as f30,@f31 as f31

--存储过程包围结束
end