• 数据库 插入更新表内容的存储过程


    gooliugle 原文 插入更新表内容的存储过程

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:      <xxxx>
    -- Create date: <2011-03-15>
    -- Description: <道具系统日报表统计>
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_PropsReport_DaySync]
    
    AS
    BEGIN
    
    --循环读取TbApps里的数据
    --声明游标
    DECLARE cursor_App CURSOR
    FOR
    SELECT Id FROM TbApps
    
    --打开游标
    OPEN cursor_App
    DECLARE @AppId int
    
    DECLARE @ReportDay VARCHAR(10), @OrderCount INT, @ActAmt INT,@PersonCount INT
    
    SELECT @ReportDay=CONVERT(VARCHAR(10),DATEADD(dd, -1, GETDATE()),120)
    
    --统计TbPropsOrder里的Status=2的所有已扣费数据
    FETCH NEXT FROM cursor_App INTO @AppId
    WHILE @@fetch_status=0
    BEGIN
        SET @ActAmt=0
        SET @OrderCount=0
        SET @PersonCount=0
        
        SELECT @ActAmt=ISNULL(SUM(ActPay),0), @OrderCount=COUNT(1),@PersonCount=COUNT(DISTINCT SndaId)
        FROM TbPropsOrder(NOLOCK)
        WHERE AppId=@AppId AND Status=2 
        AND LogTime>=@ReportDay AND LogTime<CONVERT(VARCHAR(10),GETDATE(),120)
        
        IF EXISTS(SELECT 1 FROM TbPropsOrderDayReport(NOLOCK) WHERE ReportDay=@ReportDay AND AppId=@AppId)
            UPDATE TbPropsOrderDayReport
            SET OrderCount=@OrderCount, ActAmt=@ActAmt ,PersonCount = @PersonCount
            WHERE ReportDay=@ReportDay AND AppId=@AppId
        ELSE
            INSERT TbPropsOrderDayReport(ReportDay, OrderCount, ActAmt, AppId,PersonCount)
            VALUES(@ReportDay, @OrderCount, @ActAmt, @AppId,@PersonCount)
            
        FETCH NEXT FROM cursor_App INTO @AppId
    END
    
    --关闭游标
    CLOSE cursor_App
    --释放游标
    DEALLOCATE cursor_App
    
    END
    GO
    
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
  • 相关阅读:
    time fly
    小论文初稿终于完成
    leetcode之Length of Last Word
    static关键字
    参数传递
    this关键字
    面向对象有三大特征
    空指针异常
    变量按数据类型分为
    构造方法
  • 原文地址:https://www.cnblogs.com/arxive/p/5959069.html
Copyright © 2020-2023  润新知