存储过程游标

 

临时表代替游标

废话不说，直接先上代码：

----不使用游标，使用临时表循环：更新没有配置作废章的流程
 DECLARE @temp TABLE
(
    requestid INT,
    zno NVARCHAR(10)
);

INSERT INTO @temp(requestid,zno)
SELECT requestid,zno FROM formtable_main_278 where zfz is null  
 

 DECLARE
    @rid AS INT,
    @zno AS NVARCHAR(50)

WHILE EXISTS(select requestid from @temp)
BEGIN
  select top 1 @rid = requestid from @temp 
  print '@rid is:' +cast(@rid as varchar(100))

    exec zpro_d203_back_action @rid
    DELETE FROM @temp WHERE requestid=@rid;
end

 

USE [XQPMU_ALL]
GO
/****** Object:  StoredProcedure [dbo].[pFrwBussinessType]    Script Date: 10/25/2018 16:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/*****************************
过程名称：pFrwBussinessType
目的：     获取所有流程各公司的业务类别
调用者：     工作流引擎
创建日期：2005-05-18        作  者：王世英
修改日期：            修改人：
修改内容：
输入参数：cCoID char(6),cModuleID varchar(3),cTypeName varchar(200)
输出参数：数据集
重要提示：存储过程调用
涉及表：     #tTmpBussinessType，#tTmpBussiness
讨论：     
*****************************/
ALTER     PROC [dbo].[pFrwBussinessType]
    @cCoID char(6),
    @cModuleID varchar(3),
    @cTypeName varchar(200)
AS

declare @iTypeID int            --类别标识
declare @cName varchar(200)        --流程名称
declare @cProc varchar(200)        --存储过程名称

create table #tTmpBussinessType(cName varchar(200))
create table #tTmpReturn(iTypeID int,cName varchar(200))
if @cTypeName is not null and @cTypeName <> ''
begin
    declare cProc cursor for
    select a.iProcessTypeID,b.cProcessTypeName,a.cBusinessTypeProc from tFrwProcessType a
    Left Join tFrwProcessCAndE b On b.cProcessTypeName = a.cProcessTypeName
    where b.cProcessTypeNameE = rtrim(@cTypeName)
    open cProc
    fetch next from cProc into @iTypeID,@cName,@cProc
    while(@@fetch_status=0)
    begin
        if @cProc is not null and @cProc <> ''
        begin
            insert into #tTmpBussinessType exec @cProc @cCoID
            insert into #tTmpReturn select @iTypeID,@cName+'-'+cName from #tTmpBussinessType
        end
        else
        begin
            insert into #tTmpReturn select @iTypeID,@cName
        end
        delete #tTmpBussinessType
        fetch next from cProc into @iTypeID,@cName,@cProc
    end
    close cProc
    deallocate cProc
end
else 
begin
    declare cProc cursor for
    select a.iProcessTypeID,c.cProcessTypeName,a.cBusinessTypeProc from tFrwProcessType a join tFrrUnit b on a.cMenuID = b.cUnitID
    Left Join tFrwProcessCAndE c On c.cProcessTypeName = a.cProcessTypeName
    where b.cModuleID like rtrim(@cModuleID)+'%'
    open cProc
    fetch next from cProc into @iTypeID,@cName,@cProc
    while(@@fetch_status=0)
    begin
        if @cProc is not null and @cProc <> ''
        begin
            insert into #tTmpBussinessType exec @cProc @cCoID
            --if(@@error<>0)
            --begin
                --raiserror ('运行类别存储过程 %s 出错',16,1,@cProc)
            --end
            insert into #tTmpReturn select @iTypeID,@cName+'-'+cName from #tTmpBussinessType
        end
        else
        begin
            insert into #tTmpReturn select @iTypeID,@cName
        end
        delete #tTmpBussinessType
        fetch next from cProc into @iTypeID,@cName,@cProc
    end
    close cProc
    deallocate cProc
end

select * from #tTmpReturn
return                    --[dbo].[pFrwBussinessType]

if exists (select * from sysobjects where id = object_id(N'[dbo].[pFrwCopyWorkFlow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop proc [dbo].[pFrwCopyWorkFlow]

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*************************************************************************************************************
    过程名称： [pFrwCopyWorkFlow]
    目    的： 
    调 用 者： 
    创建日期：     作    者: 杨林
    修改日期： 
    修改内容： 
        输入参数： 
    输出参数：  
    重要提示： 
        涉 及 表: 
                 1、
        讨    论：
        使用样例：exec dbo.pFrwCopyWorkFlow 150, '000001', 31, 'testName', '00000001'

        
*************************************************************************************************************/
create    proc dbo.pFrwCopyWorkFlow
(       
 @iProcessID                    int,
 @cCoID                            char(6),
 @iProcessTypeID                int,
 @cProcessName                    varchar(50),
 @cOperator                        char(8)
)
as

create table #tFrwProcess_temp 
(
   iProcessID           int                  identity,
   iProcessTypeID       int                  null,
   cCoID                char(6)              not null,
   cProcessName         varchar(50)          not null,
   gMapXML              image                null,
   cMemo                varchar(2000)        not null,
   dCreate              datetime             not null default getDate(),
   cOperator            char(10)             not null,
   iValidState          tinyint              null,
   cConfirmor           char(8)              null
)

create table #tFrwProcessNodes_temp
(
   iNodeID              int                  not null,
   iProcessID           int                  not null,
   iNodeType            tinyint              not null default 1,
   cNodeName            varchar(200)         not null,
   bFirstNode           int                  not null default 0,
   iYesNodeID           int                  not null default -1,
   iNoNodeID            int                  not null default -2,
   iSignRule            tinyint              not null default 1,
   iAutoTime            int                  not null default 0
)

create table #tFrwProcessActor_temp
(
   iID                  int                  identity,
   iNodeID              int                  not null,
   iType                tinyint              not null default 0,
   cHumanID             char(8)              null,
   iPositionID          int                  null,
   iRelation            tinyint              null,
   iRelativeID          int                  null,
   iConditionID         int                  null,
   cOperator            varchar(2)           null,
   nValue               numeric(20,2)        null
)

declare @baseNodeID int --变量 存储当前节点最大值
declare @d_value int --差值
declare @iProcessID_new int --存储新工作流标识， 这是系统产生的递增量

begin transaction

--新节点标识的起始值
select @baseNodeID = MAX(iNodeID)+1 from tFrwProcessNodes

--复制工作流
insert into #tFrwProcess_temp (iProcessTypeID, cCoID, cProcessName, gMapXML, cMemo, dCreate, cOperator, iValidState, cConfirmor)
select @iProcessTypeID, @cCoID, @cProcessName,gMapXML,cMemo, GETDATE(), @cOperator, 0, null 
from tFrwProcess 
where iProcessID = @iProcessID

--复制节点
insert into #tFrwProcessNodes_temp (iNodeID, iProcessID, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime)
select iNodeID, iProcessID, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime
from tFrwProcessNodes 
where iProcessID = @iProcessID order by iNodeID asc

--获取最大值与当前最小值 的差值
select top 1 @d_value = @baseNodeID - iNodeID from #tFrwProcessNodes_temp order by iNodeID

--复制参与人
insert into #tFrwProcessActor_temp (iNodeID, iType, cHumanID, iPositionID, iRelation, iRelativeID, cOperator, nValue)
select iNodeID+@d_value, iType, cHumanID, iPositionID, iRelation, iRelativeID+@d_value, cOperator, nValue
from tFrwProcessActor 
where iNodeID in ( select iNodeID from #tFrwProcessNodes_temp where iNodeType <> 2 ) --过滤掉条件节点

--将标识更新为新的标识,增量为差值
update #tFrwProcessNodes_temp set iNodeID = iNodeID + @d_value
--更新同意与不同意跳转节点
update #tFrwProcessNodes_temp set iYesNodeID = iYesNodeID + @d_value
where iYesNodeID > 0
update #tFrwProcessNodes_temp set iNoNodeID = iNoNodeID + @d_value
where iNoNodeID > 0

insert into tFrwProcess (iProcessTypeID, cCoID, cProcessName, gMapXML, cMemo, dCreate, cOperator, iValidState, cConfirmor)
select iProcessTypeID, cCoID, cProcessName, gMapXML, cMemo, dCreate, cOperator, iValidState, cConfirmor 
from #tFrwProcess_temp
if @@error <> 0 Goto PROBLEM

select @iProcessID_new = SCOPE_IDENTITY()

insert into tFrwProcessNodes (iNodeID, iProcessID, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime)
select iNodeID, @iProcessID_new, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime 
from #tFrwProcessNodes_temp
if @@error <> 0 Goto PROBLEM

insert into tFrwProcessActor (iNodeID, iType, cHumanID, iPositionID, iRelation, iRelativeID, iConditionID, cOperator, nValue)
select iNodeID, iType, cHumanID, iPositionID, iRelation, iRelativeID, iConditionID, cOperator, nValue 
from #tFrwProcessActor_temp
if @@error <> 0 Goto PROBLEM

--调试信息
update #tFrwProcessNodes_temp set iProcessID = @iProcessID_new
select * from #tFrwProcess_temp
select * from #tFrwProcessNodes_temp
select * from #tFrwProcessActor_temp

rollback transaction

goto exitSection

 PROBLEM:
  ROLLBACK TRANSACTION 
 exitSection:
 
 drop table #tFrwProcess_temp
 drop table #tFrwProcessNodes_temp
 drop table #tFrwProcessActor_temp
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

if exists (select * from sysobjects where id = object_id(N'[dbo].[pFrwChnageActor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop proc [dbo].[pFrwChnageActor]

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*************************************************************************************************************
    过程名称： [pFrwChnageActor]
    目    的： 
    调 用 者： 
    创建日期：     作    者: 
    修改日期： 
    修改内容： 
        输入参数： 
    输出参数：  
    重要提示： 
        涉 及 表: 
                 1、
        讨    论：
        使用样例：exec dbo.pFrwChnageActor 

        
*************************************************************************************************************/
create    proc dbo.pFrwChnageActor
(       
 @oldcHumanID                    char(8),
 @newcHumanID                    char(8),
 @cOperator                        char(8)
)
as

begin transaction

update tFrwProcessActor set cHumanID = @newcHumanID where cHumanID = @oldcHumanID
if @@error <> 0 Goto PROBLEM
update tFrwProcessInstanceDetail set cOperatorID = @newcHumanID WHERE cOperatorID = @oldcHumanID and iOperate = 0
if @@error <> 0 Goto PROBLEM

insert into tFrwProcessHumanChange (cChangeFront, cChangeAfter, cOperator, dCreate)
values(@oldcHumanID, @newcHumanID, @cOperator, GETDATE())
if @@error <> 0 Goto PROBLEM

commit transaction

goto exitSection

 PROBLEM:
  ROLLBACK TRANSACTION 
 exitSection:
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO