SSB(SQLservice Service Broker) 入门实例介绍

这篇文章主要为大家详细介绍了SSB(SQLservice Service Broker) 入门实例介绍，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

step 1：perpar database & data

代码如下:

use master
go
Create database Inventory
go
use Inventory
go
Create table inventory
(
 TitleId nvarchar(6) not null,
 Quantity int not null,
 primary key (TitleId,Quantity)
);
go

insert inventory values ('pc1001',100);insert inventory values ('pc1002',200);insert inventory values ('pc1003',300);insert inventory values ('pc1004',400);

首先创建信息类型

代码如下:

Create Message Type [//mark/v10/types/inventory]
Create Message Type [//mark/v10/types/inventoryresponse] 

代码如下:

Create contracts [//mark/v10/contracts/inventorycontract]
( 
 [//mark/v10/types/inventory] send by initiator,
 [//mark/v10/types/inventoryresponse] send by target 
); 

代码如下:

create queue [inventory client queue];
create service [//mark/v10/services/inventory client]
 on queue [inventory client queue]

 go
 --create inventory service
 create queue [inventory queue];
 create service [//mark/v10/services/inventory]
 on queue [inventory queue]
 (
 [//mark/v10/contracts/inventorycontract]
 );

代码如下:

use Inventory
go
--send message
begin transaction
 declare @dialog_id uniqueidentifier
 begin dialog conversation @dialog_id
 from service [//mark/v10/services/inventory client]
 to service '//mark/v10/services/inventory'
 on contract [//mark/v10/contracts/inventorycontract]
 with encryption = off;
 send on conversation @dialog_id message type [//mark/v10/types/inventory]
 (
 '<InventoryUpdate>
 <TitleId>pc1001</TitleId>
 <Quantity>102</Quantity>
 </InventoryUpdate>' 
 );
 commit transaction;

代码如下:

select conversation_handle, cast(message_body as xml) as conversation_body from [Inventory queue] 

setp 4: 从 inventory queue 中获取信息并更新数据

代码如下:

use inventory
go
begin transaction
 declare @dialog_id uniqueidentifier
 declare @message_body xml
 declare @amount int;
 declare @title nvarchar(128);

 receive
 @dialog_id = conversation_handle,
 @message_body = message_body 
 from [dbo].[Inventory queue]

 print 'handle is' + cast(@dialog_id as nvarchar(90))
 print 'message body is' + cast(@message_body as nvarchar(max) )
 if(@dialog_id is not null)
 begin 
 set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int');
 set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)');
 print 'a:'+cast(@amount as nvarchar(100))
 print 'b:'+@title;

 Update inventory set Quantity = @amount where TitleId = @title;
 end
 end conversation @dialog_id;
 commit transaction;

数据同时被更新了

【图片暂缺】step 5: 接下来做个自动监听功能，只要有信息发送到队列，相关队列，就可以自动执行功能.

首先建立个存储过程，（把receive 中的那段代码拿过来就可以了）

代码如下:

use Inventory
go
--create procedure to receive message and update table
create proc InventoryProc as

begin transaction declare @dialog_id uniqueidentifier declare @message_body xml declare @amount int; declare @title nvarchar(128); waitfor( receive @dialog_id = conversation_handle, @message_body = message_body from [dbo].[Inventory queue]),timeout 5000; print 'handle is' + cast(@dialog_id as nvarchar(90)) print 'message body is' + cast(@message_body as nvarchar(max) ) if(@dialog_id is not null) begin set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int'); set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)'); print 'a:'+cast(@amount as nvarchar(100)) print 'b:'+@title; Update inventory set Quantity = @amount where TitleId = @title; end end conversation @dialog_id; commit transaction; go

代码如下:

alter queue [inventory queue]
 with activation(
 status = on,
 procedure_name= InventoryProc,
 max_queue_readers=2,
 execute as self
 );

 go

PS： 我们定义了 两个队列 还有个 inventory client queue

这个队列保存的是成功或者error 提示。用receive 方法也可以提取出来。

代码如下:

select message_type_name from [Inventory client queue]

【图片暂缺】

以上的 message type service 等信息【图片暂缺】 如图所示，都在这里.

以上是SSB的入门实例，以后会再写一篇实际应用上的（以上的sql code 都是纯手工输入，可能还存在错误（虽然校验了两遍）,如果发生错误，对不起大家了。）

注：关于SSB(SQLservice Service Broker) 入门实例介绍的内容就先介绍到这里，更多相关文章的可以留意