MSSQL之二十四 实现服务器代理
【摘要】
/*AdventureWorks.Inc的管理层想知道在一年的任何点的确切的年销售表以帮助它们计划未来的策略。集合年销售数据是在SalesDB数据库的SalesDetails表中维护的.SalesOrderHeader:包含常规或父级销售订单信息;与销售订单关联的特定产品存储在 SalesOrderDetail 表中。SalesOrde...
-
/*
-
AdventureWorks.Inc的管理层想知道在一年的任何点的确切的年销售表以帮助它们计划未来的策略。
-
集合年销售数据是在SalesDB数据库的SalesDetails表中维护的.
-
-
SalesOrderHeader:包含常规或父级销售订单信息;与销售订单关联的特定产品存储在 SalesOrderDetail 表中。
-
SalesOrderDetail:包含与特定销售订单关联的各个产品。一个销售订单可以订购多个产品。每个销售订单的常规
-
或父级信息存储在 SalesOrderHeader 中。每个订购的产品或子级信息存储在 SalesOrderDetail 中。
-
-
销售详情被存储在AdventureWorks数据库的SalesOrderHeader和SalesOrderDetail表中.为了保持
-
年销售数据更新,你需要确保无论何时任何订单被处理和在AdventureWorks数据库中交货日期被更新的
-
时候,订单的总钱数,存储在SalesOrderHeader表的SubTotal列中,应该被添加到SalesDB数据库的总年销售中.
-
*/
-
--SalesDB数据库应该在服务器上,这个数据库被用于产生报告.
-
/*
-
解决方案:需要在服务器上实现服务代理.为了实现服务代理，你需要完成如下任务:
-
1.创建一个服务程序。
-
2.创建消息类型、协议、队列和服务对象
-
3.在表上创建触发器
-
4.验证功能
-
*/
-
--select count(*) from Sales.SalesOrderHeader 31465行
-
--Select count(*) from Sales.SalesOrderDetail 121317行
-
--1.创建SalesDB
-
CREATE DATABASE SalesDB;
-
GO
-
drop database SalesDB
-
USE SalesDB
-
go
-
CREATE TABLE SalesDetails--年销售详情表
-
(
-
Year varchar(4),--年
-
TotalSales money--年销售额
-
)
-
INSERT INTO SalesDetails VALUES ('2006',25000)--插入数据
-
use SalesDB
-
go
-
--建立Robert的登录名，使用SQL SERVER验证模式
-
sp_addlogin 'Robert','niit#1234','SalesDB'
-
CREATE USER Robert FOR LOGIN Robert
-
--登录名：到SQL SERVER2005服务器
-
--角色:数据库的职权分类，相当于现实生活中的职位
-
--用户：针对数据库来说
-
-- to grant permission
-
EXEC sp_addrolemember 'db_owner', 'Robert'
-
-
-- to give ownership of teh database to the sa
-
-
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
-
ALTER DATABASE SalesDB SET TRUSTWORTHY ON
-
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [SA];--修改授权，给sa
-
ALTER AUTHORIZATION ON DATABASE::[SalesDB] TO [SA];
-
-
-- to crate master encryption key
-
USE AdventureWorks--创建数据库主密钥。
-
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'niit#1234'
-
USE SalesDB
-
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'niit#1234'
-
select * FROM SalesDetails
-
--使服务代理可用
-
use AdventureWorks
-
go
-
alter database AdventureWorks set Enable_Broker--
-
go
-
use SalesDB
-
go
-
alter database SalesDB set Enable_broker
-
go
-
--先让某一个触发器停用,基于SalesOrderHeader表
-
USE AdventureWorks
-
GO
-
DISABLE TRIGGER SalesOrderHeader.uSalesOrderHeader ON
-
Sales.SalesOrderHeader
-
--创建存储过程,以实现自动化的服务代理
-
USE SalesDB
-
GO
-
CREATE PROCEDURE OnReceiveMessage--OnReceiveMessage存储过程
-
AS
-
declare @message_type int--消息类型变量
-
declare @dialog uniqueidentifier,--会话变量
-
@ErrorSave INT,--错误点保存变量
-
@ErrorDesc NVARCHAR(100),--错误描述变量
-
@message_body int;--消息主体变量
-
-
while (1 = 1)
-
begin
-
begin transaction --开始事务.
-
WAITFOR (--等待到某个时间执行语句
-
RECEIVE top(1)--从队列中选择第一条语句
-
@message_type=message_type_id,--说明消息的消息类型的 SQL Server 对象标识符
-
@message_body=message_body,
-
@dialog = conversation_handle
-
FROM SalesQueue
-
), TIMEOUT 3000--如果超时时间已到，则 RECEIVE 返回一个空结果集,即0行
-
if (@@ROWCOUNT = 0)--受上一语句影响的行数
-
BEGIN
-
Rollback Transaction
-
BREAK
-
END
-
-
SET @ErrorSave = @@ERROR ;--执行的上一个 Transact-SQL 语句的错误号
-
IF (@ErrorSave <> 0)--有错误
-
BEGIN
-
ROLLBACK TRANSACTION ;
-
SET @ErrorDesc = N'An error has occurred.' ;--给错误信息赋值;以字母 N 为前缀标识 Unicode 字符串常量
-
--在服务器上执行的代码中（例如在存储过程和触发器中）显示的 Unicode 字符串常量必须以大写字母 N
-
--为前缀。即使所引用的列已定义为 Unicode 类型，也应如此。如果不使用 N 前缀，
-
--字符串将转换为数据库的默认代码页。这可能导致不识别某些字符。
-
END CONVERSATION @dialog
-
WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ; --结束对话
-
END
-
ELSE
-
IF (@message_type <> 2)--部分消息的“状态”列设为 2（禁用）。
-
BEGIN
-
UPDATE SalesDetails SET TotalSales = TotalSales + @message_body;
-
--更新年销售总额表，让年销售总额自动加上消息里面的金额
-
END
-
ELSE
-
BEGIN
-
END CONVERSATION @dialog--金额会话
-
END
-
COMMIT TRANSACTION--提交事务
-
END
-
--2.在AdventureWorks库中创建消息类型、协议、队列和服务对象
-
USE AdventureWorks
-
GO
-
CREATE MESSAGE TYPE SendMessage --创建消息类型：发送消息
-
VALIDATION = NONE--不验证
-
CREATE MESSAGE TYPE AcknowledgeMessage--创建消息类型:接收消息
-
VALIDATION = NONE--不验证
-
--**********************************
-
CREATE CONTRACT MyContract--创建协议
-
(SendMessage SENT BY INITIATOR,--初始化服务为：发送消息类型
-
AcknowledgeMessage SENT BY TARGET)--目标服务为：接收消息类型
-
--*********************************
-
CREATE QUEUE AdvQueue;--创建队列
-
--*********************************
-
CREATE SERVICE SalesService--创建基于使用MyContact协议的队列消息服务
-
ON QUEUE AdvQueue (MyContract)--
-
--3.在SalesDB库中创建消息类型、协议、队列和服务对象******************
-
--*******************************************************************
-
USE SalesDB
-
GO
-
CREATE MESSAGE TYPE SendMessage
-
VALIDATION = NONE
-
CREATE MESSAGE TYPE AcknowledgeMessage
-
VALIDATION = NONE
-
CREATE CONTRACT MyContract
-
(SendMessage SENT BY INITIATOR,
-
AcknowledgeMessage SENT BY INITIATOR)
-
--**********************************
-
CREATE QUEUE SalesQueue--销售队列
-
WITH STATUS=ON,--状态为打开
-
ACTIVATION(--指定使用的存储过程
-
PROCEDURE_NAME = OnReceiveMessage,--OnReceiveMessage为存储过程.
-
MAX_QUEUE_READERS = 5,--最大队列数为5个
-
Execute AS SELF) ;--当前用户执行
-
--*****************************
-
CREATE SERVICE RecieveService--接收服务
-
ON QUEUE SalesQueue (MyContract)
-
--*********************************************************************
-
--4.在SalesOrderHeader表上创建触发器，实现：当完成任何销售的时候更新SalesDB数据库
-
USE AdventureWorks
-
GO
-
--select * from Sys.sysobjects where name='SendTrigger'
-
--drop trigger SendTrigger
-
--sp_helptext SendTrigger
-
create TRIGGER SendTrigger--创建触发器
-
ON Sales.SalesOrderHeader FOR UPDATE AS--基于SalesOrderHeader的更新触发器
-
DECLARE @amt AS int--定义变量
-
SELECT @amt = SubTotal FROM INSERTED--总数,从插入的临时表中获得
-
DECLARE @dialog_handle UNIQUEIDENTIFIER ; --定义会话变量
-
-
BEGIN DIALOG CONVERSATION @dialog_handle FROM --开始会话，从销售服务到接收服务
-
SERVICE [SalesService] TO SERVICE 'RecieveService' ON CONTRACT [MyContract] ;
-
-
SEND ON CONVERSATION --发送消息，类型为SendMessage,内容为@amt,即更新的总数
-
@dialog_handle MESSAGE TYPE[SendMessage] (@amt)
-
select @amt
-
--5.AdventureWorks表,
-
USE AdventureWorks
-
GO
-
select subtotal from Sales.SalesOrderHeader where SalesOrderId=43692
-
--6.执行更新操作，来响应触发器
-
update Sales.SalesOrderHeader
-
set SubTotal =5000
-
where SalesOrderID = 43692
-
--7.查看SalesDB库年度销售表的数据
-
use SalesDB
-
go
-
select * from SalesDetails
-
select * from sys.transmission_queue
-
--创建建库 Test1
-
create database Test1
-
on
-
(
-
name='Test1_data',
-
filename='c:\pk\Test1_data.mdf',
-
size=3mb,
-
filegrowth=15%
-
)
-
log on
-
(
-
name= 'Test1_log',
-
filename='c:\pk\Test1_log.ldf',
-
size=3mb,
-
filegrowth=15%
-
)
-
-
--创建建库 Test2
-
create database Test2
-
on
-
(
-
name='Test2_data',
-
filename='c:\uk\Test2_data.mdf',
-
size=3mb,
-
filegrowth=15%
-
)
-
log on
-
(
-
name= 'Test2_log',
-
filename='c:\uk\Test2_log.ldf',
-
size=3mb,
-
filegrowth=15%
-
)
-
--===============================================
-
use Test1
-
go
-
-
create table userinfo --用户信息表
-
(
-
customerid int identity(1,1),
-
customername char(8) not null,
-
pid char(18) not null,
-
telephone char(13) not null,
-
address varchar(50)
-
)
-
go
-
--==============================================
-
use Test2
-
go
-
create table cardinfo --银行卡信息表
-
(
-
cardid char(19) not null,
-
curtype char(5) not null,
-
savingtype char(8) not null,
-
opendate datetime not null,
-
openmoney money not null,
-
balance int not null,
-
pass char(6) not null,
-
isreportloss bit not null,
-
customerid int not null
-
)
-
go
-
drop table cardinfo
-
--==============================================
-
select * from Test1.dbo.userinfo
-
select * from Test2.dbo.cardinfo
-
-
insert into cardinfo(cardid,curtype,savingtype,opendate,openmoney,balance,pass,isreportloss,customerid)
-
values('1010 3576 1234 5678','rmb','活期','2000-12-12',1000,1000,'888888',0,1)
-
-
insert into userinfo(customername,pid,telephone,address)
-
values('张三','123456789012345','010-67898978','北京海淀')
-
-
delete from userinfo
-
--==================
-
--=创建服务程序（存储过程）实现对银行卡信息表进行修改操作===================================
-
use Test2
-
go
-
-
alter procedure OnReceiveMessage
-
as
-
declare @message_type int
-
declare @dialog uniqueidentifier,
-
@ErrorSave int,
-
@ErrorDesc nvarchar(100),
-
@message_body int;
-
while(1=1)
-
begin
-
begin transaction
-
waitfor(
-
receive top(1)
-
@message_type = message_type_id,
-
@message_body = message_body,
-
@dialog = conversation_handle
-
from SalesQueue
-
),timeout 3000
-
if(@@rowcount = 0)
-
begin
-
rollback tran
-
break
-
end
-
set @ErrorSave = @@error;
-
if(@ErrorSave <> 0)
-
begin
-
rollback tran;
-
set @ErrorDesc = '错误发生，请注意！';
-
end conversation @dialog
-
with error = @ErrorSave description = @ErrorDesc;
-
end
-
else
-
if(@message_type <> 2)
-
begin
-
update Test2.dbo.cardinfo set balance = balance + @message_body;
-
end
-
else
-
begin
-
end conversation @dialog
-
end
-
commit tran
-
end
-
--======================================
-
use Test1
-
-- 创建 消息类型
-
create message type SendMessage
-
validation = none
-
-
create message type AcknowledgeMessage
-
validation = none
-
-
-- 创建 协议
-
create contract MyContract
-
(
-
SendMessage sent by initiator,
-
AcknowledgeMessage sent by target
-
)
-
-- 创建 队列
-
create queue AdvQueue
-
-
-- 创建 服务
-
create service SalesService
-
on queue AdvQueue(MyContract)
-
-
--======================================
-
use Test2
-
-- 创建 消息类型
-
create message type SendMessage
-
validation = none
-
-
create message type AcknowledgeMessage
-
validation = none
-
-
-- 创建 协议
-
create contract MyContract
-
(
-
SendMessage sent by initiator,
-
AcknowledgeMessage sent by initiator
-
)
-
-- 创建 队列
-
create queue SalesQueue
-
with status = on,
-
activation(
-
procedure_name = OnReceiveMessage,
-
max_queue_readers = 5,
-
execute as self
-
);
-
-
-- 创建 服务
-
create service RecieveService
-
on queue SalesQueue(MyContract)
-
-
--===================================
-
use Test1
-
go
-
-
alter trigger SendTrigger
-
on userinfo
-
for update
-
as
-
declare @amt int
-
select @amt = customerid from inserted
-
declare @dialog_handle uniqueidentifier;
-
begin dialog conversation @dialog_handle from
-
service [SalesService] to service 'RecieveService'
-
on contract MyContract;
-
send on conversation @dialog_handle
-
message type SendMessage(@amt)
-
-
--===============
-
select * from userinfo
-
insert into userinfo(customername,pid,telephone,address)
-
values('张三','123456789012345','010-67898978','北京海淀')
-
--==================
-
update userinfo
-
set pid = '111111111111111'
-
where customerid = 2
-
-
-
--=========断开Test2数据库===============
-
exec sp_detach_db 'Test2','true'
-
-
-------------查看队列中暂存的数据
-
select * from sys.transmission_queue
-
-
--=========================================================
-
--恢复数据库Test2
-
exec sp_attach_db @dbname = N'Test2',
-
@filename1 = N'C:\uk\Test2_data.mdf',
-
@filename2 = N'C:\uk\Test2_log.ldf'
-
-
-
--=========================
-
alter database Test2
-
set enable_broker
-
-
--ENABLE_BROKER。
-
--此选项可激活 Service Broker 消息传递功能，
-
--并保留数据库的现有 Service Broker 标识符。
-
--============================
-
select * from Test2.dbo.cardinfo
-
-
select * from Test1.dbo.userinfo
视频课 https://edu.csdn.net/course/play/7940
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/52518505
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）