MSSQL编程：用存储历程写的谈天室程序

用一个库#bak_database存放这些历史数据。程序|存储历程|谈天室
转载请说明出处
/******Object:Triggerdbo.update_roomScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[update_room])andOBJECTPROPERTY(id,NIsTrigger)=1)
droptrigger[dbo].[update_room]
GO

/******Object:StoredProceduredbo.chatScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[chat])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[chat]
GO

/******Object:Table[dbo].[AirLinkMT]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[AirLinkMT])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[AirLinkMT]
GO

/******Object:Table[dbo].[chat_clew]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[chat_clew])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[chat_clew]
GO

/******Object:Table[dbo].[chat_log]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[chat_log])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[chat_log]
GO

/******Object:Table[dbo].[chat_room]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[chat_room])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[chat_room]
GO

/******Object:Table[dbo].[chat_user]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[chat_user])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[chat_user]
GO

/******Object:Table[dbo].[FREE_PHONE]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[FREE_PHONE])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[FREE_PHONE]
GO

/******Object:Table[dbo].[test_phone]ScriptDate:2004-9-278:18:44******/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[test_phone])andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable[dbo].[test_phone]
GO

/******Object:Table[dbo].[AirLinkMT]ScriptDate:2004-9-278:18:45******/
CREATETABLE[dbo].[AirLinkMT](
[OuQ_Id][int]IDENTITY(1,1)NOTNULL,
[OuQ_Date][datetime]NOTNULL,
[MsgFmt][int]NOTNULL,
[Province][smallint]NOTNULL,
[Service][int]NULL,
[FeeCode][nvarchar](7)COLLATEChinese_PRC_CI_ASNOTNULL,
[MsgBody][nvarchar](1024)COLLATEChinese_PRC_CI_ASNOTNULL,
[DestPhone][nvarchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
[FeeSevID][nvarchar](11)COLLATEChinese_PRC_CI_ASNOTNULL,
[FeeType][nchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[SrcPhone][nvarchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
[FeePhone][nvarchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
[Priority][tinyint]NOTNULL,
[MsgCode][tinyint]NOTNULL,
[ReportFlag][tinyint]NOTNULL,
[MTType][tinyint]NOTNULL,
[LinkID][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL
)ON[PRIMARY]
GO

/******Object:Table[dbo].[chat_clew]ScriptDate:2004-9-278:18:47******/
CREATETABLE[dbo].[chat_clew](
[id][int]IDENTITY(1,1)NOTNULL,
[content][nvarchar](512)COLLATEChinese_PRC_CI_ASNOTNULL,
[province][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL
)ON[PRIMARY]
GO

/******Object:Table[dbo].[chat_log]ScriptDate:2004-9-278:18:48******/
CREATETABLE[dbo].[chat_log](
[chat_id][int]IDENTITY(1,1)NOTNULL,
[Phone][nvarchar](11)COLLATEChinese_PRC_CI_ASNOTNULL,
[srcPhone][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[msgBody][nvarchar](512)COLLATEChinese_PRC_CI_ASNULL,
[roomid][int]NULL,
[sendTime][datetime]NOTNULL,
[tophone][nvarchar](11)COLLATEChinese_PRC_CI_ASNULL
)ON[PRIMARY]
GO

/******Object:Table[dbo].[chat_room]ScriptDate:2004-9-278:18:49******/
CREATETABLE[dbo].[chat_room](
[id][int]IDENTITY(1,1)NOTNULL,
[Phone][nvarchar](11)COLLATEChinese_PRC_CI_ASNOTNULL,
[roomname][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[createtime][datetime]NOTNULL,
[online][int]NOTNULL,
[welcome][nvarchar](512)COLLATEChinese_PRC_CI_ASNULL,
[intime][datetime]NULL
)ON[PRIMARY]
GO

/******Object:Table[dbo].[chat_user]ScriptDate:2004-9-278:18:51******/
CREATETABLE[dbo].[chat_user](
[userid][int]IDENTITY(10000,1)NOTNULL,
[phone][nvarchar](11)COLLATEChinese_PRC_CI_ASNOTNULL,
[nickname][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[srcPhone][nvarchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
[Province][int]NOTNULL,
[regtime][datetime]NOTNULL,
[intime][datetime]NULL,
[intime1][datetime]NULL,
[State][int]NOTNULL,
[roomid][int]NULL,
[sex][nvarchar](2)COLLATEChinese_PRC_CI_ASNULL,
[chat][bit]NOTNULL,
[fraction][int]NOTNULL
)ON[PRIMARY]
GO

/******Object:Table[dbo].[FREE_PHONE]ScriptDate:2004-9-278:18:53******/
CREATETABLE[dbo].[FREE_PHONE](
[PhoneNumber][nvarchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
[FreeSrvId][nvarchar](10)COLLATEChinese_PRC_CI_ASNOTNULL
)ON[PRIMARY]
GO

/******Object:Table[dbo].[test_phone]ScriptDate:2004-9-278:18:54******/
CREATETABLE[dbo].[test_phone](
[phone][nvarchar](11)COLLATEChinese_PRC_CI_ASNOTNULL
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[AirLinkMT]WITHNOCHECKADD
CONSTRAINT[PK_AirLinkMT]PRIMARYKEYCLUSTERED
(
[OuQ_Id]
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[chat_clew]WITHNOCHECKADD
CONSTRAINT[PK_chat_clew]PRIMARYKEYCLUSTERED
(
[id]
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[chat_log]WITHNOCHECKADD
CONSTRAINT[PK_chat_log]PRIMARYKEYCLUSTERED
(
[chat_id]DESC
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[chat_room]WITHNOCHECKADD
CONSTRAINT[PK_chat_room2]PRIMARYKEYCLUSTERED
(
[id]
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[chat_user]WITHNOCHECKADD
CONSTRAINT[PK_chat_user]PRIMARYKEYCLUSTERED
(
[userid]
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[AirLinkMT]WITHNOCHECKADD
CONSTRAINT[DF_AirLinkMT_OuQ_Date]DEFAULT(getdate())FOR[OuQ_Date],
CONSTRAINT[DF_AirLinkMT_OuQ_SrcPhone]DEFAULT(8888)FOR[SrcPhone],
CONSTRAINT[DF_AirLinkMT_OuQ_Priority]DEFAULT(1)FOR[Priority],
CONSTRAINT[DF_AirLinkMT_OuQ_MsgCode]DEFAULT(0)FOR[MsgCode],
CONSTRAINT[DF_AirLinkMT_ReportFlag]DEFAULT(1)FOR[ReportFlag],
CONSTRAINT[DF_AirLinkMT_MTType]DEFAULT(2)FOR[MTType],
CONSTRAINT[DF_AirLinkMT_LinkID]DEFAULT(0)FOR[LinkID]
GO

ALTERTABLE[dbo].[chat_log]WITHNOCHECKADD
CONSTRAINT[DF_chat_log_sendTime]DEFAULT(getdate())FOR[sendTime]
GO

ALTERTABLE[dbo].[chat_room]WITHNOCHECKADD
CONSTRAINT[DF_chat_room2_createtime]DEFAULT(getdate())FOR[createtime],
CONSTRAINT[DF_chat_room_online]DEFAULT(0)FOR[online],
CONSTRAINT[DF_chat_room2_intime]DEFAULT(getdate())FOR[intime]
GO

ALTERTABLE[dbo].[chat_user]WITHNOCHECKADD
CONSTRAINT[DF_chat_user_intime]DEFAULT(getdate())FOR[regtime],
CONSTRAINT[DF_chat_user_intime_1]DEFAULT(getdate())FOR[intime],
CONSTRAINT[DF_chat_user_intime1]DEFAULT(getdate())FOR[intime1],
CONSTRAINT[DF_chat_user_state]DEFAULT(0)FOR[State],
CONSTRAINT[DF_chat_user_chat]DEFAULT(0)FOR[chat],
CONSTRAINT[DF_chat_user_fraction]DEFAULT(0)FOR[fraction],
CONSTRAINT[IX_chat_user]UNIQUENONCLUSTERED
(
[nickname]
)ON[PRIMARY]
GO

SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSOFF
GO

/******Object:StoredProceduredbo.chatScriptDate:2004-9-278:18:56******/
CREATEproc[dbo].[chat]
--chat13588144652,MY,278810,571,1
@phonenvarchar(11),
@Contentnvarchar(512),
@srcPhonenvarchar(20),--端口
@Provincenvarchar(20),--省分
@debugint,--调试
@LinkIDnvarchar(20)=0
as
declare
@msgBodynvarchar(512),--发送动静
@roomidint,--房间ID
@roomnamenvarchar(20),--房间称号
@onlineint,--在耳目数
@stateint,--用户形态
@useridint,--用户ID
@nicknamenvarchar(20),--用户昵称
@tonicknamenvarchar(20),--对方昵称
@sexnvarchar(2),--性别
@chatbit,--是不是承受群聊
@clewnvarchar(512),--提醒语
@tophonenvarchar(11),--吸收号码
@welcomenvarchar(512),--接待词

@FeeCodeint,
@FeeTypeint,
@FeeSevIDnvarchar(20),
@MtTempint

--毛病反省
if@Content=or@Contentisnullorlen(@phone)11begin
return
end

--前面补上10
iflen(@srcPhone)<6begin
set@srcPhone=left(@srcPhone,4)+10
end

--3天没动静主动封闭群聊
updatechat_usersetchat=0wherechat=1anddatediff(d,intime,getdate())>2

--10天没动静主动离线
updatechat_usersetstate=0,roomid=nullwherestate=1anddatediff(d,intime,getdate())>5

--更新本人形态
updatechat_usersetintime=getdate(),intime1=getdate(),fraction=fraction+1wherephone=@phone

--屏障手机号码
ifcharindex(13,@Content)>0andisnumeric(substring(@Content,charindex(13,@Content),7))=1andnotexists(select*fromtest_phonewherephone=@phone)begin
return
end

--容错处置
ifupper(left(@Content,1))=Mbegin
set@Content=replace(@Content,,,)
set@Content=replace(@Content,,,)
set@Content=replace(@Content,,)
set@Content=replace(@Content,(,)
set@Content=replace(@Content,),)
set@Content=replace(@Content,.,)
set@Content=replace(@Content,+,)
end

ifexists(select*fromfree_phonewherephonenumber=@phoneandfreesrvid=520LT)begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=2371begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=LTS
end
elseif@Province=2571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=YXG
end

--未注册
ifnotexists(select*fromchat_userwherephone=@phone)andupper(@Content)QXLTbegin

ifupper(@Content)MEand@Province=571begin
set@msgBody=请复兴ME完成注册
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,BZ,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
return
end

ifupper(left(@Content,2))=MEandlen(@Content)>2begin
set@nickname=substring(@Content,3,len(@Content))
set@sex=substring(@nickname,1,1)

if@sexnotin(男,女)begin
set@sex=女
end
elsebegin
set@nickname=substring(@nickname,2,len(@nickname))
end
iflen(@nickname)>6begin
set@msgBody=嗨！这名字好长啊！不要觉得帅哥玉人都是电脑哦~！想捉住属于你的情缘，昵称不要凌驾6个字。复兴ME+性别+昵称完成注册。
end
elseiflen(@nickname)<1begin
set@msgBody=嗨！这名字太短了吧？不要觉得帅哥玉人都是电脑哦~！想捉住属于你的情缘，昵称不要少于1个字。复兴ME+性别+昵称完成注册。
end
elseifisnumeric(@nickname)=1begin
set@msgBody=嗨！怎样取数字名字呀？不要觉得帅哥玉人都是电脑哦~！想捉住属于你的情缘，昵称不要凌驾6个字。复兴ME+性别+昵称完成注册。
end
elseifexists(select*fromchat_userwherenickname=@nickname)begin
set@msgBody=哇！聪明而灵秀的名字啊，惋惜有人争先一步了。守候你的，是否是心动的相逢呢？快快复兴ME+性别+昵称换一个靓名吧。
end
elsebegin
insertintochat_user(phone,nickname,srcphone,province,sex)values(@phone,@nickname,left(@srcphone,6),@province,@sex)
set@roomid=rand()*8+1
select@roomname=roomnamefromchat_roomwhereid=@roomid
updatechat_usersetstate=1,roomid=@roomid,chat=1wherephone=@phone
set@msgBody=@nickname+",接待离开愿望都会之"+@roomname+"谈天室,复兴想要说的话既可与人人谈天,复兴MR看房间,复兴MK找伴侣,改昵称复兴ME+性别+昵称"
end
end
elsebegin
selecttop1@userid=useridfromchat_userorderbyuseriddesc
insertintochat_user(phone,nickname,srcphone,province,sex)values(@phone,ltrim(str(@userid+1)),left(@srcphone,6),@province,女)
select@roomid=roomid,@nickname=nicknamefromchat_userwherephone=@phone
set@roomid=rand()*8+1
select@roomname=roomnamefromchat_roomwhereid=@roomid
updatechat_usersetstate=1,roomid=@roomidwherephone=@phone
set@msgBody="接待离开愿望都会之"+@roomname+"谈天室，邂遇一场恋爱，寻求一段浪漫！复兴MR看房间，复兴MK找伴侣，改昵称复兴ME+性别+昵称"
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintochat_log(phone,srcphone,msgbody,roomid)values(@phone,@srcPhone,@nickname+离开谈天室,@roomid)
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end

--已注册
elsebegin

ifupper(left(ltrim(@Content),4))in(QXLT)begin
set@srcPhone=left(@srcPhone,6)
deletechat_userwherephone=@phone
deletechat_roomwherephone=@phone
if@Province=2571begin
set@msgBody=夜凉如水，路上当心！愿今晚给你留下的不但是优美的回想！记得常返来看看，发送520到8788重回都会情缘谈天室。
end
elsebegin
set@msgBody=夜凉如水，路上当心！愿今晚给你留下的不但是优美的回想！记得常返来看看，发送ME到+@srcPhone+重回都会情缘谈天室。
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)and@Province571begin--浙江挪动不下发
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
return
end

--MEMKMRMCMQMS
ifupper(left(@Content,2))=MEbegin
set@srcPhone=left(@srcPhone,6)
iflen(@Content)>2begin--改昵称
set@nickname=substring(@Content,3,len(@Content))
set@sex=substring(@nickname,1,1)
if@sexnotin(男,女)begin
set@sex=女
end
elsebegin
set@nickname=substring(@nickname,2,len(@nickname))
end
iflen(@nickname)>6begin
set@msgBody=嗨！这名字好长啊！不要觉得帅哥玉人都是电脑哦~！想找到你的梦中恋人，昵称不要凌驾6个字。复兴ME+性别+昵称完成修正。
end
elseiflen(@nickname)<1begin
set@msgBody=嗨！这名字太短了吧？不要觉得帅哥玉人都是电脑哦~！想捉住属于你的情缘，昵称不要凌驾6个字。复兴ME+性别+昵称完成注册。
end
elseifisnumeric(@nickname)=1begin
set@msgBody=嗨！怎样取数字名字呀？不要觉得帅哥玉人都是电脑哦~！想捉住属于你的情缘，昵称不要凌驾6个字。复兴ME+性别+昵称完成注册。
end
elsebegin
ifexists(select*fromchat_userwherephone@phoneandnickname=@nickname)begin
set@msgBody=哇！聪明而灵秀的名字啊，惋惜有人争先一步了。守候你的，是否是心动的相逢呢？快快复兴ME+性别+昵称换一个靓名吧。
end
elsebegin
updatechat_usersetnickname=@nickname,state=1,sex=@sex,chat=1wherephone=@phone
if@sex=男begin
set@msgBody=@nickname+，哇，帅哥来了，让众玉人长远一亮！体验热情约会，冲破就在今晚。复兴MR谈天，寻觅你的梦中恋人。
end
elsebegin
set@msgBody=@nickname+，你宛如彷佛轻云避月，由由然若流风之回雪。旧事如烟，悠悠回忆。复兴MR谈天，感到都会情缘。
end
end
end
end
elsebegin
select@roomid=roomid,@nickname=nicknamefromchat_userwherephone=@phone
set@roomid=rand()*8+1
select@roomname=roomnamefromchat_roomwhereid=@roomid
updatechat_usersetstate=1,roomid=@roomidwherephone=@phone
set@msgBody=@nickname+"，接待回到愿望都会之"+@roomname+"谈天室，邂遇一场恋爱，寻求一段浪漫！复兴MR看房间，复兴MK找伴侣，改昵称复兴ME+性别+昵称"
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseifupper(left(@Content,3))=MKGbegin
set@Content=replace(@Content,+,)
set@srcPhone=left(@srcPhone,6)
ifisnumeric(substring(@Content,4,len(@Content)))=1begin

select@roomid=substring(@Content,4,len(@Content))
end
elsebegin
select@roomid=roomidfromchat_userwherephone=@phone
end
set@msgBody=房间里有：
declareybcursor
forselecttop8nicknamefromchat_userwhereroomid=@roomidandfraction>0andphone@phoneandsex=男orderbyisnumeric(nickname),newid()
openyb
fetchnextfromybinto@nickname
while(@@fetch_status=0)
begin
set@msgBody=@msgBody+@nickname+,
fetchnextfromybinto@nickname
end
closeyb
deallocateyb
select@nickname=nicknamefromchat_userwherephone=@phone
set@msgBody=@msgBody+正在等你,恣意挥洒往吧!复兴MS+昵称约请对方
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseifupper(left(@Content,3))=MKMbegin
set@Content=replace(@Content,+,)
set@srcPhone=left(@srcPhone,6)
ifisnumeric(substring(@Content,4,len(@Content)))=1begin
select@roomid=substring(@Content,4,len(@Content))
end
elsebegin
select@roomid=roomidfromchat_userwherephone=@phone
end
set@msgBody=房间里有:
declareybcursor
forselecttop8nicknamefromchat_userwhereroomid=@roomidandfraction>0andphone@phoneandsex=女orderbyisnumeric(nickname),newid()
openyb
fetchnextfromybinto@nickname
while(@@fetch_status=0)
begin
set@msgBody=@msgBody+@nickname+,
fetchnextfromybinto@nickname
end
closeyb
deallocateyb
select@nickname=nicknamefromchat_userwherephone=@phone
set@msgBody=@msgBody+正在等你,恣意挥洒往吧!复兴MS+昵称约请对方
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseifupper(left(@Content,2))=MKbegin
set@Content=replace(@Content,+,)
set@srcPhone=left(@srcPhone,6)
ifisnumeric(substring(@Content,3,len(@Content)))=1begin
select@roomid=substring(@Content,3,len(@Content))
end
elsebegin
select@roomid=roomidfromchat_userwherephone=@phone
end
set@msgBody=房间里有:
declareybcursor
forselecttop5nickname,sexfromchat_userwhereroomid=@roomidandfraction>0andphone@phoneorderbyisnumeric(nickname),newid()
openyb
fetchnextfromybinto@nickname,@sex
while(@@fetch_status=0)
begin
set@msgBody=@msgBody+@nickname+(+@sex+),
fetchnextfromybinto@nickname,@sex
end
closeyb
deallocateyb
select@nickname=nicknamefromchat_userwherephone=@phone
set@msgBody=@msgBody+正在等你,恣意挥洒往吧!复兴MS+昵称约请对方
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseif@Contentin(1,2,3,4,5,6,7,8,9)begin--进进体系默许谈天室
set@srcPhone=left(@srcPhone,6)
ifexists(select*fromchat_roomwhereid=@Content)begin
updatechat_usersetroomid=@Content,state=1wherephone=@phone
select@welcome=welcomefromchat_roomwhereid=@Content
if@welcomeisnullbegin
set@welcome=
end
set@msgBody=@welcome
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elseifisnumeric(@Content)=1and@Contentnotin(1,2,3,4,5,6,7,8,9)andlen(@Content)<4begin--进进自建谈天室
set@srcPhone=left(@srcPhone,6)
ifexists(select*fromchat_roomwhereid=@Content)begin
updatechat_usersetroomid=@Content,state=1wherephone=@phone
select@welcome=welcomefromchat_roomwhereid=@Content
if@welcomeisnullbegin
set@welcome=
end
set@msgBody=@welcome
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elseifupper(left(@Content,2))=MFbegin
set@srcPhone=left(@srcPhone,6)
set@roomname=left(substring(@Content,3,len(@Content)),12)
ifexists(select*fromchat_userwherephone=@phoneandstate=1)begin
ifexists(select*fromchat_roomwherephone=@phone)begin
updatechat_roomsetroomname=@roomnamewherephone=@phone
select@roomid=idfromchat_roomwherephone=@phone
set@msgBody=谈天室称号修正乐成.复兴+ltrim(str(@roomid))+进进本人的房间复兴MG+接待词修正本人房间的接待词
end
elsebegin
insertintochat_room(phone,roomname)values(@phone,@roomname)
select@roomid=idfromchat_roomwherephone=@phone
set@msgBody=您如今具有本人的谈天室了.复兴+ltrim(str(@roomid))+进进本人的房间复兴MF+房间名修正房间名复兴MG+接待词修正本人房间的接待词
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elseifupper(left(@Content,2))=MGbegin
set@Content=replace(@Content,+,)
set@srcPhone=left(@srcPhone,6)
set@welcome=left(substring(@Content,3,len(@Content)),65)
ifexists(select*fromchat_userwherephone=@phone)begin
ifexists(select*fromchat_roomwherephone=@phone)begin
updatechat_roomsetwelcome=@welcomewherephone=@phone
select@roomid=idfromchat_roomwherephone=@phone
set@msgBody=房间接待词修正乐成.复兴+ltrim(str(@roomid))+进进本人的房间
end
elsebegin
set@msgBody=复兴MF+房间名创立房间名复兴MG+接待词修正本人房间的接待词
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elseifupper(left(@Content,2))=MSbegin
set@Content=replace(@Content,+,)
set@srcPhone=left(@srcPhone,6)
set@nickname=substring(@Content,3,len(@Content))
ifexists(select*fromchat_userwherenickname=@nicknameandstate=1)begin
select@userid=userid,@tophone=phonefromchat_userwherenickname=@nickname
set@msgBody=间接复兴谈天内容便可与+@nickname+私聊，MC封闭/开启群聊，免他人打搅。
set@SrcPhone=left(@SrcPhone,6)+ltrim(str(@userid))
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elseifupper(@Content)=MRbegin
set@srcPhone=left(@srcPhone,6)
set@msgBody=
declareybcursor
forselecttop5id,roomname,onlinefromchat_roomorderbyonlinedesc
openyb
fetchnextfromybinto@roomid,@roomname,@online
while(@@fetch_status=0)
begin
set@msgBody=@msgBody+ltrim(str(@roomid))+.+@roomname+(+ltrim(str(@online))+人)+char(13)
fetchnextfromybinto@roomid,@roomname,@online
end
closeyb
deallocateyb
set@msgBody=@msgBody+复兴房间编号进进.
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseifupper(@Content)=MYbegin
set@srcPhone=left(@srcPhone,6)
ifexists(select*fromchat_roomwherephone=@phone)begin
updatechat_usersetroomid=(selectidfromchat_roomwherephone=@phone)wherephone=@phone
set@msgBody=您已进进本人的房间
end
elsebegin
set@msgBody=没有创立
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseifupper(left(@Content,2))=MTbegin
set@Content=replace(@Content,+,)
ifexists(select*fromchat_roomwherephone=@phone)begin
ifisnumeric(substring(@Content,3,len(@Content)))=1andexists(select*fromchat_userwhereuserid=substring(@Content,3,len(@Content)))begin
select@userid=userid,@SrcPhone=srcphone,@province=province,@tophone=phone,@nickname=nickname,@roomid=roomidfromchat_userwhereuserid=substring(@Content,3,len(@Content))
end
elseifexists(select*fromchat_userwherenickname=substring(@Content,3,len(@Content)))begin
select@userid=userid,@SrcPhone=srcphone,@province=province,@tophone=phone,@nickname=nickname,@roomid=roomidfromchat_userwherenickname=substring(@Content,3,len(@Content))
end
ifexists(select*fromchat_roomwhereid=@roomidandphone=@phone)begin
set@roomid=rand()*7+1
select@roomname=roomnamefromchat_roomwhereid=@roomid
updatechat_usersetroomid=@roomidwherephone=@tophone
set@msgBody=@nickname+"，接待离开愿望都会之"+@roomname+"谈天室，邂遇一场恋爱，寻求一段浪漫！复兴MR看房间，复兴MK找伴侣，改昵称复兴ME+性别+昵称"

ifnotexists(select*fromtest_phonewherephone=@tophone)begin
ifexists(select*fromfree_phonewherephonenumber=@tophoneandfreesrvid=520LT)begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=2371begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=LTS
end
elseif@Province=2571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=YXG
end

if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
end
end
end
elsebegin
set@msgBody=您的房间里有这团体吗？我怎样没有找到呢？
select@SrcPhone=srcphone,@province=provincefromchat_userwherephone=@phone
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)and@msgBodyisnotnullbegin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elsebegin
set@msgBody=您还没有创立本人的房间呢，怎样就踢他人啊？从速复兴MF+房间称号创立本人的房间吧！体验一下本人做办理员的味道！！
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)and@msgBodyisnotnullbegin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
elseifupper(left(@Content,2))=MCorleft(@Content,4)=0000begin
updatechat_usersetchat=1^chatwherephone=@phone
select@chat=chatfromchat_userwherephone=@phone
set@msgBody=您已封闭群聊功效，不吸收群聊信息。复兴MC开启群聊功效。
if@chat=1begin
set@msgBody=您已开启群聊功效，吸收群聊信息。复兴MC封闭群聊功效。
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
elseifupper(left(@Content,2))=MQbegin--分开
set@srcPhone=left(@srcPhone,6)
updatechat_usersetstate=0,roomid=nullwherephone=@phone
set@msgBody=丢失的情感，庞杂的胶葛，交叉的爱意...我决意退隐江湖，过一段隐居生存。这时代将收不到谈天信息。发送ME到+@srcPhone+重拾都会情缘
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
/*
--检察用户信息
elseifupper(left(@Content,2))=MMbegin
set@msgBody=null
ifisnumeric(substring(@Content,3,len(@Content)))=1begin
select@msgBody=phonefromchat_userwhereuserid=substring(@Content,3,len(@Content))
end
elsebegin
select@msgBody=phonefromchat_userwherenickname=substring(@Content,3,len(@Content))
end
if@debug1andnotexists(select*fromtest_phonewherephone=@phone)and@msgBodyisnotnullbegin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
*/
elseiflen(@SrcPhone)>6begin--私聊
set@userid=substring(@SrcPhone,6,len(@SrcPhone))
ifexists(select*fromchat_userwherestate=1anduserid=@userid)begin
set@tophone=(selectphonefromchat_userwherestate=1anduserid=@userid)
select@nickname=nickname,@userid=userid,@sex=sex,@roomid=roomidfromchat_userwherephone=@phone
if@sexnullbegin
set@msgBody=""+@nickname+"("+@sex+)对你说:+@Content
end
elsebegin
set@msgBody=""+@nickname+"偷偷的对你说:"+@Content
end
set@srcPhone=left(@srcphone,6)+ltrim(str(@userid))
iflen(@msgBody)>0begin

select@srcphone=srcphone,@province=provincefromchat_userwherephone=@tophone
set@SrcPhone=@srcPhone+ltrim(str(@userid))

ifexists(select*fromchat_clewwhere(len(content)<(68-len(@msgBody)))and(province=@provinceorprovinceisnull))begin
selecttop1@clew=contentfromchat_clewwhere(len(content)<(68-len(@msgBody)))and(province=@provinceorprovinceisnull)orderbynewid()
set@msgBody=@msgBody+@clew
end

ifnotexists(select*fromtest_phonewherephone=@tophone)begin
ifexists(select*fromfree_phonewherephonenumber=@tophoneandfreesrvid=520LT)begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=2371begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=LTS
end
elseif@Province=2571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=YXG
end

if@debug1andnotexists(select*fromtest_phonewherephone=@tophone)begin
while(len(@msgBody)>0)
begin

insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,left(@msgBody,70),@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
set@msgBody=substring(@msgBody,71,len(@msgBody))
end
end
end
select@tonickname=nicknamefromchat_userwherephone=@tophone
set@msgBody=""+@nickname+"("+@sex+")对"+@tonickname+"说:"+@Content
if@msgBodynullbegin
insertintochat_log(phone,srcphone,msgbody,roomid,tophone)values(@phone,@srcPhone,@msgBody,@roomid,@tophone)
end
end
end
end
elsebegin
select@roomid=roomid,@nickname=nickname,@sex=sex,@state=statefromchat_userwherephone=@phone
if@roomidnulland@state=1andexists(select*fromchat_userwhereroomid=@roomidandphone@phone)begin

set@msgBody=+"+@nickname+(+@sex+)"对人人说:+@Content

if@msgBodynullbegin
insertintochat_log(phone,srcphone,msgbody,roomid)values(@phone,@srcPhone,@msgBody,@roomid)
end

declareybcursor
forselectphonefromchat_userwhereroomid=@roomidandphone@phoneandstate=1andchat=1
openyb
fetchnextfromybinto@tophone
while(@@fetch_status=0)
begin
select@srcphone=srcphone,@province=provincefromchat_userwherephone=@tophone

ifnotexists(select*fromtest_phonewherephone=@tophone)begin

ifexists(select*fromfree_phonewherephonenumber=@tophoneandfreesrvid=520LT)begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=2371begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=LTS
end
elseif@Province=2571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=520LT
end
elseif@Province=571begin
set@FeeCode=0
set@FeeType=1
set@FeeSevID=YXG
end
if@debug1andnotexists(select*fromtest_phonewherephone=@tophone)begin

declare@tempBodynvarchar(512)

--拔出告白
set@tempBody=@msgBody
ifexists(select*fromchat_clewwhere(len(content)<(68-len(@tempBody)))and(province=@provinceorprovinceisnull))begin
selecttop1@clew=contentfromchat_clewwhere(len(content)<(68-len(@tempBody)))and(province=@provinceorprovinceisnull)orderbynewid()
set@tempBody=@tempBody+@clew
end

while(len(@tempBody)>0)
begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,left(@tempBody,70),@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
set@tempBody=substring(@tempBody,71,len(@tempBody))
end
end
end
fetchnextfromybinto@tophone
end
closeyb
deallocateyb
end
if@roomid=nullbegin
set@msgBody=复兴编号进进房间+char(13)
declareybcursor
forselecttop4id,roomname,onlinefromchat_roomorderbynewid()
openyb
fetchnextfromybinto@roomid,@roomname,@online
while(@@fetch_status=0)
begin
set@msgBody=@msgBody+ltrim(str(@roomid))+.+@roomname+(+ltrim(str(@online))+人)+char(13)
fetchnextfromybinto@roomid,@roomname,@online
end
closeyb
deallocateyb

if@debug1andnotexists(select*fromtest_phonewherephone=@phone)begin
insertintoAirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID)values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
end
GO

SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO

SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO

/******Object:Triggerdbo.update_roomScriptDate:2004-9-278:18:56******/
CREATETRIGGER[update_room]ON[dbo].[chat_user]
FORINSERT,UPDATE,DELETE
AS
updatersetr.online=(selectcount(chat_user.phone)fromchat_userwherechat_user.roomid=r.idgroupbychat_user.roomid)fromchat_roomr,chat_useruwherer.id=u.roomid
GO

SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


execsp_addextendedpropertyNMS_Description,N服务号（挪动2788，联通8788）,Nuser,Ndbo,Ntable,Nchat_user,Ncolumn,NsrcPhone


GO


execsp_addextendedpropertyNMS_Description,N收费的服务种别,Nuser,Ndbo,Ntable,NFREE_PHONE,Ncolumn,NFreeSrvId
GO
execsp_addextendedpropertyNMS_Description,N收费德律风号码,Nuser,Ndbo,Ntable,NFREE_PHONE,Ncolumn,NPhoneNumber


GO

相干文章：http://blog.csdn.net/iuhxq/archive/2004/09/24/115990.aspx
出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。

如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。

我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

我个人认为就是孜孜不懈的学习

连做梦都在想页面结构是怎么样的，绝非虚言

外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

代替了原来VB式的错误判断。比Oracle高级不少。

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。