【SQL Server】Service Broker（SQL Server 内置的消息队列)——3.使用实例

前面我们已经学习了消息队列：

　现在可以考虑一个实际需求，有一张日志表。需要操作各个表格后都需要用到它。为了避免多个同时操作锁表，使用消息队列。

前提

日志表设置：

 1 --  日志表
 2 CREATE TABLE t_queue_message(
 3     id INT IDENTITY(1,1) PRIMARY KEY,        -- 关键字
 4     tableName NVARCHAR(255),                -- 表格名称
 5     fid NVARCHAR(255),                        -- 主表id
 6     operate NVARCHAR(255),                    -- 主表操作
 7     flag INT DEFAULT 0,                        -- 标识状态（0表示队列消息未处理：1表示已处理）
 8     create_time DATETIME,                    -- 队列消息创建时间
 9     operate_time DATETIME,                    -- 队列消息处理时间
10     resultdata NVARCHAR(MAX)                -- 队列消息处理结果
11 )

因为多个表格使用到日志表，所以新建了通用的存储过程。当其他过程需要新增、修改、删除表格表格 t_queue_message_operate 时，调用以下存储过程。

 1 -- =============================================
 2 -- Author:        <Author,,陆彦捷>
 3 -- Create date: <Create Date,,2025-02-21>
 4 -- Description:    <Description,,操作请求(表格操作日志)>
 5 -- =============================================
 6 CREATE PROCEDURE usp_t_queue_message_operate
 7     @tableName NVARCHAR(255)        -- 表格名称
 8     ,@id INT=0                            -- 主键
 9     ,@fid INT                            -- 被操作的表主键
10     ,@operate NVARCHAR(255)                -- 操作
11     ,@flag INT                            -- 标识
12     ,@resultdata NVARCHAR(MAX)=''            -- 返回消息
13     ,@type INT = 0                -- 类型： 0 :新增，1：修改：2：删除
14     
15 AS
16 BEGIN
17     BEGIN TRANSACTION;
18         IF @type IS NULL OR @type = 0
19         BEGIN 
20             -- 新增
21             INSERT INTO t_queue_message(tableName, fid, operate , flag,create_time)
22             VALUES(@tableName , @fid , @operate  ,0 , GETDATE())
23         END
24         ELSE IF @type = 1
25         BEGIN
26             UPDATE t_queue_message
27             SET flag = @flag , resultdata = @resultdata , operate_time = GETDATE()
28             WHERE id = @id
29         END
30         ELSE IF @type = 2
31         BEGIN
32             DELETE t_queue_message WHERE id = @id
33         END
34     IF @@ERROR <> 0
35     BEGIN
36         ROLLBACK TRANSACTION;
37     END
38     COMMIT TRANSACTION;
39 END
40 GO

步骤

1. 新建类型为XML的消息类型（如已存在，可跳过）。

1 CREATE MESSAGE TYPE [//AWD/myTest/msgType_request_xml] VALIDATION=WELL_FORMED_XML;
2 CREATE MESSAGE TYPE [//AWD/myTest/msgType_reply_xml] VALIDATION=WELL_FORMED_XML;

2. 新建约束。（如已存在，可跳过）

1 CREATE CONTRACT [//AWD/myTest/contract_default] (
2      [//AWD/myTest/msgType_request_xml] SENT BY INITIATOR,
3     [//AWD/myTest/msgType_reply_xml] SENT BY TARGET
4 );

3. 新建队列和服务。

1 -- 发送方方队列和服务(由于未指定约定名称，其他服务不可将此服务用作目标服务)
2 CREATE QUEUE queue_request;
3 CREATE SERVICE [//AWD/myTest/service_request]
4     ON QUEUE queue_request;
5 -- 接收方队列和服务
6 CREATE QUEUE queue_reply;
7 CREATE SERVICE [//AWD/myTest/service_reply]
8     ON QUEUE queue_reply
9     ([//AWD/myTest/contract_default]);

4. 新建发送消息的存储过程。将需要发送的表格参数合并转换为XML格式，并选定固定服务发送。

 1 ALTER PROCEDURE [dbo].[usp_dlhandle_request]
 2 (
 3     -- Add the parameters for the function here
 4     @tableName NVARCHAR(255)= ''        -- 表格名称
 5     ,@id INT=0                            -- 主键
 6     ,@fid INT = 0                        -- 被操作的表主键
 7     ,@operate NVARCHAR(255)= ''            -- 操作
 8     ,@flag INT     = 0                    -- 标识
 9     ,@resultdata NVARCHAR(MAX)=''            -- 返回消息
10     ,@type INT = 0                -- 类型： 0 :新增，1：修改：2：删除
11 
12 )
13 AS
14 BEGIN
15     -- 1. 基础定义
16     DECLARE @InitDlgHandle UNIQUEIDENTIFIER; -- 发送句柄
17     DECLARE @RequestMsg NVARCHAR(MAX);   -- 发送消息
18     SET @RequestMsg = '<id>' + CONVERT( varchar,@id )  +'</id>'
19         + '<tableName>' + @tableName + '</tableName>'
20         + '<fid>' + CONVERT(VARCHAR, @fid) + '</fid>'
21         + '<operate>' + @operate + '</operate>'
22         + '<flag>' + CONVERT(VARCHAR, @flag) + '</flag>'
23         + '<resultdata>' + @resultdata + '</resultdata>'
24         + '<type>' + CONVERT(VARCHAR,@type) + '</type>'
25 
26     BEGIN TRANSACTION;
27 
28     -- 2. 发送消息
29     BEGIN DIALOG @InitDlgHandle
30             FROM SERVICE
31             [//AWD/myTest/service_request]    -- 发送方服务请求
32             TO SERVICE
33             '//AWD/myTest/service_reply'    -- 接收方服务请求（注意要写正确）
34             ON CONTRACT
35             [//AWD/myTest/contract_default]    -- 约定
36             WITH
37                 ENCRYPTION = OFF;
38 
39     SEND ON CONVERSATION @InitDlgHandle
40             MESSAGE TYPE
41             [//AWD/myTest/msgType_request_xml]    -- 消息类型
42             (@RequestMsg);
43 
44     SELECT @RequestMsg AS SentRequestMsg;
45 
46     COMMIT TRANSACTION;
47 END

 5.新建接收消息的存储过程。当存在消息时，读取消息，将消息转换为参数后，调用存操作表格日志的存储过程，并进行下一次读取；当不存在消息时，跳出循环。

 1 ALTER PROCEDURE [dbo].[usp_dlhandle_reply]
 2 
 3 AS
 4 BEGIN
 5     WHILE (1=1)
 6     BEGIN
 7         -- 1. 接收消息
 8         DECLARE @RecvReplyMsg XML;            -- 返回消息
 9         DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;    -- 句柄
10 
11         BEGIN TRANSACTION;
12 
13         WAITFOR
14         ( RECEIVE TOP(1)
15             @RecvReplyDlgHandle = conversation_handle,
16             @RecvReplyMsg = message_body
17             FROM queue_reply
18         ), TIMEOUT 1000;
19         IF @@ROWCOUNT = 0 
20         BEGIN
21             PRINT '没有消息'
22             ROLLBACK TRANSACTION;
23             -- 跳出循环
24             BREAK;
25         END
26         
27         END CONVERSATION @RecvReplyDlgHandle;        -- 结束当前接收消息
28         -- 2.发返回获取的消息
29         DECLARE @tableName NVARCHAR(255)        -- 表格名称
30         ,@id INT=0                            -- 主键
31         ,@fid INT                            -- 被操作的表主键
32         ,@operate NVARCHAR(255)                -- 操作
33         ,@flag INT                            -- 标识
34         ,@resultdata NVARCHAR(MAX)=''            -- 返回消息
35         ,@type INT = 0                -- 类型： 0 :新增，1：修改：2：删除
36         SET @id = CONVERT(INT, CONVERT(VARCHAR, @RecvReplyMsg.query('/id/text()')))
37         SET @tableName = CONVERT(VARCHAR, @RecvReplyMsg.query('/tableName/text()') )
38         SET @fid = CONVERT(INT, CONVERT(VARCHAR, @RecvReplyMsg.query('/fid/text()') ))
39         SET @operate =CONVERT( VARCHAR, @RecvReplyMsg.query('/operate/text()'))
40         SET @flag =CONVERT(INT, CONVERT( VARCHAR, @RecvReplyMsg.query('/flag/text()')) )
41         SET @resultdata = CONVERT(VARCHAR, @RecvReplyMsg.query('/resultdata/text()'))
42         SET @type = CONVERT(INT, CONVERT(VARCHAR, @RecvReplyMsg.query('/type/text()')))
43         SELECT @id id , @fid fid, @operate operate, @flag flag, @resultdata resultdata, @type type
44         PRINT CONVERT(VARCHAR,@fid) + '/' + @tableName
45 
46         -- 3.新增/修改/删除详细
47         EXEC usp_t_queue_message_operate
48             @tableName = @tableName        -- 表格名称
49             ,@id  = @id                    -- 主键
50             ,@fid = @fid                -- 被操作的表主键
51             ,@operate=@operate            -- 操作
52             ,@flag= @flag                -- 标识
53             ,@resultdata= @resultdata    -- 返回消息
54             ,@type = @type                -- 类型： 0 :新增，1：修改：2：删除
55         COMMIT TRANSACTION;
56     END
57 END

 6.修改接收消息的队列，设置接收队列的存储过程，激活流程。

ALTER QUEUE queue_reply 
    WITH ACTIVATION(STATUS=ON,
    PROCEDURE_NAME=usp_dlhandle_reply,
    MAX_QUEUE_READERS = 10,
    EXECUTE AS SELF
    );

 接下来，我们在需要的操作队列的时候调用请求存储过程 usp_dlhandle_request ,就能自动按顺序处理消息队列了。

posted @ 2025-02-21 09:39  陆陆无为而治者  阅读(44)  评论(0)    收藏  举报