|
--建时间表
CREATE TABLE [dbo].[TIME](
?[ID_] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
?[YEAR_] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
?[SEASON_] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
?[MONTH_] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
---插入数据
? declare @sqltext varchar(8000),@id varchar(100),@i int,@beginyear int,@endyear int,@month int,@quarter int,@lenth int
set? @month=1
set? @beginyear=2000
set? @endyear=2010
set? @lenth=(@endyear-@beginyear)*12+1
SELECT?? @i?? =? 1
? WHILE(@i<@lenth)
begin
?? if(@month<4)
set @quarter=1
?? if(3<@month and @month<7)
set @quarter=2
?? if(6<@month and @month<9)
set @quarter=3
?? if(9<@month and @month<13)
set @quarter=4
?set @id=convert(varchar(12),@beginyear)+convert(varchar(12),@quarter)
if(@month<10)
? set @id=@id+'0'
? set @id=@id+convert(varchar(12),@month)
?set @sqltext =convert(varchar(12),@id)+''','''+convert(varchar(12),(@month/13+@beginyear))+''',@quarter)+''',@month)
?if @sqltext<>''
begin
???? set @sqltext='insert into TIME(ID_,YEAR_,season_,MONTH_) values('''+@sqltext+''')';
print @sqltext
exec(@sqltext)
???? set @sqltext=''
end
??? select @i=@i+1
if(@month<13)
?? begin
??? select @month=@month+1
?? end
if(@month=13)
??? begin
??? select @month=1
??? select @beginyear=@beginyear+1
??? end
end
?
---使用游标
?CREATE PROCEDURE updateCursor as
??? BEGIN
???????? declare @id varchar(20),
???????????????? @month varchar(20),
???????????????? @season varchar(20)
???????? declare cur1 CURSOR FOR SELECT ID_,month_ FROM? TIME? --声明游标变量
???? OPEN cur1??? --打开游标
???????? FETCH cur1 INTO @id,@month? --将游标里的ID_,month_赋给变量
???? WHILE (@@fetch_status=0) ---@@fetch_status 指针：0?? FETCH?? 语句成功;1?? FETCH?? 语句失败或此行不在结果集中;2?? 被提取的行不存在。
begin
if(@month='1' or @month='2' or @month='3')?? set @season='第一季度'
if(@month='4' or @month='5' or @month='6')?? set @season='第二季度'
if(@month='7' or @month='8' or @month='9')?? set @season='第三季度'
if(@month='10' or @month='11' or @month='12')?? set @season='第四季度'
?update? TIME? set? season_=@season? where ID_=@id
? FETCH cur1 INTO @id,@month?? --下一个
????? END???????
??? CLOSE cur1??? --关闭游标
deallocate cur1??? --释放游标引用
END
--执行exec updateCursor
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
