If not exists (select * from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is Nulls or comments != 'web login user'))
Begin --Has no login record
Insert into kodak.tOnlineClient (UserGuid, RoleName, IISUrl, MachineIP, SessionID, IsOnline, LoginTime)
Values ('e540b9bc-3c19-469e-b3ee-362a228911a2', 'Administrator', 'http://localhost:4302/WebService/GCRISService.asmx', '150.245.176.149', 'ozff5zav4i5t1myspgj0mr55', 1, GETDATE())
Select 0
End
Else
Begin --Has login record
If exists (Select 1 from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and IsOnline = 0 and (comments is null or comments != 'web login user'))
Begin --(UserGuid's Online status is Off)
If 'False' = 'True'
Begin -- This is a session has ever logined but time out now
IF exists (Select 1 from didaj.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and '150.245.176.149' <> MachineIP and (comments is null or comments != 'web login user'))
Begin -- ClientSeesion ID has been hijackered by other session
--Return Value = 2 implicate Some one else hase ever been logined and logouted since this session time out
Select 2
End
Else
Begin -- The ClientSessionID is of himself
Update kodak.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2 ' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
else
Begin --Login try for the first time
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
Else
Begin --(UserGuid's Online status is On)
if exists (Select 1 from dbo.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and MachineIP = '150.245.176.149' and (comments is null or comments != 'web login user'))
Begin --At Same Machine, imlicated that is the Same User(Maybe exit the client side abnormally or relogin when fisnishing lockscreen status)
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
Else --At different Machine
Select 1 -- Some one Else with the Same UserID Is Online
End
End
Begin --Has no login record
Insert into kodak.tOnlineClient (UserGuid, RoleName, IISUrl, MachineIP, SessionID, IsOnline, LoginTime)
Values ('e540b9bc-3c19-469e-b3ee-362a228911a2', 'Administrator', 'http://localhost:4302/WebService/GCRISService.asmx', '150.245.176.149', 'ozff5zav4i5t1myspgj0mr55', 1, GETDATE())
Select 0
End
Else
Begin --Has login record
If exists (Select 1 from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and IsOnline = 0 and (comments is null or comments != 'web login user'))
Begin --(UserGuid's Online status is Off)
If 'False' = 'True'
Begin -- This is a session has ever logined but time out now
IF exists (Select 1 from didaj.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and '150.245.176.149' <> MachineIP and (comments is null or comments != 'web login user'))
Begin -- ClientSeesion ID has been hijackered by other session
--Return Value = 2 implicate Some one else hase ever been logined and logouted since this session time out
Select 2
End
Else
Begin -- The ClientSessionID is of himself
Update kodak.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2 ' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
else
Begin --Login try for the first time
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
Else
Begin --(UserGuid's Online status is On)
if exists (Select 1 from dbo.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and MachineIP = '150.245.176.149' and (comments is null or comments != 'web login user'))
Begin --At Same Machine, imlicated that is the Same User(Maybe exit the client side abnormally or relogin when fisnishing lockscreen status)
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
Else --At different Machine
Select 1 -- Some one Else with the Same UserID Is Online
End
End
解决方案 »
- 簡單的兩表查詢都出問題。。。救命
- 这个默认值应该如何设置?
- 如何恢复ORACLE中被DROP的列?
- 如何过滤出两行某列之差大于某个值的那些行
- 求一个SQL语句
- ★★急求sql优化算法处理手机区域确定,超高分相赠
- oracle中grant问题?
- Oracle中使用where查询或条件匹配默认区分大小写,有没有办法可以设置不区分大小写?
- oracle客户端连接问题(在线等待,马上节分)
- rac 连接 freenas iscsi报错 rac1 iscsid: Kernel reported iSCSI connection 3:0 error
- 多维分区表问题
- 哪位大侠有系统视图v$系列得详细资料,跪求一份可以吗
可以参考下面的方法修改
declare
v_i integer;
v_j integer;
begin
select count(*) into v_i
from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is Nulls or comments != 'web login user');
if v_i=0 then
Insert into kodak.tOnlineClient (UserGuid, RoleName, IISUrl, MachineIP, SessionID, IsOnline, LoginTime)
Values ('e540b9bc-3c19-469e-b3ee-362a228911a2', 'Administrator', 'http://localhost:4302/WebService/GCRISService.asmx', '150.245.176.149', 'ozff5zav4i5t1myspgj0mr55', 1, sysdate);
else
...
end if;
end;