帮我解释一下代码中前5行中用到游标的作用(实现了什么)及用法规则;谢谢!Declare orders CURSOR FOR select a.ordno ,b.PROGNAME,a.sampname from orders a ,SAMPLE_PROGRAMS b
where a.sp_code=b.sp_code and folderno=@folderno
OPEN orders --找到几个样品
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname --样品循环
WHILE @@FETCH_STATUS = 0
BEGIN
--样品名称
/****************************************/
set @i_ordno=convert(varchar(3),convert(int,( Substring(@ordno,10,3) ) ) )--转成数字型
if exists (select top 1 origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品名称:' and item=@sampname)
begin
select @origrec=origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品名称:' and item=@sampname
update #CDC_PRINTINFO set ordno=ordno+','+convert(varchar(3),@i_ordno) where origrec=@origrec
end
else
begin
insert into #CDC_PRINTINFO(dept,folderno,ITEMGROUP,ordno,item,orderby)
values (@dept,@folderno,'样品名称:',@i_ordno,@sampname,8)
end
--样品数量
/****************************************/
set @i_ordno=convert(varchar(3),convert(int,( Substring(@ordno,10,3) ) ) )--转成数字型
if exists (select top 1 origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品数量:' and item=@sampname)
begin
select @origrec=origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品数量:' and item=@sampname
update #CDC_PRINTINFO set ordno=ordno+','+convert(varchar(3),@i_ordno) where origrec=@origrec
end
else
begin
insert into #CDC_PRINTINFO(dept,folderno,ITEMGROUP,ordno,item,orderby)
values (@dept,@folderno,'样品数量:',@i_ordno,'1',8)
end
--检测项目和方法按科室分
Declare item CURSOR For select b.testcode,b.testno from ordtask a,TESTS b where a.TESTCODE = b.TESTCODE AND A.ordno=@ordno and A.dept=@dept
--Declare item CURSOR For select b.testcode,b.testno from ordtask a,TESTS b where a.ordno=@ordno AND a.TESTCODE = b.TESTCODE
OPEN item
FETCH FROM item INTO @testcode,@testno
WHILE @@FETCH_STATUS = 0
BEGIN
IF CHARINDEX('-',@testno)>1
select @testno=SUBSTRING(@testno,1,CHARINDEX('-',@testno)-1)
set @temptestno=@temptestno+@testno+'.'
/****************************************
检测方法
****************************************/
select @method=b.METHOD from TESTS a,TEST_METHODS b
where a.method=b.method and a.testcode=@testcode --and a.dept=@dept 该处不用部门过滤。上面已经过滤
if exists (select top 1 origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='检测方法:' and item=@testno+' - '+@method)
begin
select @origrec=origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='检测方法:' and item=@testno+' - '+@method
update #CDC_PRINTINFO set ordno=ordno+','+convert(varchar(3),@i_ordno) where origrec=@origrec
end
else
insert into #CDC_PRINTINFO(dept,folderno,ITEMGROUP,ordno,item,orderby)
values (@dept,@folderno,'检测方法:',@i_ordno,@testno+' - '+@method,2)
FETCH FROM item INTO @testcode,@testno
END
CLOSE item
DEALLOCATE item
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname --样品循环
END
CLOSE orders
DEALLOCATE orders end --while Exists(Select * from #dept)
where a.sp_code=b.sp_code and folderno=@folderno
OPEN orders --找到几个样品
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname --样品循环
WHILE @@FETCH_STATUS = 0
BEGIN
--样品名称
/****************************************/
set @i_ordno=convert(varchar(3),convert(int,( Substring(@ordno,10,3) ) ) )--转成数字型
if exists (select top 1 origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品名称:' and item=@sampname)
begin
select @origrec=origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品名称:' and item=@sampname
update #CDC_PRINTINFO set ordno=ordno+','+convert(varchar(3),@i_ordno) where origrec=@origrec
end
else
begin
insert into #CDC_PRINTINFO(dept,folderno,ITEMGROUP,ordno,item,orderby)
values (@dept,@folderno,'样品名称:',@i_ordno,@sampname,8)
end
--样品数量
/****************************************/
set @i_ordno=convert(varchar(3),convert(int,( Substring(@ordno,10,3) ) ) )--转成数字型
if exists (select top 1 origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品数量:' and item=@sampname)
begin
select @origrec=origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='样品数量:' and item=@sampname
update #CDC_PRINTINFO set ordno=ordno+','+convert(varchar(3),@i_ordno) where origrec=@origrec
end
else
begin
insert into #CDC_PRINTINFO(dept,folderno,ITEMGROUP,ordno,item,orderby)
values (@dept,@folderno,'样品数量:',@i_ordno,'1',8)
end
--检测项目和方法按科室分
Declare item CURSOR For select b.testcode,b.testno from ordtask a,TESTS b where a.TESTCODE = b.TESTCODE AND A.ordno=@ordno and A.dept=@dept
--Declare item CURSOR For select b.testcode,b.testno from ordtask a,TESTS b where a.ordno=@ordno AND a.TESTCODE = b.TESTCODE
OPEN item
FETCH FROM item INTO @testcode,@testno
WHILE @@FETCH_STATUS = 0
BEGIN
IF CHARINDEX('-',@testno)>1
select @testno=SUBSTRING(@testno,1,CHARINDEX('-',@testno)-1)
set @temptestno=@temptestno+@testno+'.'
/****************************************
检测方法
****************************************/
select @method=b.METHOD from TESTS a,TEST_METHODS b
where a.method=b.method and a.testcode=@testcode --and a.dept=@dept 该处不用部门过滤。上面已经过滤
if exists (select top 1 origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='检测方法:' and item=@testno+' - '+@method)
begin
select @origrec=origrec from #CDC_PRINTINFO where folderno=@folderno and dept=@dept and ITEMGROUP='检测方法:' and item=@testno+' - '+@method
update #CDC_PRINTINFO set ordno=ordno+','+convert(varchar(3),@i_ordno) where origrec=@origrec
end
else
insert into #CDC_PRINTINFO(dept,folderno,ITEMGROUP,ordno,item,orderby)
values (@dept,@folderno,'检测方法:',@i_ordno,@testno+' - '+@method,2)
FETCH FROM item INTO @testcode,@testno
END
CLOSE item
DEALLOCATE item
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname --样品循环
END
CLOSE orders
DEALLOCATE orders end --while Exists(Select * from #dept)
解决方案 »
- SQL2005缓存为什么过一段时间自动清理了????
- 为什么安装sql sever 2000的时候 选择不了 “服务器和客户端” 这一项呢?
- 设计数据库时候表之间的关系如何表示?
- 求助一句SQL Server的datediff时间差语句
- 请问如何在SQL中比较时间
- sql server中自定义聚合函数怎么编写?
- 全文检索问题,我现在有一 image 字段,用 二进制 保存 大字符串,如何进行全文检索.
- 求一更新语句
- 讨论一下 游标 和 sp_executesql的效率
- sql in使用问题,帮忙看下
- 用户‘sa’登录失败
- 您好,还是那个行列转换问题,原始文件是没有时分的,因为有些股票在某一天会停牌一小时或是干脆停牌,关于您说的自增列中间表我不太懂,希望能详细讲一讲
where a.sp_code=b.sp_code and folderno=@folderno
OPEN orders --找到几个样品
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname --样品循环
WHILE @@FETCH_STATUS = 0 声明游标
打开游标!
查询出一段的话然后 吧值赋给 @ordno,@PROGNAME,@sampname
OPEN orders -->打开游标,打开的结果集是 select a.ordno ,b.PROGNAME,a.sampname from orders a ,SAMPLE_PROGRAMS b where a.sp_code=b.sp_code and folderno=@folderno
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname -->提取一行数据,放到变量中保存。
Declare orders CURSOR FOR select a.ordno ,b.PROGNAME,a.sampname from orders a ,SAMPLE_PROGRAMS b
where a.sp_code=b.sp_code and folderno=@folderno
--打开游标
OPEN orders --找到几个样品
--取游标内第一条数据,并把它们赋值给@ordno,@PROGNAME,@sampname
FETCH FROM orders INTO @ordno,@PROGNAME,@sampname --样品循环
--循环使用游标,直到最后一条
WHILE @@FETCH_STATUS = 0
BEGIN