笔记22 service broker例子
1 --service broker例子
2 --http://www.builder.com.cn/2007/0302/379424.shtml
3 USE master
4
5 GO
6
7 IF EXISTS ( SELECT *
8 FROM sys.databases
9 WHERE name = 'SB' )
10 DROP DATABASE SB
11
12 GO
13
14 CREATE DATABASE SB
15
16 GO
17
18 ALTER DATABASE SB
19
20 SET ENABLE_BROKER
21
22 GO
23
24 USE SB;
25
26 GO
27
28 CREATE TABLE Sales
29 (
30 SaleID INT IDENTITY(1, 1) ,
31 SaleDate SMALLDATETIME ,
32 SaleAmount MONEY ,
33 ItemsSold INT
34 );
35
36 GO
37
38 USE [SB]
39 CREATE MESSAGE TYPE [RecordSale] VALIDATION = NONE;
40
41 CREATE CONTRACT [SalesContract]
42
43 (
44
45 [RecordSale] SENT BY INITIATOR
46
47 );
48
49 GO
50 USE [SB]
51
52 CREATE QUEUE [SalesQueue];
53
54 CREATE SERVICE [SalesService] ON QUEUE [SalesQueue]([SalesContract]);
55
56 GO
57
58 CREATE QUEUE [RecordSalesQueue];
59
60 CREATE SERVICE [RecordSalesService] ON QUEUE [RecordSalesQueue];
61
62 GO
63
64
65 CREATE PROCEDURE usp_RecordSaleMessage
66 AS
67 BEGIN
68 SET NOCOUNT ON;
69 DECLARE @Handle UNIQUEIDENTIFIER;
70 DECLARE @MessageType SYSNAME;
71 DECLARE @Message XML
72 DECLARE @SaleDate DATETIME
73 DECLARE @SaleAmount MONEY
74 DECLARE @ItemsSold INT;
75 RECEIVE TOP (1) @Handle = conversation_handle, @MessageType = message_type_name, @Message = message_body FROM [SalesQueue];
76 IF ( @Handle IS NOT NULL
77 AND @Message IS NOT NULL
78 )
79 BEGIN
80 SELECT @SaleDate = CAST(CAST(@Message.query('/Params/SaleDate/text()') AS NVARCHAR(MAX)) AS DATETIME)
81 SELECT @SaleAmount = CAST(CAST(@Message.query('/Params/SaleAmount/text()') AS NVARCHAR(MAX)) AS MONEY)
82 SELECT @ItemsSold = CAST(CAST(@Message.query('/Params/ItemsSold/text()') AS NVARCHAR(MAX)) AS INT)
83 INSERT INTO Sales
84 ( SaleDate ,
85 SaleAmount ,
86 ItemsSold
87 )
88 VALUES ( @SaleDate ,
89 @SaleAmount ,
90 @ItemsSold
91 );
92 END
93 END
94 GO
95
96 ALTER QUEUE [SalesQueue] WITH ACTIVATION
97
98 (
99
100 STATUS = ON,
101
102 MAX_QUEUE_READERS = 1,
103
104 PROCEDURE_NAME = usp_RecordSaleMessage,
105
106 EXECUTE AS OWNER
107
108 );
109
110 GO
111
112
113 CREATE PROCEDURE usp_SendSalesInfo
114 (
115 @SaleDate SMALLDATETIME ,
116 @SaleAmount MONEY ,
117 @ItemsSold INT
118 )
119 AS
120 BEGIN
121 DECLARE @MessageBody XML
122 CREATE TABLE #ProcParams
123 (
124 SaleDate SMALLDATETIME ,
125 SaleAmount MONEY ,
126 ItemsSold INT
127 )
128 INSERT INTO #ProcParams
129 ( SaleDate ,
130 SaleAmount ,
131 ItemsSold
132 )
133 VALUES ( @SaleDate ,
134 @SaleAmount ,
135 @ItemsSold
136 )
137 SELECT @MessageBody = ( SELECT *
138 FROM #ProcParams
139 FOR
140 XML PATH('Params') ,
141 TYPE
142 );
143 DECLARE @Handle UNIQUEIDENTIFIER;
144 BEGIN DIALOG CONVERSATION @Handle FROM SERVICE
145 [RecordSalesService] TO SERVICE 'SalesService' ON CONTRACT
146 [SalesContract] WITH ENCRYPTION = OFF;
147 SEND ON CONVERSATION @Handle MESSAGE TYPE [RecordSale](@MessageBody);
148 END
149 GO
150
151
152
153
154
155
156
157 EXECUTE usp_SendSalesInfo '1/9/2005', 30, 90
158
159 --执行这个过程后，运行下面的SELECT语句看Sales表中是否插入一条记录。
160
161 SELECT * FROM Sales;
