Contents

1
2
3 朱朱 T-SQL 伎 SP
4 Service Broker 覿


1 #

觜蠍 覃讌 豌襴 襦蠏碁 旧襯 企 . 讀, 覈 豌襴螳 螻 企殊伎誤語 願 蠍磯 覦 覿覿蟾讌襷 覯 願 螳讌螻 螻 襾語 Service Broker 襷蟆 觜蠍 豌襴襯 蟆企.

2 #

sssb05.jpg
CREATE TABLE dbo.Cust
	(
	CustID varchar(20) NOT NULL,
	PassWD varchar(20) NOT NULL,
	CurrentConnYN bit NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Cust ADD CONSTRAINT
	PK_Cust PRIMARY KEY CLUSTERED 
	(
	CustID
	) 
GO
CREATE TABLE dbo.Connect_History
	(
	CustID varchar(20) NOT NULL,
	DisConnectDT datetime NOT NULL,
	ConnectDT datetime NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Connect_History ADD CONSTRAINT
	PK_Connect_History PRIMARY KEY CLUSTERED 
	(
	CustID,
	DisConnectDT,
	ConnectDT
	) 
GO
ALTER TABLE dbo.Connect_History ADD CONSTRAINT
	FK_Connect_History_Cust FOREIGN KEY
	(
	CustID
	) REFERENCES dbo.Cust
	(
	CustID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO

覓語:
Connect_History 企 企殊伎誤語蟆 觜るゼ 螻牛磯 蟯 企朱 一企 企リ襴 企企. 讌襷 れ襦 襦蠏語語 Connect_History企 Insert襯 伎 螻, 襦蠏語 Update襯 伎 . 蠏碁覩襦 碁 蠍語伎 SP語 旧螳 蠍語伎蟆 .

3 朱朱 T-SQL 伎 SP #

insert Cust values('yasicom', '1234', 0)
go

create proc usp_connect
	@cust_id varchar(20)
,	@pass_wd varchar(20)
,	@conn_yn bit = 1 --1:, 0:譬襭
as
begin
	declare @rs bit;

	-- 螻螳 ID, PW襯 譟壱螻,  譴語 
	--if CurrentConnYN = 1 then  else 譬襭
	--select 伎 ID, PW螳 譟伎覃 れ 一危 豺 蠏碁郁碓 讌 襷蠍磯ゼ
	--update 譟郁唄 襷  伎 一危誤覩襦 朱Μ朱 update襷 企 覓 覓語 . 

	update Cust
	set
		@rs = CurrentConnYN = case when @conn_yn = 1 then 1 else 0 end
	from Cust
	where CustID = @cust_id
	and PassWD = @pass_wd
	and CurrentConnYN = case when @conn_yn = 1 then 0 else 1 end;

	--企レInsert, update
	if @rs is null 
		return isnull(@rs, 0);
	else if @rs = 1
	begin
		insert Connect_History(CustID, DisConnectDT, ConnectDT)
		values(@cust_id, '99991231', getdate());
	end else 
	begin
		update Connect_History
		set DisConnectDT = getdate()
		where CustID = @cust_id
		and DisConnectDT = '99991231'
		and @conn_yn = 0
	end

	return isnull(@rs, 0);

end
go

--2ろる伎黄覯讌碁1襴危螻覯讌碁0襴危蟆企. 
declare @rs1 int
exec @rs1 = usp_connect 'yasicom', '1234', 1
select @rs1

--譬襭
declare @rs2 int
exec @rs2 = usp_connect 'yasicom', '1234', 0
select @rs2

襷 企 襴 蟆企. 蠏碁 襷 蟆豌 企レ 蟯襴蠍 伎 蠏 襷殊 襴り 觜. 碁 觜襦蟆 企リ襴 企 谿讌 螻糾 企. 螻糾伎 伎 讌襷 焔リ讌 伎 襴螳 . 蠏碁 SQL Server 2005 襦蟆 覲伎 Service Broker襯 伎覃 企リ襴 企 覃讌襷 螻 豌襴襯 讌 襷讌 願 企殊伎誤語蟆 譯朱襦 旧襯 レ . 覓朱 蠏覈..伎 れ襦 Service Broker襯 伎伎 觜蠍 豌襴襯 企慨襦 蟆. 貊 襷朱襦 語襴 牛.

create message type conn_history_msg
validation = none;

create contract conn_history_contract
(conn_history_msg sent by initiator);
go

create proc usp_sb_conn
as
return 0;
go

create queue sendqueue;
create queue receivequeue
with activation
(
	status = off
,	procedure_name = usp_sb_conn
,	max_queue_readers = 100
,	execute as self
);

create service send_service
  on queue sendqueue (conn_history_contract);

create service receive_service
  on queue receivequeue (conn_history_contract);
go


alter proc usp_connect
	@cust_id varchar(20)
,	@pass_wd varchar(20)
,	@conn_yn bit = 1 --1:, 0:譬襭
as
begin
	declare @rs bit;

	update Cust
	set
		@rs = CurrentConnYN = case when @conn_yn = 1 then 1 else 0 end
	from Cust
	where CustID = @cust_id
	and PassWD = @pass_wd
	and CurrentConnYN = case when @conn_yn = 1 then 0 else 1 end;

	--蠍磯 (伎る 蠏碁襦)
	-- 企レ 覃讌 讌
	/*
	if @rs is null 
	begin
		return isnull(@rs, 0);
	end
	else if @rs = 1
	begin
		insert Connect_History(CustID, DisConnectDT, ConnectDT)
		values(@cust_id, '99991231', getdate());
	end else
	begin
		update Connect_History
		set DisConnectDT = getdate()
		where CustID = @cust_id
		and DisConnectDT = '99991231'
		and @conn_yn = 0
	end
	*/
	declare 
		@handle uniqueidentifier
	,	@msg nvarchar(max);

	set @msg = N'
	<root>
		<rs>' + isnull(convert(varchar, @rs), '') + '</rs>
		<custid>' + @cust_id + '</custid>
		<currdt>' + convert(varchar, getdate(), 121) + '</currdt>
		<connyn>' + convert(varchar, @conn_yn) + '</connyn>
	</root>
	';

	begin dialog conversation @handle
	from service send_service
	to service 'receive_service'
	on contract conn_history_contract
	with encryption = off;

	send on conversation @handle
	message type conn_history_msg(@msg); 


	return isnull(@rs, 0);

end
go

alter proc usp_sb_conn
as
begin
	declare 
		@msg xml
	,	@handle uniqueidentifier
	,	@rs bit
	,	@cust varchar(20)
	,	@currdt varchar(30)
	,	@connyn bit;

	while(1=1)
	begin
		receive top(1) 
			@msg = convert(xml, convert(nvarchar(max), message_body))
		,	@handle = conversation_handle
		from receivequeue

		if @@rowcount = 0
		begin
			end conversation @handle;
			break;
			
		end

		select
			@rs = x.item.value('rs[1]', 'bit')
		,	@cust = x.item.value('custid[1]', 'varchar(20)')
		,	@currdt = x.item.value('currdt[1]', 'varchar(30)') 
		,	@connyn = x.item.value('connyn[1]', 'bit') 
		from @msg.nodes('/root') as x(item)

		if @rs is null 
		begin
			return isnull(@rs, 0);
		end
		else if @rs = 1
		begin
			insert Connect_History(CustID, DisConnectDT, ConnectDT)
			values(@cust, '99991231', @currdt);
		end else
		begin
			update Connect_History
			set DisConnectDT = @currdt
			where CustID = @cust
			and DisConnectDT = '99991231'
			and @connyn = 0
		end
	end

	return isnull(@rs, 0)
end
go

伎 れ襦 ろ 覲伎.
--Connect_History觜企.
--譴語覈襯企..
truncate table Connect_History

--企蓋.
--Connect_History 企企レ企讌朱炎概/ろ覿螳襴企. 
--讀, 覃讌襯朱慨願瑚企. 
declare @rs int
exec @rs = usp_connect 'yasicom', '1234', 0
exec @rs = usp_connect 'yasicom', '1234', 1
select @rs

--譟壱
select top(100) convert(xml, convert(nvarchar(max), message_body))
from receivequeue with (nolock);
go
sssb06.jpg

--status=on朱覲蟆
alter queue receivequeue
with activation
(
	status = on --off on朱覲蟆
,	procedure_name = usp_sb_conn
,	max_queue_readers = 100
,	execute as self
);
go

--譟壱
--譟郁朱 Servic Broker螳 usp_sb_conn 語 企レ  豌襴襯  蟆. 
--SQL Server Server  覲願  豌襴り . 伎 豕 觜襴..
select top(100) convert(xml, convert(nvarchar(max), message_body))
from receivequeue with (nolock);

--Connect_History譟壱
select * from Connect_History;
go
sssb07.jpg

4 Service Broker 覿 #

  • 蠍 碁 覿
  • 觜れ レ 殊讌 覿覿 豌襴蟇磯 (企, 襦蠏語 一危 豌襴)
  • 蠏覈 覿 蟆


GOOD~ ... 襷 l朱 讖... 蟲
企螻 覯碁ゼ l伎 蟆.. -- 貅 2012-05-11 13:54:18


給朱 企 螳 蠑 讌 伎襴.. -- 伎 2012-05-11 14:55:59