sqlserver 游标

游标使用

 

--新建ERP临时表
if object_id('tempdb..#ERP_interface') is not null 
     drop table #ERP_interface
    
--将ERP中间表，写入条码系统临时表
select * into #ERP_interface  from 
openquery(SYN_ERP, 'select * from FYG.FYG_INV_TRANS_INT_TEMP st ')
WHERE TS>'2016-01-01'

--select * from #ERP_interface

--新建条码临时表
if object_id('tempdb..#BarCode_interface') is not null 
     drop table #BarCode_interface

--将oracleinterface状态为1的数据，写入条码系统临时表

select * into #BarCode_interface from [WMS_BarCode_V10_SH].[dbo].[OracleInterface] 
where status<>2 and operatetime>'2016-01-01' and status=1

--select * from #BarCode_interface

DECLARE @BarcodeId varchar(255)
DECLARE @TranceNo varchar(255)

DECLARE My_Cursor CURSOR                             --定义游标
FOR (SELECT id FROM #BarCode_interface)                 --查出需要的集合放到游标中
OPEN My_Cursor;                                      --打开游标
FETCH NEXT FROM My_Cursor INTO @BarcodeId;           --读取第一行数据
WHILE @@FETCH_STATUS = 0
    BEGIN

    UPDATE [WMS_BarCode_V10_SH].[dbo].[LabelTransactionInfo]
       SET [Status] = 1
     WHERE transactionid=@BarcodeId

        select @TranceNo=TraceNo from #BarCode_interface where id=@BarcodeId;
        
    UPDATE [WMS_BarCode_V10_SH].[dbo].[BizTransactionInfo]
       SET [Status] = 2      
     WHERE [WMS_BarCode_V10_SH].[dbo].[BizTransactionInfo].Traceno=@TranceNo
        
    if exists (select * from #ERP_interface where codebar_id=@BarcodeId and transaction_reference=@TranceNo)
            begin
                DECLARE @ERRORMESSGAE varchar(255)
                DECLARE @STATUS NVARCHAR(255)
                select @STATUS=[STATUS],@ERRORMESSGAE=ERROR_EXPLANATION from #ERP_interface where codebar_id=@BarcodeId and transaction_reference=@TranceNo
                IF(@STATUS IS NULL OR @STATUS='I')
                    PRINT 'IS DOING'
                ELSE
                    UPDATE [WMS_BarCode_V10_SH].[dbo].[OracleInterface] SET STATUS=-1,errormessage=@ERRORMESSGAE WHERE ID=@BarcodeId;
            end
        else
            BEGIN
                UPDATE [WMS_BarCode_V10_SH].[dbo].[OracleInterface] SET STATUS=2 WHERE ID=@BarcodeId;
            END       
            
        FETCH NEXT FROM My_Cursor INTO @BarcodeId;
    END
CLOSE My_Cursor;                                     --关闭游标
DEALLOCATE My_Cursor;                                 --释放游标