对于那种 存在需要update的不存在需要insert的需求的时候,如果可以的话可以在某些操作的时候将数据初始化,这样可以使得需求变成update
例如:
在使用增加雇员属性的时效性道具的时候,可以用一般的做法是有使用的时候才将道具类型和到期时间插入到表中,等时间到了再删除此行数据,这样的话在再次使用的时候再插入数据一次循环会使得数据库的日志变大,并且在第一使用还未到期的时间再次使用道具会使得此过程变得复杂,代码如下:
DECLARE @EndTime4 datetime
DECLARE @EndTime5 datetime
DECLARE @EndTime6 datetime
DECLARE @EndTime7 datetime
SELECT @EndTime4=EndTime FROM TimeProperty WHERE Emid=@Emid AND Classid=4
SELECT @EndTime5=EndTime FROM TimeProperty WHERE Emid=@Emid AND Classid=5
SELECT @EndTime6=EndTime FROM TimeProperty WHERE Emid=@Emid AND Classid=6
SELECT @EndTime7=EndTime FROM TimeProperty WHERE Emid=@Emid AND Classid=7
IF @Classid=1
BEGIN
UPDATE Employee
SET EmPower=EmPower+5
WHERE Emid=@Emid
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
IF @EndTime4 IS NULL
BEGIN
INSERT TimeProperty(Roleid,Shopid,Emid,Classid,EndTime)----------------------------------------使用道具
SELECT @Roleid,@Shopid,@Emid,4,DATEADD(hh,24,GETDATE())
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
ELSE
BEGIN
UPDATE TimeProperty------------------------------------再次使用道具
SET EndTime=DATEADD(hh,24,@EndTime4)
WHERE Emid=@Emid AND Classid=4
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
END
IF @Classid=2
BEGIN
UPDATE Employee
SET EmCelerity=EmCelerity+5
WHERE Emid=@Emid
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
IF @EndTime5 IS NULL
BEGIN
INSERT TimeProperty(Roleid,Shopid,Emid,Classid,EndTime)
SELECT @Roleid,@Shopid,@Emid,5,DATEADD(hh,24,GETDATE())
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
ELSE
BEGIN
UPDATE TimeProperty
SET EndTime=DATEADD(hh,24,@EndTime5)
WHERE Emid=@Emid AND Classid=5
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
END
IF @Classid=3
BEGIN
UPDATE Employee
SET EmWisdom=EmWisdom+5
WHERE Emid=@Emid
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
IF @EndTime6 IS NULL
BEGIN
INSERT TimeProperty(Roleid,Shopid,Emid,Classid,EndTime)
SELECT @Roleid,@Shopid,@Emid,6,DATEADD(hh,24,GETDATE())
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
ELSE
BEGIN
UPDATE TimeProperty
SET EndTime=DATEADD(hh,24,@EndTime6)
WHERE Emid=@Emid AND Classid=6
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
END
IF @Classid=4
BEGIN
UPDATE Employee
SET EmCharm=EmCharm+5
WHERE Emid=@Emid
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
IF @EndTime7 IS NULL
BEGIN
INSERT TimeProperty(Roleid,Shopid,Emid,Classid,EndTime)
SELECT @Roleid,@Shopid,@Emid,7,DATEADD(hh,24,GETDATE())
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
ELSE
BEGIN
UPDATE TimeProperty
SET EndTime=DATEADD(hh,24,@EndTime7)
WHERE Emid=@Emid AND Classid=7
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
END
END
UPDATE a
SET a.Num=a.Num-1
FROM BLGame..Re_Role_BShop a JOIN Property..Property c ON a.PropertyId=c.PropertyId
WHERE c.PropertyName=CASE WHEN @Classid=1 THEN '力量丸' WHEN @Classid=2 THEN '敏捷丸' WHEN @Classid=3 THEN '智慧丸' WHEN @Classid=4 THEN '魅力丸' END
AND a.Roleid=@Roleid
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
ELSE
BEGIN
COMMIT tran
SET @Result=0
END
现在如果采用在雇佣雇员的时候就将道具的使用放入到表TimeProperty中让结束时间为null(此处用null是因为在刷新的时候null<=getdate()查出来为空,所以让雇员属性减掉一个值是没有影响的),当时间到了的时候也需将此字段变成null。
现在的需求就从需要判断insert还是update变成只需要update就可以了 ,如下:
UPDATE a
SET a.EmPower=a.EmPower+CASE WHEN b.Classid=4 AND b.EndTime IS NULL THEN CASE WHEN @Classid=1 THEN 5 ELSE 0 END ELSE 0 END ,
a.EmCelerity=a.EmCelerity+CASE WHEN b.Classid=5 AND b.EndTime IS NULL THEN CASE WHEN @Classid=2 THEN 5 ELSE 0 END ELSE 0 END ,
a.EmWisdom=a.EmWisdom+CASE WHEN b.Classid=6 AND b.EndTime IS NULL THEN CASE WHEN @Classid=3 THEN 5 ELSE 0 END ELSE 0 END ,
a.EmCharm=a.EmCharm+CASE WHEN b.Classid=7 AND b.EndTime IS NULL THEN CASE WHEN @Classid=4 THEN 5 ELSE 0 END ELSE 0 END
FROM Employee a JOIN TimeProperty b ON a.Emid=b.Emid
WHERE a.Emid=@Emid
/*UPDATE Employee
SET EmPower=EmPower+CASE WHEN @Classid=1 THEN 5 ELSE 0 END ,
EmCelerity=EmCelerity+CASE WHEN @Classid=2 THEN 5 ELSE 0 END,
EmWisdom=EmWisdom+CASE WHEN @Classid=3 THEN 5 ELSE 0 END,
EmCharm=EmCharm+CASE WHEN @Classid=4 THEN 5 ELSE 0 END
WHERE Emid=@Emid*/
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
UPDATE TimeProperty
SET EndTime=DATEADD(hh,24,CASE WHEN EndTime IS NULL THEN GETDATE() ELSE EndTime END )
WHERE Emid=@Emid AND Classid=CASE WHEN @Classid=1 THEN 4
WHEN @Classid=2 THEN 5
WHEN @Classid=3 THEN 6
WHEN @Classid=4 THEN 7 END
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
UPDATE a
SET a.Num=a.Num-1
FROM BLGame..Re_Role_BShop a JOIN Property..Property c ON a.PropertyId=c.PropertyId
WHERE c.PropertyName=CASE WHEN @Classid=1 THEN '力量丸' WHEN @Classid=2 THEN '敏捷丸' WHEN @Classid=3 THEN '智慧丸' WHEN @Classid=4 THEN '魅力丸' END
AND a.Roleid=@Roleid
IF @@ERROR >=1
BEGIN
ROLLBACK tran
SET @Result=3
RETURN
END
ELSE
BEGIN
COMMIT tran
SET @Result=0
END