给玩家加载邮件消息

最新推荐文章于 2024-06-24 16:28:00 发布

QiuNiao_TuDou

最新推荐文章于 2024-06-24 16:28:00 发布

阅读量407

点赞数

分类专栏： sql 文章标签： SQL

本文链接：https://blog.csdn.net/QiuNiao_TuDou/article/details/52685248

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

有时候在项目会遇见给玩家发送系统消息今天想了下可以用游标来实现。

如创建系统消息表和用户消息表

USE [QPAccountsDB]
GO

/* Object: Table [dbo].[ShortMsgInfos] Script Date: 2016/9/28 21:56:01 */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ShortMsgInfos](
[id] [int] IDENTITY(1,1) NOT NULL,
[State] [int] NOT NULL,
[Nullity] [int] NOT NULL,
[SenderID] [int] NOT NULL,
[SenderName] [int] NOT NULL,
[MsgInfo] nvarchar NOT NULL,
[SenderTime] [datetime] NOT NULL,
CONSTRAINT [PK_ShortMsgInfos] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

USE [QPAccountsDB]
GO

/* Object: Table [dbo].[ShortMsgUser] Script Date: 2016/9/28 22:02:38 */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/用户存储信息表*/

USE [QPAccountsDB]
GO

/* Object: Table [dbo].[ShortMsgUser] Script Date: 2016/9/28 22:02:38 */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ShortMsgUser](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MsgID] [int] NULL CONSTRAINT [DF_ShortMsgUser_MsgID] DEFAULT ((0)),
[UserID] [int] NOT NULL,
[Nullity] [int] NOT NULL,
[AddDate] [datetime] NOT NULL,
[State] [int] NULL CONSTRAINT [DF_ShortMsgUser_State] DEFAULT ((0)),
[GetRewardDate] [datetime] NULL,
[TaskCommetedDate] [datetime] NULL,
[ClientIP] nvarchar NULL
) ON [PRIMARY]

/***************************************/

先给系统消息表赋值

然后写一个游标

USE QPAccountsDB
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N’dbo.GSP_MB_ShortMsgUser’) and OBJECTPROPERTY(ID, N’IsProcedure’) = 1)
DROP PROCEDURE dbo.GSP_MB_ShortMsgUser
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

– 给玩家加载消息
CREATE PROC GSP_MB_ShortMsgUser
@dwUserID int –用户ID
WITH ENCRYPTION AS

– 属性设置
SET NOCOUNT ON

– 执行逻辑
BEGIN
declare @Nullity INT
declare @State INT
DECLARE @ID INT
Declare cur_ShortMsgInfo Cursor for SELECT id,State,Nullity from QPAccountsDB.dbo.ShortMsgInfos where Nullity=0
Open cur_ShortMsgInfo
FETCH NEXT FROM cur_ShortMsgInfo INTO @ID,@State,@Nullity
WHILE (@@FETCH_STATUS = 0)
BEGIN
if((select count(*) from QPAccountsDB.dbo.ShortMsgUser where MsgID=@ID and UserID=@dwUserID)=0)
BEGIN
INSERT INTO QPAccountsDB.dbo.ShortMsgUser
(MsgID,UserID,Nullity,AddDate,State,GetRewardDate,TaskCommetedDate,ClientIP)
VALUES
(@ID ,@dwUserID ,@Nullity,getdate(),@State
,getdate(),getdate()
,’127.0.0.1’)

     END
        --读取下一条消息
    FETCH NEXT FROM cur_ShortMsgInfo INTO @ID,@State,@Nullity
    END
    CLOSE cur_ShortMsgInfo
    DEALLOCATE cur_ShortMsgInfo

END

RETURN 0

游标可以在用户登录或者用户注册的时候调用

一边建议在存储成功中输出参数后加

如：

EXEC QPAccountsDB.dbo.GSP_MB_ShortMsgUser @UserID