游标的简单使用
也不知是啥原因(听老总说,是因为在烧代码至芯片上时经常改动的原因),公司研制的设备所采集的数据(采集时间)显示在数据库中格式不
一致,为了统一时间格式,就特意写了个游标来改动,尽管执行效率低下,尤其对上百万条数据来说,更不用谈,但为了应付一下,不得不暂时这样做。代码如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
declare cur_Time cursor
for select rtrim(ltrim(CommTime)),id from CX_data where len(rtrim(ltrim(CommTime))) <>19--and ModuleID between 7000 and 7012
declare @temTime varchar(50)
declare @timT varchar(50)
declare @len int
declare @id int
open cur_Time
fetch next from cur_Time into @temTime, @id
while @@FETCH_STATUS =0
begin
--set @temTime = LTRIM(@temTime)
--set @temTime = Rtrim(@temTime)
-- 规范化月份
set @timT = SUBSTRING(@temTime,7,1)
if @timT ='-'
set @temTime = STUFF(@temTime,6,0,'0')
--规范化日
set @timT = SUBSTRING(@temTime,10,1)
if @timT =''
set @temTime = STUFF(@temTime,9,0,'0')
--规范化空格
set @timT = SUBSTRING(@temTime,12,1)
if @timT =''
set @temTime =STUFF(@temTime,11,2,'')
--规范化时
set @timT = SUBSTRING(@temTime,13,1)
if @timT =':'
set @temTime = STUFF(@temTime,12,0,'0')
--规范化分
set @timT = SUBSTRING(@temTime,16,1)
if @timT =':'
set @temTime = STUFF(@temTime,15,0,'0')
--规范化秒 */
if LEN(@temTime)<=19
begin
set @len = LEN(substring(@temTime,18,2))
if @len !=2
set @temTime = STUFF(@temTime,18,0,'0')
end
print @temTime+''+ Convert(varchar(20),@id)
update CX_data set CommTime = @temTime where id = @id
fetch next from cur_Time into @temTime,@id
end
close cur_Time
deallocate cur_Time