数据库 插入更新表内容的存储过程
时间：2021-07-01 10:21:17
帮助过：2人阅读
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
数据库 插入更新表内容的存储过程
标签：
