sql之游标_sql 游标详解 csdn-优快云博客

本文链接：https://blog.youkuaiyun.com/WaiCyuyan/article/details/38317723

本文介绍了如何使用SQL脚本批量更新数据库中的工作流控制状态，包括服务和工作表单，涉及更新工作流控制状态、验证人和审核时间等关键信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare @cre_user_id nvarchar(20)                                               ---定义变量来保存***人
declare @cre_user_name nvarchar(20)                                       ---定义变量来保存***人名称
declare @cre_time datetime                                                             ---定义变量来保存***时间

declare @ser_id numeric(22,0)                                                   ---定义变量来保存ID号
declare cursor_service cursor for select service_id,create_user_id,create_time from tc_service where obj_type=51 and (iswfcontrolled IS NULL or iswfcontrolled=0)
open cursor_service
fetch next from cursor_service into @ser_id,@cre_user_id,@cre_time
WHILE @@FETCH_STATUS = 0
BEGIN
    select @cre_user_name=login_name from tc_user where user_id = @cre_user_id;
    update tc_service set iswfcontrolled=2,iverifystate=2,ivouchstate=2,cVerifier=@cre_user_name,audit_time=@cre_time where service_id=@ser_id;
    FETCH NEXT FROM cursor_service into @ser_id,@cre_user_id,@cre_time           ---这里的fetch语句被循环执行
END
close cursor_service                                                                                                                   ---关闭游标
deallocate cursor_service                                                                                                          ---删除游标

declare @work_id numeric(22,0)                                                   ---定义变量来保存ID号
declare cursor_worksheet cursor for select ws_id,create_user_id,create_time from tc_worksheet where iswfcontrolled IS NULL or iswfcontrolled=0;
open cursor_worksheet
fetch next from cursor_worksheet into @work_id,@cre_user_id,@cre_time
WHILE @@FETCH_STATUS = 0
BEGIN
    select @cre_user_name=login_name from tc_user where user_id = @cre_user_id;
    update tc_worksheet set iswfcontrolled=2,iverifystate=2,ivouchstate=2,cVerifier=@cre_user_name,audit_time=@cre_time where ws_id=@work_id;
    FETCH NEXT FROM cursor_worksheet into @work_id,@cre_user_id,@cre_time       ---这里的fetch语句被循环执行
END
close cursor_worksheet                                                           ---关闭游标
deallocate cursor_worksheet                                                       ---删除游标