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
--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
