基于游标技术的复杂信息发布数据库解决方案(3)

DECLARE CEVENT CURSOR FAST_FORWARD

FOR

select cast(year(kbqs_bdtime) as varchar(4))+'-'+ cast(month(kbqs_bdtime) as varchar(4))+'-'+cast(day(kbqs_bdtime) as varchar(4))

from oa_kbqs where kbtz_id=@kbtz_id

OPEN CEVENT

FETCH CEVENT INTO @EVENTDATA --获得基本日期的信息字符串

  while @@fetch_status=0

    begin

      if @semi=1

        begin

        set @i=@i+1

        set @password=@password+'；'+'第'+cast(@i as varchar(5))+'期开班的密码是：'+CAST ( @EVENTDATA AS varchar(20) )

        insert into oa_user (user_name,user_code,user_degree)

        values(@plan_code,CAST ( @EVENTDATA AS varchar(20) ),5)

        end

      else

        begin

          set @i=@i+1

          set @password='第'+cast(@i as varchar(5))+'期开班的密码是：'+CAST ( @EVENTDATA AS varchar(20) )

          set @semi=1

        insert into oa_user (user_name,user_code,user_degree)

        values(@plan_code,CAST ( @EVENTDATA AS varchar(20) ),5)

        end

    fetch CEVENT into @EVENTDATA --游标向下移动

    end

close CEVENT

deallocate CEVENT

第四步：将以上信息进行拼接，形成短消息字符串，并嵌入标准的css格式。

set @short_mess='<p>'+@plan_name+'培训项目现在已经开班，共拟开办'+'<span class=ts>'+CAST (@q AS varchar(4)) +'</span>期。本次通知的详细内容已经在您的公告中可以查询到。<br>

<span class=ts>本次开班的用户名称是：'+@plan_code+'<br>

本次开班的用户密码是：'+@password+'</span><br>

收到本消息后，请尽快通知相关单位培训的同志，登陆网地址：http://www.ccetnet.net.cn 进行工作。'+'再次感谢您对我们工作的支持。</p></div><div  id=lk>'+@user_name+'<br>'+cast(year(getdate()) as varchar(4)) +'年'+cast(month(getdate()) as varchar(4))+'月'+cast(day(getdate()) as varchar(4))+'日</div>'insert into oa_message(Users_id,Userf_id,mess_topic,mess_content)select user_id ,@user_id,@kbtz_topic,'<div id=ch>'+user_name+'：</div><div id=nr><p>您好!</p>'+@short_mess

from oa_user

where user_degree=4

GO

[参考文献]

[1] 中国培训网《项目任务书》；《用户需求说明书》；《用户说明书》；《软件设计说明书》