MSSQL之二十四实现服务器代理

怼烎@ 2022-09-25 15:22 136阅读 0赞

/*
    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][https_edu.csdn.net_course_play_7940] #

[https_edu.csdn.net_course_play_7940]: https://edu.csdn.net/course/play/7940