/************************************************ * * 2004.10.27 * RYL_PART2_CREATE_TABLE_VIEW_PROC_FULL_VERSION * **************************************************/ GO CREATE TABLE [dbo].[AdminList] ( [UID] [int] NOT NULL , [Level] [smallint] NULL , [IP_S] [varchar] (16) NULL , [IP_E] [varchar] (16) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CIDStore] ( [UID] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharInfoEx] ( [UID] [int] NOT NULL , [Total] [int] NULL , [ServerID] [int] NULL , [Config] [binary] (50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharInfo_1] ( [UID] [int] NOT NULL , [Name] [varchar] (16) NULL , [Sex] [tinyint] NULL , [Hair] [tinyint] NULL , [Face] [tinyint] NULL , [Nationality] [tinyint] NULL , [Class] [smallint] NULL , [Fame] [int] NULL , [Mileage] [int] NULL , [Guild] [int] NULL , [Party] [int] NULL , [Level] [tinyint] NULL , [Equip] [binary] (30) NULL , [Gold] [int] NULL , [IP] [smallint] NULL , [STR] [smallint] NULL , [DEX] [smallint] NULL , [CON] [smallint] NULL , [Intell] [smallint] NULL , [WIS] [smallint] NULL , [HP] [smallint] NULL , [MP] [smallint] NULL , [Exp] [bigint] NULL , [Chance] [tinyint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharInfo_2] ( [UID] [int] NOT NULL , [Name] [varchar] (16) NULL , [Sex] [tinyint] NULL , [Hair] [tinyint] NULL , [Face] [tinyint] NULL , [Nationality] [tinyint] NULL , [Class] [smallint] NULL , [Fame] [int] NULL , [Mileage] [int] NULL , [Guild] [int] NULL , [Party] [int] NULL , [Level] [tinyint] NULL , [Equip] [binary] (30) NULL , [Gold] [int] NULL , [IP] [smallint] NULL , [STR] [smallint] NULL , [DEX] [smallint] NULL , [CON] [smallint] NULL , [Intell] [smallint] NULL , [WIS] [smallint] NULL , [HP] [smallint] NULL , [MP] [smallint] NULL , [Exp] [bigint] NULL , [Chance] [tinyint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharInfo_3] ( [UID] [int] NOT NULL , [Name] [varchar] (16) NULL , [Sex] [tinyint] NULL , [Hair] [tinyint] NULL , [Face] [tinyint] NULL , [Nationality] [tinyint] NULL , [Class] [smallint] NULL , [Fame] [int] NULL , [Mileage] [int] NULL , [Guild] [int] NULL , [Party] [int] NULL , [Level] [tinyint] NULL , [Equip] [binary] (30) NULL , [Gold] [int] NULL , [IP] [smallint] NULL , [STR] [smallint] NULL , [DEX] [smallint] NULL , [CON] [smallint] NULL , [Intell] [smallint] NULL , [WIS] [smallint] NULL , [HP] [smallint] NULL , [MP] [smallint] NULL , [Exp] [bigint] NULL , [Chance] [tinyint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharInfo_4] ( [UID] [int] NOT NULL , [Name] [varchar] (16) NULL , [Sex] [tinyint] NULL , [Hair] [tinyint] NULL , [Face] [tinyint] NULL , [Nationality] [tinyint] NULL , [Class] [smallint] NULL , [Fame] [int] NULL , [Mileage] [int] NULL , [Guild] [int] NULL , [Party] [int] NULL , [Level] [tinyint] NULL , [Equip] [binary] (30) NULL , [Gold] [int] NULL , [IP] [smallint] NULL , [STR] [smallint] NULL , [DEX] [smallint] NULL , [CON] [smallint] NULL , [Intell] [smallint] NULL , [WIS] [smallint] NULL , [HP] [smallint] NULL , [MP] [smallint] NULL , [Exp] [bigint] NULL , [Chance] [tinyint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharInfo_Time] ( [CID] [int] NOT NULL , [CreateTime] [smalldatetime] NULL , [UpdateTime] [smalldatetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharItem] ( [UID] [int] NOT NULL , [Quick] [binary] (100) NULL , [Equip] [binary] (1440) NULL , [Inventory] [binary] (5120) NULL , [Extra] [binary] (512) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharItemEx] ( [UID] [int] NOT NULL , [Exchange] [binary] (1284) NULL , [TempInven] [binary] (1440) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharPos_1] ( [UID] [int] NOT NULL , [LastPointX] [real] NULL , [LastPointY] [real] NULL , [LastPointZ] [real] NULL , [SavePointX] [real] NULL , [SavePointY] [real] NULL , [SavePointZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharPos_2] ( [UID] [int] NOT NULL , [LastPointX] [real] NULL , [LastPointY] [real] NULL , [LastPointZ] [real] NULL , [SavePointX] [real] NULL , [SavePointY] [real] NULL , [SavePointZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharPos_3] ( [UID] [int] NOT NULL , [LastPointX] [real] NULL , [LastPointY] [real] NULL , [LastPointZ] [real] NULL , [SavePointX] [real] NULL , [SavePointY] [real] NULL , [SavePointZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharPos_4] ( [UID] [int] NOT NULL , [LastPointX] [real] NULL , [LastPointY] [real] NULL , [LastPointZ] [real] NULL , [SavePointX] [real] NULL , [SavePointY] [real] NULL , [SavePointZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharSkill_1] ( [UID] [int] NOT NULL , [Skill] [binary] (84) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharSkill_2] ( [UID] [int] NOT NULL , [Skill] [binary] (84) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharSkill_3] ( [UID] [int] NOT NULL , [Skill] [binary] (84) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharSkill_4] ( [UID] [int] NOT NULL , [Skill] [binary] (84) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Friend] ( [UID] [int] NOT NULL , [Friend] [binary] (2404) NULL , [Ban] [binary] (2004) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ItemStore1] ( [UID] [int] NOT NULL , [Password] [char] (5) NULL , [Frag] [int] NULL , [Gold] [int] NULL , [Store] [binary] (7300) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ItemStore2] ( [UID] [int] NOT NULL , [Store] [binary] (7300) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ItemUID] ( [Server] [int] NOT NULL , [Item] [bigint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PIDStore] ( [UID] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PartyInfo_1] ( [UID] [int] NOT NULL , [Party] [binary] (200) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PartyInfo_2] ( [UID] [int] NOT NULL , [Party] [binary] (200) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PartyInfo_3] ( [UID] [int] NOT NULL , [Party] [binary] (200) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PartyInfo_4] ( [UID] [int] NOT NULL , [Party] [binary] (200) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Quest] ( [UID] [int] NOT NULL , [Quest] [binary] (134) NULL , [History] [binary] (204) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblCampInfo] ( [nCampID] [int] IDENTITY (1, 1) NOT NULL , [nGuildID] [int] NULL , [nHP] [int] NULL , [tnZone] [tinyint] NULL , [tnChannel] [tinyint] NULL , [tnState] [tinyint] NULL , [tnUpgradeStep] [tinyint] NULL , [RemainTime] [smalldatetime] NULL , [LastUseTime] [smalldatetime] NULL , [bRight] [binary] (10) NULL , [fPosX] [real] NULL , [fPosY] [real] NULL , [fPosZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblCastleCreatureInfo] ( [nCreatureID] [int] IDENTITY (1, 1) NOT NULL , [nCastleID] [int] NULL , [nHP] [int] NULL , [snObjectType] [smallint] NULL , [fDirection] [real] NULL , [tnState] [tinyint] NULL , [tnSubState] [tinyint] NULL , [tnUpgradeStep] [tinyint] NULL , [tnUpgradeType] [tinyint] NULL , [RemainTime] [smalldatetime] NULL , [fPosX] [real] NULL , [fPosY] [real] NULL , [fPosZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblCastleInfo] ( [nCastleID] [int] IDENTITY (1, 1) NOT NULL , [nGuildID] [int] NULL , [snTax] [smallint] NULL , [nTaxMoney] [int] NULL , [tnZone] [tinyint] NULL , [tnTaxIncomeRemainDay] [tinyint] NULL , [tnInvincibleDay] [tinyint] NULL , [LastSiegeTime] [smalldatetime] NULL , [bRight] [binary] (10) NULL , [strCastleName] [varchar] (50) NULL , [fPosInX] [real] NULL , [fPosInY] [real] NULL , [fPosInZ] [real] NULL , [fPosOutX] [real] NULL , [fPosOutY] [real] NULL , [fPosOutZ] [real] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblCharDelHistory] ( [intCID] [int] NOT NULL , [intUID] [int] NOT NULL , [dateInsertTime] [smalldatetime] NOT NULL , [dateDeleteTime] [smalldatetime] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblCharDelHistory_LOG] ( [intCID] [int] NULL , [intUID] [int] NULL , [dateInsertTime] [smalldatetime] NULL , [dateDeleteTime] [smalldatetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblEventItem] ( [nItemIndex] [int] NULL , [nItemAmount] [int] NULL , [nMaxAmount] [int] NULL , [StartTime] [smalldatetime] NULL , [EndTime] [smalldatetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildGold_Log] ( [nGuildID] [int] NOT NULL , [tInsertTime] [datetime] NOT NULL , [strCharName] [varchar] (20) NOT NULL , [tnStrengthType] [tinyint] NOT NULL , [snPosition] [smallint] NOT NULL , [tnStrengthLevel] [tinyint] NOT NULL , [nLostGold] [int] NOT NULL , [nGetGold] [int] NOT NULL , [nRemainGold] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildInfo] ( [nGuildID] [int] IDENTITY (10001, 1) NOT NULL , [strGuildName] [varchar] (20) NOT NULL , [tnGuildLevel] [tinyint] NOT NULL , [nGuildFame] [int] NOT NULL , [nGuildGold] [int] NOT NULL , [tLastLogout] [smalldatetime] NOT NULL , [tCheckMember] [smalldatetime] NOT NULL , [tGMLastLogout] [smalldatetime] NULL , [bGuildMark] [binary] (433) NULL , [bRight] [binary] (50) NULL , [tnNationType] [tinyint] NULL , [tChangeState] [smalldatetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildItem] ( [nGuildID] [int] NOT NULL , [bGuildItem] [binary] (7300) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildItem_EX] ( [nGuildID] [int] NOT NULL , [bGuildItem] [binary] (7300) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildItem_log] ( [nGuildID] [int] NULL , [tInsertTime] [datetime] NULL , [strCharName] [varchar] (20) NULL , [nItemID] [int] NULL , [nItemType] [int] NULL , [nItemStrengthStep] [int] NULL , [nReason] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildMember] ( [nGuildID] [int] NOT NULL , [nCID] [int] NOT NULL , [nPosition] [int] NOT NULL , [nPositionInGuild] [int] NULL , [tLeaveGuildTime] [smalldatetime] NULL , [nCampID] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildMsg] ( [nMsgID] [int] IDENTITY (1, 1) NOT NULL , [nGuildID] [int] NOT NULL , [strCharName] [varchar] (20) NOT NULL , [tInsertTime] [smalldatetime] NOT NULL , [strTitle] [varchar] (60) NOT NULL , [strMsgType] [varchar] (10) NULL , [nClass] [int] NULL , [nLevel] [int] NULL , [strContent] [varchar] (4000) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildOtherList] ( [nGuildID] [int] NOT NULL , [nOtherGuildID] [int] NOT NULL , [tnRelation] [tinyint] NOT NULL , [tnState] [tinyint] NOT NULL , [waitTime] [smalldatetime] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildRestoreData] ( [nGuildID] [int] NULL , [strGuildName] [varchar] (20) NULL , [nGuildGold] [int] NULL , [bGuildItem] [binary] (7300) NULL , [tDeleteTime] [smalldatetime] NULL , [strGuildMName] [varchar] (20) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblGuildRestoreData_EX] ( [nGuildID] [int] NULL , [bGuildItem_EX] [binary] (7300) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TblItemLog] ( [InsertTime] [datetime] NULL , [UpdateTime] [datetime] NULL , [UID] [int] NULL , [CID] [int] NULL , [ItemSerial] [bigint] NULL , [CharName] [varchar] (20) NULL , [Qty] [int] NULL , [CheckField] [bit] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[UserInfo_1] ( [UID] [int] NOT NULL , [Char1] [int] NULL , [Char2] [int] NULL , [Char3] [int] NULL , [Char4] [int] NULL , [Char5] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[UserInfo_2] ( [UID] [int] NOT NULL , [Char1] [int] NULL , [Char2] [int] NULL , [Char3] [int] NULL , [Char4] [int] NULL , [Char5] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[UserInfo_3] ( [UID] [int] NOT NULL , [Char1] [int] NULL , [Char2] [int] NULL , [Char3] [int] NULL , [Char4] [int] NULL , [Char5] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[UserInfo_4] ( [UID] [int] NOT NULL , [Char1] [int] NULL , [Char2] [int] NULL , [Char3] [int] NULL , [Char4] [int] NULL , [Char5] [int] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[AdminList] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharInfoEx] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharInfo_1] WITH NOCHECK ADD CONSTRAINT [PK__CharInfo_1__7A8729A3] PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharInfo_2] WITH NOCHECK ADD CONSTRAINT [PK__CharInfo_2__0F824689] PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharInfo_3] WITH NOCHECK ADD CONSTRAINT [PK__CharInfo_3__247D636F] PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharInfo_4] WITH NOCHECK ADD CONSTRAINT [PK__CharInfo_4__39788055] PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharInfo_Time] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [CID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharItem] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharItemEx] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharPos_1] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharPos_2] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharPos_3] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharPos_4] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharSkill_1] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharSkill_2] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharSkill_3] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharSkill_4] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Friend] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ItemStore1] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ItemStore2] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ItemUID] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [Server] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PartyInfo_1] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PartyInfo_2] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PartyInfo_3] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PartyInfo_4] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Quest] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblCampInfo] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [nCampID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblCastleCreatureInfo] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [nCreatureID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblCharDelHistory] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [intCID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblGuildInfo] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [nGuildID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblGuildItem] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [nGuildID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblGuildItem_EX] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [nGuildID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblGuildMember] WITH NOCHECK ADD CONSTRAINT [PK_GuildMember_Guildid_nCid] PRIMARY KEY CLUSTERED ( [nGuildID], [nCID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblGuildOtherList] WITH NOCHECK ADD CONSTRAINT [PK_TblGuildOtherList_1] PRIMARY KEY CLUSTERED ( [nGuildID], [nOtherGuildID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserInfo_1] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserInfo_2] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserInfo_3] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserInfo_4] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [UID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[AdminList] ADD CONSTRAINT [DF__AdminList__Level__150615B5] DEFAULT (0) FOR [Level] GO ALTER TABLE [dbo].[CharInfoEx] ADD CONSTRAINT [DF__CharInfoE__Total__5CC1BC92] DEFAULT (0) FOR [Total], CONSTRAINT [DF__CharInfoE__Serve__5DB5E0CB] DEFAULT (0) FOR [ServerID] GO ALTER TABLE [dbo].[CharInfo_1] ADD CONSTRAINT [DF__CharInfo_1__Sex__7C6F7215] DEFAULT (0) FOR [Sex], CONSTRAINT [DF__CharInfo_1__Hair__7D63964E] DEFAULT (0) FOR [Hair], CONSTRAINT [DF__CharInfo_1__Face__7E57BA87] DEFAULT (0) FOR [Face], CONSTRAINT [DF__CharInfo___Natio__7F4BDEC0] DEFAULT (0) FOR [Nationality], CONSTRAINT [DF__CharInfo___Class__004002F9] DEFAULT (1) FOR [Class], CONSTRAINT [DF__CharInfo___Merit__01342732] DEFAULT (0) FOR [Fame], CONSTRAINT [DF__CharInfo_1__Fame__02284B6B] DEFAULT (0) FOR [Mileage], CONSTRAINT [DF__CharInfo___Guild__031C6FA4] DEFAULT (0) FOR [Guild], CONSTRAINT [DF__CharInfo___Party__041093DD] DEFAULT (0) FOR [Party], CONSTRAINT [DF__CharInfo___Level__0504B816] DEFAULT (1) FOR [Level], CONSTRAINT [DF__CharInfo_1__IP__05F8DC4F] DEFAULT (0) FOR [IP], CONSTRAINT [DF__CharInfo_1__STR__06ED0088] DEFAULT (0) FOR [STR], CONSTRAINT [DF__CharInfo_1__DEX__07E124C1] DEFAULT (0) FOR [DEX], CONSTRAINT [DF__CharInfo_1__CON__08D548FA] DEFAULT (0) FOR [CON], CONSTRAINT [DF__CharInfo___Intel__09C96D33] DEFAULT (0) FOR [Intell], CONSTRAINT [DF__CharInfo_1__WIS__0ABD916C] DEFAULT (0) FOR [WIS], CONSTRAINT [DF__CharInfo_1__HP__0BB1B5A5] DEFAULT (0) FOR [HP], CONSTRAINT [DF__CharInfo_1__MP__0CA5D9DE] DEFAULT (0) FOR [MP], CONSTRAINT [DF__CharInfo_1__Exp__0D99FE17] DEFAULT (0) FOR [Exp], CONSTRAINT [DF__CharInfo___Chanc__7CF981FA] DEFAULT (0) FOR [Chance], CONSTRAINT [CK__CharInfo_1__UID__7B7B4DDC] CHECK ([UID] % 4 = 0) GO ALTER TABLE [dbo].[CharInfo_2] ADD CONSTRAINT [DF__CharInfo_2__Sex__116A8EFB] DEFAULT (0) FOR [Sex], CONSTRAINT [DF__CharInfo_2__Hair__125EB334] DEFAULT (0) FOR [Hair], CONSTRAINT [DF__CharInfo_2__Face__1352D76D] DEFAULT (0) FOR [Face], CONSTRAINT [DF__CharInfo___Natio__1446FBA6] DEFAULT (0) FOR [Nationality], CONSTRAINT [DF__CharInfo___Class__153B1FDF] DEFAULT (1) FOR [Class], CONSTRAINT [DF__CharInfo___Merit__162F4418] DEFAULT (0) FOR [Fame], CONSTRAINT [DF__CharInfo_2__Fame__17236851] DEFAULT (0) FOR [Mileage], CONSTRAINT [DF__CharInfo___Guild__18178C8A] DEFAULT (0) FOR [Guild], CONSTRAINT [DF__CharInfo___Party__190BB0C3] DEFAULT (0) FOR [Party], CONSTRAINT [DF__CharInfo___Level__19FFD4FC] DEFAULT (1) FOR [Level], CONSTRAINT [DF__CharInfo_2__IP__1AF3F935] DEFAULT (0) FOR [IP], CONSTRAINT [DF__CharInfo_2__STR__1BE81D6E] DEFAULT (0) FOR [STR], CONSTRAINT [DF__CharInfo_2__DEX__1CDC41A7] DEFAULT (0) FOR [DEX], CONSTRAINT [DF__CharInfo_2__CON__1DD065E0] DEFAULT (0) FOR [CON], CONSTRAINT [DF__CharInfo___Intel__1EC48A19] DEFAULT (0) FOR [Intell], CONSTRAINT [DF__CharInfo_2__WIS__1FB8AE52] DEFAULT (0) FOR [WIS], CONSTRAINT [DF__CharInfo_2__HP__20ACD28B] DEFAULT (0) FOR [HP], CONSTRAINT [DF__CharInfo_2__MP__21A0F6C4] DEFAULT (0) FOR [MP], CONSTRAINT [DF__CharInfo_2__Exp__22951AFD] DEFAULT (0) FOR [Exp], CONSTRAINT [DF__CharInfo___Chanc__7DEDA633] DEFAULT (0) FOR [Chance], CONSTRAINT [CK__CharInfo_2__UID__10766AC2] CHECK ([UID] % 4 = 1) GO ALTER TABLE [dbo].[CharInfo_3] ADD CONSTRAINT [DF__CharInfo_3__Sex__2665ABE1] DEFAULT (0) FOR [Sex], CONSTRAINT [DF__CharInfo_3__Hair__2759D01A] DEFAULT (0) FOR [Hair], CONSTRAINT [DF__CharInfo_3__Face__284DF453] DEFAULT (0) FOR [Face], CONSTRAINT [DF__CharInfo___Natio__2942188C] DEFAULT (0) FOR [Nationality], CONSTRAINT [DF__CharInfo___Class__2A363CC5] DEFAULT (1) FOR [Class], CONSTRAINT [DF__CharInfo___Merit__2B2A60FE] DEFAULT (0) FOR [Fame], CONSTRAINT [DF__CharInfo_3__Fame__2C1E8537] DEFAULT (0) FOR [Mileage], CONSTRAINT [DF__CharInfo___Guild__2D12A970] DEFAULT (0) FOR [Guild], CONSTRAINT [DF__CharInfo___Party__2E06CDA9] DEFAULT (0) FOR [Party], CONSTRAINT [DF__CharInfo___Level__2EFAF1E2] DEFAULT (1) FOR [Level], CONSTRAINT [DF__CharInfo_3__IP__2FEF161B] DEFAULT (0) FOR [IP], CONSTRAINT [DF__CharInfo_3__STR__30E33A54] DEFAULT (0) FOR [STR], CONSTRAINT [DF__CharInfo_3__DEX__31D75E8D] DEFAULT (0) FOR [DEX], CONSTRAINT [DF__CharInfo_3__CON__32CB82C6] DEFAULT (0) FOR [CON], CONSTRAINT [DF__CharInfo___Intel__33BFA6FF] DEFAULT (0) FOR [Intell], CONSTRAINT [DF__CharInfo_3__WIS__34B3CB38] DEFAULT (0) FOR [WIS], CONSTRAINT [DF__CharInfo_3__HP__35A7EF71] DEFAULT (0) FOR [HP], CONSTRAINT [DF__CharInfo_3__MP__369C13AA] DEFAULT (0) FOR [MP], CONSTRAINT [DF__CharInfo_3__Exp__379037E3] DEFAULT (0) FOR [Exp], CONSTRAINT [DF__CharInfo___Chanc__7EE1CA6C] DEFAULT (0) FOR [Chance], CONSTRAINT [CK__CharInfo_3__UID__257187A8] CHECK ([UID] % 4 = 2) GO ALTER TABLE [dbo].[CharInfo_4] ADD CONSTRAINT [DF__CharInfo_4__Sex__3B60C8C7] DEFAULT (0) FOR [Sex], CONSTRAINT [DF__CharInfo_4__Hair__3C54ED00] DEFAULT (0) FOR [Hair], CONSTRAINT [DF__CharInfo_4__Face__3D491139] DEFAULT (0) FOR [Face], CONSTRAINT [DF__CharInfo___Natio__3E3D3572] DEFAULT (0) FOR [Nationality], CONSTRAINT [DF__CharInfo___Class__3F3159AB] DEFAULT (1) FOR [Class], CONSTRAINT [DF__CharInfo___Merit__40257DE4] DEFAULT (0) FOR [Fame], CONSTRAINT [DF__CharInfo_4__Fame__4119A21D] DEFAULT (0) FOR [Mileage], CONSTRAINT [DF__CharInfo___Guild__420DC656] DEFAULT (0) FOR [Guild], CONSTRAINT [DF__CharInfo___Party__4301EA8F] DEFAULT (0) FOR [Party], CONSTRAINT [DF__CharInfo___Level__43F60EC8] DEFAULT (1) FOR [Level], CONSTRAINT [DF__CharInfo_4__IP__44EA3301] DEFAULT (0) FOR [IP], CONSTRAINT [DF__CharInfo_4__STR__45DE573A] DEFAULT (0) FOR [STR], CONSTRAINT [DF__CharInfo_4__DEX__46D27B73] DEFAULT (0) FOR [DEX], CONSTRAINT [DF__CharInfo_4__CON__47C69FAC] DEFAULT (0) FOR [CON], CONSTRAINT [DF__CharInfo___Intel__48BAC3E5] DEFAULT (0) FOR [Intell], CONSTRAINT [DF__CharInfo_4__WIS__49AEE81E] DEFAULT (0) FOR [WIS], CONSTRAINT [DF__CharInfo_4__HP__4AA30C57] DEFAULT (0) FOR [HP], CONSTRAINT [DF__CharInfo_4__MP__4B973090] DEFAULT (0) FOR [MP], CONSTRAINT [DF__CharInfo_4__Exp__4C8B54C9] DEFAULT (0) FOR [Exp], CONSTRAINT [DF__CharInfo___Chanc__7FD5EEA5] DEFAULT (0) FOR [Chance], CONSTRAINT [CK__CharInfo_4__UID__3A6CA48E] CHECK ([UID] % 4 = 3) GO ALTER TABLE [dbo].[CharPos_1] ADD CHECK ([UID] % 4 = 0) GO ALTER TABLE [dbo].[CharPos_2] ADD CHECK ([UID] % 4 = 1) GO ALTER TABLE [dbo].[CharPos_3] ADD CHECK ([UID] % 4 = 2) GO ALTER TABLE [dbo].[CharPos_4] ADD CHECK ([UID] % 4 = 3) GO ALTER TABLE [dbo].[CharSkill_1] ADD CHECK ([UID] % 4 = 0) GO ALTER TABLE [dbo].[CharSkill_2] ADD CHECK ([UID] % 4 = 1) GO ALTER TABLE [dbo].[CharSkill_3] ADD CHECK ([UID] % 4 = 2) GO ALTER TABLE [dbo].[CharSkill_4] ADD CHECK ([UID] % 4 = 3) GO ALTER TABLE [dbo].[ItemStore1] ADD CONSTRAINT [DF__ItemStore__Passw__73A521EA] DEFAULT ('0000') FOR [Password] GO ALTER TABLE [dbo].[ItemUID] ADD CONSTRAINT [DF__ItemUID__Item__70C8B53F] DEFAULT (0) FOR [Item] GO ALTER TABLE [dbo].[PartyInfo_1] ADD CHECK ([UID] % 4 = 0) GO ALTER TABLE [dbo].[PartyInfo_2] ADD CHECK ([UID] % 4 = 1) GO ALTER TABLE [dbo].[PartyInfo_3] ADD CHECK ([UID] % 4 = 2) GO ALTER TABLE [dbo].[PartyInfo_4] ADD CHECK ([UID] % 4 = 3) GO ALTER TABLE [dbo].[TblCharDelHistory] ADD CONSTRAINT [DF__TblCharDe__dateI__0C3BC58A] DEFAULT (getdate()) FOR [dateInsertTime] GO ALTER TABLE [dbo].[TblGuildInfo] ADD CONSTRAINT [DF__TblGuildI__nGuil__13DCE752] DEFAULT (0) FOR [nGuildGold], CONSTRAINT [DF__TblGuildI__tLast__14D10B8B] DEFAULT ('2010.12.31') FOR [tLastLogout], CONSTRAINT [DF__TblGuildI__tChec__15C52FC4] DEFAULT (getdate()) FOR [tCheckMember], CONSTRAINT [DF__TblGuildI__tGMLa__16B953FD] DEFAULT ('2010.12.31') FOR [tGMLastLogout], CONSTRAINT [uk_TblGuildInfo_strGuildName] UNIQUE NONCLUSTERED ( [strGuildName] ) ON [PRIMARY] , CHECK ([strGuildName] <> null) GO ALTER TABLE [dbo].[TblGuildMember] ADD CONSTRAINT [DF__TblGuildM__nCamp__36470DEF] DEFAULT (0) FOR [nCampID] GO ALTER TABLE [dbo].[TblGuildMsg] ADD PRIMARY KEY NONCLUSTERED ( [nMsgID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TblGuildOtherList] ADD CHECK ([tnRelation] = 0 or [tnRelation] = 1 or [tnRelation] = 2) GO ALTER TABLE [dbo].[TblItemLog] ADD CONSTRAINT [DF__TblItemLo__Inser__4A4E069C] DEFAULT (getdate()) FOR [InsertTime], CONSTRAINT [DF__TblItemLo__Updat__4B422AD5] DEFAULT (getdate()) FOR [UpdateTime], CONSTRAINT [DF__TblItemLo__Check__4D2A7347] DEFAULT (0) FOR [CheckField], CHECK ([Qty] > 0) GO CREATE INDEX [nidx_TblItemLog_UidCidItemSerial] ON [dbo].[TblItemLog]([UID], [CID], [ItemSerial]) ON [PRIMARY] GO ALTER TABLE [dbo].[UserInfo_1] ADD CHECK ([UID] % 4 = 0) GO ALTER TABLE [dbo].[UserInfo_2] ADD CHECK ([UID] % 4 = 1) GO ALTER TABLE [dbo].[UserInfo_3] ADD CHECK ([UID] % 4 = 2) GO ALTER TABLE [dbo].[UserInfo_4] ADD CHECK ([UID] % 4 = 3) GO ALTER TABLE [dbo].[TblGuildItem] ADD CONSTRAINT [FK_TblGuildInfo_guilditem] FOREIGN KEY ( [nGuildID] ) REFERENCES [dbo].[TblGuildInfo] ( [nGuildID] ) ON DELETE CASCADE GO ALTER TABLE [dbo].[TblGuildItem_EX] ADD CONSTRAINT [FK_TblGuildInfo_guilditem_ex] FOREIGN KEY ( [nGuildID] ) REFERENCES [dbo].[TblGuildInfo] ( [nGuildID] ) ON DELETE CASCADE GO ALTER TABLE [dbo].[TblGuildMember] ADD CONSTRAINT [FK_TblGuildInfo_TblGuildMember] FOREIGN KEY ( [nGuildID] ) REFERENCES [dbo].[TblGuildInfo] ( [nGuildID] ) ON DELETE CASCADE GO ALTER TABLE [dbo].[TblGuildMsg] ADD CONSTRAINT [FK_TblGuildInfo_TblGuildMsg] FOREIGN KEY ( [nGuildID] ) REFERENCES [dbo].[TblGuildInfo] ( [nGuildID] ) ON DELETE CASCADE GO ALTER TABLE [dbo].[TblGuildOtherList] ADD CONSTRAINT [FK_TblGuildInfo_TblGuildOtherList] FOREIGN KEY ( [nGuildID] ) REFERENCES [dbo].[TblGuildInfo] ( [nGuildID] ) ON DELETE CASCADE GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW UserInfoConvertView AS SELECT UID,Char1 AS CID FROM UserInfo Where Char1 > 0 UNION ALL SELECT UID,Char2 FROM UserInfo Where Char2 > 0 UNION ALL SELECT UID,Char3 FROM UserInfo Where Char3 > 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* 디폴트 = 0 */ CREATE VIEW dbo.CharInfo AS SELECT * FROM CharInfo_1 UNION ALL SELECT * FROM CharInfo_2 UNION ALL SELECT * FROM CharInfo_3 UNION ALL SELECT * FROM CharInfo_4 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* 캐릭터 위치 테이블 뷰 */ CREATE VIEW dbo.CharPos AS SELECT * FROM CharPos_1 UNION ALL SELECT * FROM CharPos_2 UNION ALL SELECT * FROM CharPos_3 UNION ALL SELECT * FROM CharPos_4 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* 캐릭터 추가 정 테이블 뷰 */ CREATE VIEW dbo.CharSkill AS SELECT * FROM CharSkill_1 UNION ALL SELECT * FROM CharSkill_2 UNION ALL SELECT * FROM CharSkill_3 UNION ALL SELECT * FROM CharSkill_4 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* 파티 정보 테이블 뷰 */ CREATE VIEW dbo.PartyInfo AS SELECT * FROM PartyInfo_1 UNION ALL SELECT * FROM PartyInfo_2 UNION ALL SELECT * FROM PartyInfo_3 UNION ALL SELECT * FROM PartyInfo_4 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.UserInfo AS SELECT * FROM UserInfo_1 UNION ALL SELECT * FROM UserInfo_2 UNION ALL SELECT * FROM UserInfo_3 UNION ALL SELECT * FROM UserInfo_4 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /**** 최근 한달 사이에 접속을 한번이라도 하고 (and) 캐릭 셋중 하나만이라도 30레벨 넘은 경우 캐릭 셋 및 창고의 금액 총합을 구한다. 이런 case있는 사람들의 금액 평균을 구한다. ***/ CREATE VIEW AvgGoldUser AS SELECT COUNT(UID) AS nCount,AVG(nSum) AS AVGold FROM ( SELECT B.UID,cast (Gold as bigint) + cast(nGold as bigint) as nSum FROM ( SELECT UID,(CASE WHEN Gold IS NULL THEN 0 ELSE Gold END) AS Gold FROM ItemStore1 WHERE UID IN (SELECT DISTINCT A.UID FROM dbo.UserInfoConvertView A JOIN ( SELECT UID FROM CharInfo WHERE UID IN ( SELECT CID FROM CharInfo_Time WHERE UpdateTime >= Convert(VARCHAR(10),GETDATE()-30,102) AND Level > 29 )) B ON A.CID = B.UID) ) A RIGHT OUTER JOIN (SELECT B.UID,SUM(CAST(Gold AS BIGINT)) AS nGold FROM CharInfo A JOIN ( SELECT A.UID,CID FROM UserInfoConvertView A JOIN ( SELECT DISTINCT A.UID FROM dbo.UserInfoConvertView A JOIN ( SELECT UID FROM CharInfo WHERE UID IN ( SELECT CID FROM CharInfo_Time WHERE UpdateTime >= Convert(VARCHAR(10),GETDATE()-30,102) AND Level > 29 )) B ON A.CID = B.UID) B ON A.UID = B.UID ) B ON A.UID = B.CID GROUP BY B.UID) B ON A.UID = B.UID ) A GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- DeleteAdmin.sql ---------------------------------------------------------------------------------------------------------------------- /* 운영자 삭제 프로시져 */ CREATE PROCEDURE dbo.DeleteAdmin @UID AS INT /* 유저 고유 아이디 */ AS SET NOCOUNT ON DELETE FROM AdminList WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC DeleteChar @user_uid INT,@user_pos INT,@CID INT AS SET NOCOUNT ON DECLARE @Div INT DECLARE @TableIndex INT DECLARE @ColumnIndex INT DECLARE @QUERY NVARCHAR(500) /* 유저 정보 테이블 업데이트 */ SET @Div = @user_uid % 4 IF @user_pos = 0 SET @ColumnIndex = 1 ELSE IF @user_pos = 1 SET @ColumnIndex = 2 ELSE IF @user_pos = 2 SET @ColumnIndex = 3 ELSE IF @user_pos = 3 SET @ColumnIndex = 4 ELSE IF @user_pos = 4 SET @ColumnIndex = 5 IF @Div = 0 SET @TableIndex = 1 ELSE IF @Div = 1 SET @TableIndex = 2 ELSE IF @Div = 2 SET @TableIndex = 3 ELSE IF @Div = 3 SET @TableIndex = 4 SET @QUERY = 'Begin Tran' + ' UPDATE UserInfo_' + CAST(@TableIndex AS CHAR(1)) + ' SET Char' + CAST(@ColumnIndex AS CHAR(1)) + ' = 0 WHERE UID = @User_Uid' + ' IF @@ROWCOUNT <> 1' + ' BEGIN' + ' ROLLBACK TRAN' + ' RETURN' + ' END' + ' INSERT INTO TblCharDelHistory (intCID,intUID,dateDeleteTime)' + ' VALUES (@CID,@User_Uid,GETDATE() + 90)' + ' IF @@ROWCOUNT <> 1' + ' BEGIN' + ' ROLLBACK TRAN' + ' RETURN' + ' END' + ' COMMIT TRAN' + ' RETURN' --select @QUERY Exec sp_executeSql @QUERY,N'@User_Uid int,@CID int', @user_uid,@CID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- DeleteCharEx.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 삭제 */ CREATE PROCEDURE dbo.DeleteCharEx @Char_uid AS INT /* 캐릭터 고유 아이디 */ AS DECLARE @UserID INT, @CharSlot INT SELECT @UserID = UID, @CharSlot = Char1 FROM UserInfo WHERE Char1 = @Char_uid IF @UserID IS NOT NULL BEGIN EXEC DeleteChar @UserID, 0, @Char_uid RETURN END SELECT @UserID = UID, @CharSlot = Char2 FROM UserInfo WHERE Char2 = @Char_uid IF @UserID IS NOT NULL BEGIN EXEC DeleteChar @UserID, 1, @Char_uid RETURN END SELECT @UserID = UID, @CharSlot = Char2 FROM UserInfo WHERE Char3 = @Char_uid IF @UserID IS NOT NULL BEGIN EXEC DeleteChar @UserID, 2, @Char_uid RETURN END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- DeleteCharInfo.sql ---------------------------------------------------------------------------------------------------------------------- /* 유저 정보 삭제 프로시져 */ CREATE PROCEDURE dbo.DeleteCharInfo @char_uid AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT /* 캐릭터 삭제 */ SET @Div = @char_uid % 4 IF @Div = 0 BEGIN --INSERT BEFORE CHARINFO DELETED INSERT INTO CharDelHistory.dbo.DelCharInfo_1 SELECT * FROM CharInfo_1 WHERE UID = @char_uid INSERT INTO CharDelHistory.dbo.DelCharSkill_1 SELECT * FROM CharSkill_1 WHERE UID = @char_uid DELETE FROM CharInfo_1 WHERE UID = @char_uid DELETE FROM CharPos_1 WHERE UID = @char_uid DELETE FROM CharSkill_1 WHERE UID = @char_uid END ELSE IF @Div = 1 BEGIN --INSERT BEFORE CHARINFO DELETED INSERT INTO CharDelHistory.dbo.DelCharInfo_2 SELECT * FROM CharInfo_2 WHERE UID = @char_uid INSERT INTO CharDelHistory.dbo.DelCharSkill_2 SELECT * FROM CharSkill_2 WHERE UID = @char_uid DELETE FROM CharInfo_2 WHERE UID = @char_uid DELETE FROM CharPos_2 WHERE UID = @char_uid DELETE FROM CharSkill_2 WHERE UID = @char_uid END ELSE IF @Div = 2 BEGIN --INSERT BEFORE CHARINFO DELETED INSERT INTO CharDelHistory.dbo.DelCharInfo_3 SELECT * FROM CharInfo_3 WHERE UID = @char_uid INSERT INTO CharDelHistory.dbo.DelCharSkill_3 SELECT * FROM CharSkill_3 WHERE UID = @char_uid DELETE FROM CharInfo_3 WHERE UID = @char_uid DELETE FROM CharPos_3 WHERE UID = @char_uid DELETE FROM CharSkill_3 WHERE UID = @char_uid END ELSE IF @Div = 3 BEGIN --INSERT BEFORE CHARINFO DELETED INSERT INTO CharDelHistory.dbo.DelCharInfo_4 SELECT * FROM CharInfo_4 WHERE UID = @char_uid INSERT INTO CharDelHistory.dbo.DelCharSkill_4 SELECT * FROM CharSkill_4 WHERE UID = @char_uid DELETE FROM CharInfo_4 WHERE UID = @char_uid DELETE FROM CharPos_4 WHERE UID = @char_uid DELETE FROM CharSkill_4 WHERE UID = @char_uid END DELETE FROM CharInfoEx WHERE UID = @char_uid --INSERT BEFORE CHARINFO DELETED INSERT INTO CharDelHistory.dbo.DelCharItem SELECT * FROM CharItem WHERE UID = @char_uid INSERT INTO CharDelHistory.dbo.DelCharItemEx SELECT * FROM CharItemEx WHERE UID = @char_uid DELETE FROM CharItem WHERE UID = @char_uid DELETE FROM CharItemEx WHERE UID = @char_uid DELETE FROM Friend WHERE UID = @char_uid DELETE FROM Quest WHERE UID = @char_uid -- 시간기록삭제 INSERT INTO CharDelHistory.dbo.DelCharInfo_Time VALUES (@char_uid,GETDATE(),GETDATE()) DELETE FROM CharInfo_Time WHERE CID = @char_uid GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- DeleteChar.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 삭제 프로시져 */ CREATE PROCEDURE dbo.DeleteChar_TEMP_BACKUP @user_uid AS INT, /* 유저 고유 아이디 */ @user_pos AS INT, /* 캐릭터 위치 (3개의 슬롯) */ @char_uid AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT DECLARE @CID INT /* 고유 아이디 설정 */ SELECT @CID = UID FROM CharInfo WHERE UID = @char_uid IF @CID IS NOT NULL BEGIN /* 캐릭터 삭제 */ EXEC DeleteCharInfo @CID /* 유저 정보 테이블 업데이트 */ EXEC UpdateCharUID @user_uid, @user_pos, 0 INSERT INTO CIDStore (UID) VALUES (@CID) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- DeleteParty.sql ---------------------------------------------------------------------------------------------------------------------- /* 파티 제거 프로시져 */ CREATE PROCEDURE dbo.DeleteParty @Party_uid AS INT /* 파티 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT /* 파티 추가 */ SET @Div = @Party_uid % 4 IF @Div = 0 BEGIN DELETE FROM PartyInfo_1 WHERE UID = @Party_uid END ELSE IF @Div = 1 BEGIN DELETE FROM PartyInfo_2 WHERE UID = @Party_uid END ELSE IF @Div = 2 BEGIN DELETE FROM PartyInfo_3 WHERE UID = @Party_uid END ELSE IF @Div = 3 BEGIN DELETE FROM PartyInfo_4 WHERE UID = @Party_uid END INSERT INTO PIDStore (UID) VALUES (@Party_uid) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- DeleteUser.sql ---------------------------------------------------------------------------------------------------------------------- /* 유저 삭제 프로시져 ( DeleteCharInfo 정의 되어 있어야 함. ) */ CREATE PROCEDURE dbo.DeleteUser @UID INT /* 사용자 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT DECLARE @Char1 AS INT, @Char2 AS INT, @Char3 AS INT,@Char4 INT,@Char5 INT BEGIN TRAN /* 캐릭터 삭제 */ SELECT @Char1 = Char1, @Char2 = Char2, @Char3 = Char3,@Char4 =Char4,@Char5 = Char5 FROM UserInfo WHERE UID = @UID IF(@Char1 <> 0) BEGIN EXEC DeleteCharInfo @Char1 END IF(@Char2 <> 0) BEGIN EXEC DeleteCharInfo @Char2 END IF(@Char3 <> 0) BEGIN EXEC DeleteCharInfo @Char3 END IF(@Char4 <> 0) BEGIN EXEC DeleteCharInfo @Char4 END IF(@Char5 <> 0) BEGIN EXEC DeleteCharInfo @Char5 END /* 유저 삭제 */ SET @Div = @UID % 4 IF @Div = 0 DELETE FROM UserInfo_1 WHERE UID = @UID ELSE IF @Div = 1 DELETE FROM UserInfo_2 WHERE UID = @UID ELSE IF @Div = 2 DELETE FROM UserInfo_3 WHERE UID = @UID ELSE IF @Div = 3 DELETE FROM UserInfo_4 WHERE UID = @UID DELETE FROM ItemStore1 WHERE UID = @UID DELETE FROM ItemStore2 WHERE UID = @UID COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- FoundSlot.sql ---------------------------------------------------------------------------------------------------------------------- /* 슬롯 찾기 */ CREATE PROCEDURE dbo.FoundSlot @Char_uid AS INT /* 캐릭터 고유 아이디 */ AS DECLARE @UserID INT SELECT @UserID = UID FROM UserInfo WHERE Char1 = @Char_uid IF @UserID IS NOT NULL BEGIN SELECT @UserID, 0 RETURN END SELECT @UserID = UID FROM UserInfo WHERE Char2 = @Char_uid IF @UserID IS NOT NULL BEGIN SELECT @UserID, 1 RETURN END SELECT @UserID = UID FROM UserInfo WHERE Char3 = @Char_uid IF @UserID IS NOT NULL BEGIN SELECT @UserID, 2 RETURN END SELECT @UserID = UID FROM UserInfo WHERE Char4 = @Char_uid IF @UserID IS NOT NULL BEGIN SELECT @UserID, 3 RETURN END SELECT @UserID = UID FROM UserInfo WHERE Char5 = @Char_uid IF @UserID IS NOT NULL BEGIN SELECT @UserID, 4 RETURN END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- ADD Equip column CREATE PROCEDURE dbo.GetCharInfo @UID AS INT AS SET NOCOUNT ON SELECT UID, Name, Sex, Hair, Face, Nationality, Class, Fame, Mileage, Guild, Party, Level, Gold, IP, STR, DEX, CON, Intell, WIS, HP, MP, Exp , Chance,Equip FROM CharInfo WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- GetCharInfoEx.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 정보 얻기 프로시져 */ CREATE PROCEDURE dbo.GetCharInfoEx @UID AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON SELECT Total, ServerID FROM CharInfoEx WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- GetCharItem.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 아이템 정보 얻기 프로시져 */ CREATE PROCEDURE dbo.GetCharItem @UID AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON SELECT Quick, Equip, Inventory FROM CharItem WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- GetCharPos.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 위치 정보 얻기 프로시져 */ CREATE PROCEDURE dbo.GetCharPos @UID AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON SELECT LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ FROM CharPos WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- GetCharSkill.sql ---------------------------------------------------------------------------------------------------------------------- /* 스킬 정보 보기 프로시져 */ CREATE PROCEDURE dbo.GetCharSkill @UID AS INT /* 캐릭터 아이디 */ AS SET NOCOUNT ON SELECT Skill FROM CharSkill WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* 캐릭터 보기 얻기 프로시져 변경 */ CREATE PROCEDURE dbo.GetCharView @UID AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON SELECT UID, Name, Sex, Hair, Face, Nationality, Class, Fame, Mileage, Guild, Party, Level, Equip FROM CharInfo WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.GetItemStore @UID AS INT /* 유저 고유 아이디 */ AS SET NOCOUNT ON SELECT Password, Frag, Gold FROM ItemStore1 WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- GetPartyInfo.sql ---------------------------------------------------------------------------------------------------------------------- /* 파티 정보 보기 프로시져 */ CREATE PROCEDURE dbo.GetPartyInfo @Party_uid AS INT /* 파티 아이디 */ AS SET NOCOUNT ON SELECT Party FROM PartyInfo WHERE UID = @Party_uid GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE Proc GetUIDCIDFromName @Name varchar(16) as Declare @CID int set @CID = 0 Declare @UID int set @UID = 0 Select @CID = UID from CharInfo where Name = @Name if @CID = 0 Begin Select 0, 0 Return End Select @UID = UID from UserInfo where Char1 = @CID or Char2 = @CID or Char3 = @CID or Char4 = @CID or Char5 = @CID if @UID = 0 Begin Select 0, @CID Return End select @UID, @CID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- GetUserInfo.sql ---------------------------------------------------------------------------------------------------------------------- /* 유저 정보 얻기 프로시져 */ CREATE PROCEDURE dbo.GetUserInfo @UID AS INT /* 유저 고유 아이디 */ AS SET NOCOUNT ON SELECT Char1, Char2, Char3 ,Char4 , Char5 FROM UserInfo WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- InsertAdmin.sql ---------------------------------------------------------------------------------------------------------------------- /* 어드민 추가 프로시져 */ CREATE PROCEDURE dbo.InsertAdmin @UID INT /* 사용자 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT INSERT INTO AdminList (UID) VALUES (@UID) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.InsertChar @user_uid AS INT, /* 유저 고유 아이디 */ @user_pos AS INT, /* 캐릭터 위치 (3개의 슬롯) */ @user_name AS VARCHAR(16), /* 캐릭터 이름 */ @user_Sex AS TINYINT, /* 캐릭터 성 */ @user_Hair AS TINYINT, /* 캐릭터 머리 모양 */ @user_Face AS TINYINT, /* 캐릭터 얼굴 모양 */ @user_Nation AS TINYINT, /* 캐릭터 국가 */ @user_Class AS SMALLINT, /* 캐릭터 클래스 */ @C_Gold AS INT, /* 캐릭터 소지금 */ @C_STR AS SMALLINT, /* 캐릭터 STR */ @C_DEX AS SMALLINT, /* 캐릭터 DEX */ @C_CON AS SMALLINT, /* 캐릭터 CON */ @C_INT AS SMALLINT, /* 캐릭터 INT */ @C_WIS AS SMALLINT, /* 캐릭터 WIS */ @PointX AS REAL, /* 캐릭터 Point X 좌표 */ @PointY AS REAL, /* 캐릭터 Point Y 좌표 */ @PointZ AS REAL, /* 캐릭터 Point Z 좌표 */ @C_Equip AS BINARY(30) /* 장비 (2 * 15 = 30) */ AS SET NOCOUNT ON DECLARE @UID INT, @Div INT DECLARE @C_HP INT, @C_MP INT /* 캐릭터 이름 확인 */ SELECT @UID = UID FROM CharInfo WHERE Name = @user_name IF @UID <> 0 BEGIN SELECT -1 /* 동일 이름 존재 */ RETURN END /* 슬롯 확인 */ DECLARE @CharSlot INT IF @user_pos = 0 BEGIN SELECT @CharSlot = Char1 FROM UserInfo WHERE UID = @user_uid END ELSE IF @user_pos = 1 BEGIN SELECT @CharSlot = Char2 FROM UserInfo WHERE UID = @user_uid END ELSE IF @user_pos = 2 BEGIN SELECT @CharSlot = Char3 FROM UserInfo WHERE UID = @user_uid END ELSE IF @user_pos = 3 BEGIN SELECT @CharSlot = Char4 FROM UserInfo WHERE UID = @user_uid END ELSE IF @user_pos = 4 BEGIN SELECT @CharSlot = Char5 FROM UserInfo WHERE UID = @user_uid END IF @CharSlot <> 0 BEGIN SELECT -2 /* 슬롯이 차있음 */ RETURN END /* 고유 아이디 설정 */ SELECT @UID = MAX(UID) + 1 FROM CharInfo IF @UID IS NULL BEGIN SET @UID = 1 END SELECT @UID SET @C_HP = 100 + @C_CON * 8 + 2 IF @C_INT > @C_WIS BEGIN SET @C_MP = 100 + @C_INT * 8 + 2 END ELSE BEGIN SET @C_MP = 100 + @C_WIS * 8 + 2 END /* 캐릭터 추가 */ SET @Div = @UID % 4 IF @Div = 0 BEGIN INSERT INTO CharInfo_1 (UID, Name, Sex, Hair, Face, Nationality, Class, Equip, Gold, STR, DEX, CON, Intell, WIS, HP, MP) VALUES (@UID, @user_name, @user_Sex, @user_Hair, @user_Face, @user_Nation, @user_Class, @C_Equip, @C_Gold, @C_STR, @C_DEX, @C_CON, @C_INT, @C_WIS, @C_HP, @C_MP) INSERT INTO CharPos_1 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@UID, @PointX, @PointY, @PointZ, @PointX, @PointY, @PointZ) END ELSE IF @Div = 1 BEGIN INSERT INTO CharInfo_2 (UID, Name, Sex, Hair, Face, Nationality, Class, Equip, Gold, STR, DEX, CON, Intell, WIS, HP, MP) VALUES (@UID, @user_name, @user_Sex, @user_Hair, @user_Face, @user_Nation, @user_Class, @C_Equip, @C_Gold, @C_STR, @C_DEX, @C_CON, @C_INT, @C_WIS, @C_HP, @C_MP) INSERT INTO CharPos_2 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@UID, @PointX, @PointY, @PointZ, @PointX, @PointY, @PointZ) END ELSE IF @Div = 2 BEGIN INSERT INTO CharInfo_3 (UID, Name, Sex, Hair, Face, Nationality, Class, Equip, Gold, STR, DEX, CON, Intell, WIS, HP, MP) VALUES (@UID, @user_name, @user_Sex, @user_Hair, @user_Face, @user_Nation, @user_Class, @C_Equip, @C_Gold, @C_STR, @C_DEX, @C_CON, @C_INT, @C_WIS, @C_HP, @C_MP) INSERT INTO CharPos_3 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@UID, @PointX, @PointY, @PointZ, @PointX, @PointY, @PointZ) END ELSE IF @Div = 3 BEGIN INSERT INTO CharInfo_4 (UID, Name, Sex, Hair, Face, Nationality, Class, Equip, Gold, STR, DEX, CON, Intell, WIS, HP, MP) VALUES (@UID, @user_name, @user_Sex, @user_Hair, @user_Face, @user_Nation, @user_Class, @C_Equip, @C_Gold, @C_STR, @C_DEX, @C_CON, @C_INT, @C_WIS, @C_HP, @C_MP) INSERT INTO CharPos_4 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@UID, @PointX, @PointY, @PointZ, @PointX, @PointY, @PointZ) END INSERT INTO CharInfoEx (UID) VALUES (@UID) INSERT INTO CharItem (UID) VALUES (@UID) INSERT INTO CharItemEx (UID) VALUES (@UID) INSERT INTO Friend (UID) VALUES (@UID) INSERT INTO Quest (UID) VALUES (@UID) -- 캐릭터 생성시간입력 INSERT INTO Charinfo_Time(CID,CreateTime,UpdateTime) VALUES (@UID,GETDATE(),GETDATE()) /* 유저 정보 테이블 업데이트 */ EXEC UpdateCharUID @user_uid, @user_pos, @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- InsertCharSkill.sql ---------------------------------------------------------------------------------------------------------------------- /* 스킬 추가 프로시져 */ CREATE PROCEDURE dbo.InsertCharSkill @UID AS INT, /* 사용자 아이디 */ @Skill AS BINARY(84) /* 스킬 슬롯 */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN /* 유저 추가 */ SET @Div = @UID % 4 IF @Div = 0 INSERT INTO CharSkill_1 (UID, Skill) VALUES (@UID, @Skill) ELSE IF @Div = 1 INSERT INTO CharSkill_2 (UID, Skill) VALUES (@UID, @Skill) ELSE IF @Div = 2 INSERT INTO CharSkill_3 (UID, Skill) VALUES (@UID, @Skill) ELSE IF @Div = 3 INSERT INTO CharSkill_4 (UID, Skill) VALUES (@UID, @Skill) COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- InsertParty.sql ---------------------------------------------------------------------------------------------------------------------- /* 파티 추가 프로시져 */ CREATE PROCEDURE dbo.InsertParty @Party AS BINARY(202) /* 친구 리스트 */ AS SET NOCOUNT ON DECLARE @UID INT DECLARE @Div INT /* 고유 아이디 설정 */ SELECT @UID = MIN(UID) FROM PIDStore IF @UID IS NULL BEGIN SELECT @UID = MAX(UID) + 1 FROM PartyInfo IF @UID IS NULL BEGIN SET @UID = 1 END SELECT @UID END ELSE BEGIN SELECT @UID DELETE FROM PIDStore WHERE UID = @UID END /* 파티 추가 */ SET @Div = @UID % 4 IF @Div = 0 BEGIN INSERT INTO PartyInfo_1 (UID, Party) VALUES (@UID, @Party) END ELSE IF @Div = 1 BEGIN INSERT INTO PartyInfo_2 (UID, Party) VALUES (@UID, @Party) END ELSE IF @Div = 2 BEGIN INSERT INTO PartyInfo_3 (UID, Party) VALUES (@UID, @Party) END ELSE IF @Div = 3 BEGIN INSERT INTO PartyInfo_4 (UID, Party) VALUES (@UID, @Party) END /* 고유 아이디 돌려줌 */ SELECT @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- InsertUser.sql ---------------------------------------------------------------------------------------------------------------------- /* 유저 추가 프로시져 */ CREATE PROCEDURE dbo.InsertUser @UID INT /* 사용자 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN /* 유저 추가 */ SET @Div = @UID % 4 IF @Div = 0 INSERT INTO UserInfo_1 (UID) VALUES (@UID) ELSE IF @Div = 1 INSERT INTO UserInfo_2 (UID) VALUES (@UID) ELSE IF @Div = 2 INSERT INTO UserInfo_3 (UID) VALUES (@UID) ELSE IF @Div = 3 INSERT INTO UserInfo_4 (UID) VALUES (@UID) INSERT ItemStore1 (UID) VALUES (@UID) INSERT ItemStore2 (UID) VALUES (@UID) COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- 복원프로시져 CREATE PROC RestoreCharInfo @user_uid INT,@CID INT,@user_name VARCHAR(20) AS DECLARE @UID INT, @Div INT DECLARE @C_HP INT, @C_MP INT /* 캐릭터 이름 확인 */ SELECT @UID = UID FROM CharInfo WHERE Name = @user_name IF @UID <> 0 BEGIN SELECT -1 /* 동일 이름 존재 */ RETURN END /* 슬롯 확인 */ DECLARE @user_pos INT DECLARE @CharSlot1 INT SET @CharSlot1 = 0 DECLARE @CharSlot2 INT SET @CharSlot2 = 0 DECLARE @CharSlot3 INT SET @CharSlot3 = 0 DECLARE @CharSlot4 INT SET @CharSlot4 = 0 DECLARE @CharSlot5 INT SET @CharSlot5 = 0 SELECT @CharSlot1 = Char1,@CharSlot2=Char2,@CharSlot3=Char3,@CharSlot4=Char4,@CharSlot5=Char5 FROM UserInfo WHERE UID = @user_uid IF @CharSlot1 = 0 SET @user_pos = 0 ELSE IF @CharSlot2 = 0 SET @user_pos = 1 ELSE IF @CharSlot3 = 0 SET @user_pos = 2 ELSE IF @CharSlot4 = 0 SET @user_pos = 3 ELSE IF @CharSlot5 = 0 SET @user_pos = 4 ELSE BEGIN SELECT -2 /* 슬롯이 차있음 */ RETURN END /* 캐릭터 추가 */ SET @Div = @CID % 4 IF @Div = 0 BEGIN INSERT INTO CharInfo_1 SELECT * FROM CharDelHistory.dbo.DelCharInfo_1 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharInfo_1 WHERE UID = @CID INSERT INTO CharSkill_1 (UID, Skill) SELECT * FROM CharDelHistory.dbo.DelCharSkill_1 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharSkill_1 WHERE UID = @CID INSERT INTO CharPos_1 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@CID, 0, 0, 0, 0, 0, 0) END ELSE IF @Div = 1 BEGIN INSERT INTO CharInfo_2 SELECT * FROM CharDelHistory.dbo.DelCharInfo_2 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharInfo_2 WHERE UID = @CID INSERT INTO CharSkill_2 (UID, Skill) SELECT * FROM CharDelHistory.dbo.DelCharSkill_2 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharSkill_2 WHERE UID = @CID INSERT INTO CharPos_2 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@CID, 0, 0, 0, 0, 0, 0) END ELSE IF @Div = 2 BEGIN INSERT INTO CharInfo_3 SELECT * FROM CharDelHistory.dbo.DelCharInfo_3 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharInfo_3 WHERE UID = @CID INSERT INTO CharSkill_3 (UID, Skill) SELECT * FROM CharDelHistory.dbo.DelCharSkill_3 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharSkill_3 WHERE UID = @CID INSERT INTO CharPos_3 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@CID, 0, 0, 0, 0, 0, 0) END ELSE IF @Div = 3 BEGIN INSERT INTO CharInfo_4 SELECT * FROM CharDelHistory.dbo.DelCharInfo_4 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharInfo_4 WHERE UID = @CID INSERT INTO CharSkill_4 (UID, Skill) SELECT * FROM CharDelHistory.dbo.DelCharSkill_4 WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharSkill_4 WHERE UID = @CID INSERT INTO CharPos_4 (UID, LastPointX, LastPointY, LastPointZ, SavePointX, SavePointY, SavePointZ) VALUES (@CID, 0, 0, 0, 0, 0, 0) END INSERT INTO CharInfoEx (UID) VALUES (@CID) INSERT INTO CharItem SELECT * FROM CharDelHistory.dbo.DelCharItem WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharItem WHERE UID = @CID INSERT INTO CharItemEx SELECT * FROM CharDelHistory.dbo.DelCharItemEx WHERE UID = @CID DELETE CharDelHistory.dbo.DelCharItemEx WHERE UID = @CID INSERT INTO Friend (UID) VALUES (@CID) INSERT INTO Quest (UID) VALUES (@CID) -- 캐릭터 생성시간입력 DELETE CharDelHistory.dbo.DelCharInfo_Time WHERE CID = @CID INSERT INTO CharInfo_Time(CID,CreateTime,UpdateTime) VALUES (@CID,GETDATE(),GETDATE()) /* 유저 정보 테이블 업데이트 */ EXEC UpdateCharUID @user_uid, @user_pos, @CID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USPCampCreate @nGuildID INT,@tnZone TINYINT,@tnChannel TINYINT,@fPosX FLOAT,@fPosY FLOAT,@fPosZ FLOAT AS SET NOCOUNT ON DECLARE @nCampID INT SET @nCampID = 0 BEGIN TRAN INSERT INTO TblCampInfo (nGuildID,nHP,tnZone,tnChannel, tnState,tnUpgradeStep,RemainTime,LastUseTime,bRight,fPosX,fPosY,fPosZ) VALUES (@nGuildID,10,@tnZone,@tnChannel,4,0,GETDATE(),GETDATE(),0x01030101010101010101,@fPosX,@fPosY,@fPosZ) IF @@ERROR <> 0 BEGIN SELECT @nCampID ROLLBACK TRAN RETURN END SET @nCampID = CAST(SCOPE_IDENTITY() AS INT) COMMIT TRAN SELECT @nCampID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USPCampDelete @CampID INT AS SET NOCOUNT ON DECLARE @RETURN INT DELETE TblCampInfo WHERE nCampID = @CampID SET @RETURN = @@ROWCOUNT SELECT @RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USPCastleUpdate @nCastleID INT,@nGuildID INT,@snTax SMALLINT,@nTaxMoney INT, @tnTaxIncomeRemainDay TINYINT,@tnInvincibleDay TINYINT AS SET NOCOUNT ON DECLARE @RETURN INT UPDATE TblCastleInfo SET nGuildID = @nGuildID,snTax = @snTax,nTaxMoney=@nTaxMoney, tnTaxIncomeRemainDay=@tnTaxIncomeRemainDay,tnInvincibleDay = @tnInvincibleDay WHERE nCastleID = @nCastleID SET @RETURN = @@ROWCOUNT SELECT @RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- 길드 직위변경 CREATE PROC USPGuildChangePos @GuildID INT,@CID INT,@BeforePos INT,@AfterPos INT AS SET NOCOUNT ON DECLARE @RETURN INT SET @RETURN = 0 IF (@BeforePos < @AfterPos) -- 강등... (1길드마스터 --> 7 일반회원) BEGIN BEGIN TRAN UPDATE TblGuildMember SET nPositionInGuild = @AfterPos WHERE nGuildID = @GuildID AND nCID = @CID AND nPositionInGuild = @BeforePos IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END UPDATE TblGuildMember SET nPositionInGuild = nPositionInGuild - 1 WHERE nGuildID = @GuildID AND nCID <> @CID AND nPositionInGuild BETWEEN @BeforePos AND @AfterPos IF @@ERROR <> 0 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END COMMIT TRAN END ELSE IF (@BeforePos > @AfterPos) -- 상승... (8 --> 1 길드마스터) BEGIN BEGIN TRAN UPDATE TblGuildMember SET nPositionInGuild = @AfterPos WHERE nGuildID = @GuildID AND nCID = @CID AND nPositionInGuild = @BeforePos IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END UPDATE TblGuildMember SET nPositionInGuild = nPositionInGuild + 1 WHERE nGuildID = @GuildID AND nCID <> @CID AND nPositionInGuild BETWEEN @AfterPos AND @BeforePos IF @@ERROR <> 0 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END COMMIT TRAN END SELECT @RETURN RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- 길드 생성 하기 CREATE PROC USPGuildCreate @GuildName VARCHAR(20),@tnNationType TINYINT,@GuildLevel Tinyint,@GuildFame INT,@bRight BINARY(50) AS SET NOCOUNT ON -- 기본길드정보에 값을 입력하기. DECLARE @RETURN INT DECLARE @GuildID INT SET @RETURN = 0 SET @GuildID = 0 IF EXISTS (SELECT strGuildName FROM dbo.TblGuildInfo WHERE strGuildName = @GuildName) BEGIN SET @RETURN = 2 SELECT @RETURN,@GuildID-- 중복된 길드명입니다. RETURN END ELSE BEGIN BEGIN TRAN INSERT INTO TblGuildInfo (strGuildName,tnGuildLevel,nGuildFame,bRIGHT,tnNationType) VALUES (@GuildName,@GuildLevel,@GuildFame,@bRight,@tnNationType) IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN,@GuildID RETURN END -- 이미 입력된 길드의 아이디를 얻어온다. SELECT @GuildID = nGuildID FROM TblGuildInfo WHERE strGuildName = @GuildName INSERT INTO TblGuildItem VALUES (@GuildID,NULL) IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 3 SET @GuildID = 0 SELECT @RETURN,@GuildID RETURN END INSERT INTO TblGuildItem_EX VALUES (@GuildID,NULL) IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 4 SET @GuildID = 0 SELECT @RETURN,@GuildID RETURN END COMMIT TRAN SELECT @RETURN,@GuildID -- SUCCESS END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USPGuildDelete @GuildID INT AS SET NOCOUNT ON INSERT INTO TblGuildRestoreData (nGuildID,strGuildName,nGuildGold,bGuildItem,tDeleteTime,strGuildMName) SELECT TOP 1 a.nGuildID,a.strGuildName,a.nGuildGold,b.bGuildItem,Getdate(),nCID FROM TblGuildInfo a JOIN TblGuildItem b ON a.nGuildID = b.nGuildID Left OUTER JOIN TblGuildMember c ON a.nGuildID = c.nGuildID WHERE a.nGuildID = @GuildID ORDER BY c.nPositionInGuild ASC INSERT INTO TblGuildRestoreData_EX (nGuildID,bGuildItem_EX) SELECT nGuildID,bGuildItem FROM TblGuildItem_EX WHERE nGuildID = @GuildID DECLARE @ROWID INT DECLARE @CID INT DECLARE @TEMPTABLE TABLE ( ROWID INT IDENTITY(1,1) , CID INT ) INSERT INTO @TEMPTABLE (CID) SELECT nCID FROM TblGuildMember WHERE nGuildID = @GuildID SET @ROWID = @@ROWCOUNT WHILE @ROWID > 0 BEGIN SELECT @CID=CID FROM @TEMPTABLE WHERE ROWID = @ROWID IF ((@CID % 4) = 0) BEGIN UPDATE CHARINFO_1 SET Guild = 0 WHERE UID = @CID END ELSE IF ((@CID % 4) = 1) BEGIN UPDATE CHARINFO_2 SET Guild = 0 WHERE UID = @CID END ELSE IF ((@CID % 4) = 2) BEGIN UPDATE CHARINFO_3 SET Guild = 0 WHERE UID = @CID END ELSE IF ((@CID % 4) = 3) BEGIN UPDATE CHARINFO_4 SET Guild = 0 WHERE UID = @CID END SET @ROWID = @ROWID - 1 END DELETE TblGuildInfo WHERE nGuildID = @GuildID IF @@ERROR <> 0 SELECT 1 -- ERROR ELSE SELECT 0 -- SUCCESS GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USPGuildGiveMemberGold @GuildID INT,@Gold INT AS SET NOCOUNT ON DECLARE @Position INT UPDATE CharInfo_1 SET Gold = Gold + @Gold FROM TblGuildMember a,Charinfo_1 b WHERE a.nCID = b.UID AND a.nGuildID = @GuildID AND a.nPosition < 4 UPDATE CharInfo_2 SET Gold = Gold + @Gold FROM TblGuildMember a,Charinfo_2 b WHERE a.nCID = b.UID AND a.nGuildID = @GuildID AND a.nPosition < 4 UPDATE CharInfo_3 SET Gold = Gold + @Gold FROM TblGuildMember a,Charinfo_3 b WHERE a.nCID = b.UID AND a.nGuildID = @GuildID AND a.nPosition < 4 UPDATE CharInfo_4 SET Gold = Gold + @Gold FROM TblGuildMember a,Charinfo_4 b WHERE a.nCID = b.UID AND a.nGuildID = @GuildID AND a.nPosition < 4 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- GUILD ITEM 입력 CREATE PROC USPGuildItemEXInsert @GuildID INT,@Item BINARY(7300) AS SET NOCOUNT ON DECLARE @RETURN INT SET @RETURN = 0 IF EXISTS (SELECT nGuildID FROM TblGuildItem_EX WHERE nGuildID = @GuildID) BEGIN UPDATE TblGuildItem_EX SET bGuildItem = @Item WHERE nGuildID = @GuildID IF @@ROWCOUNT <> 1 BEGIN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE BEGIN INSERT TblGuildItem_EX VALUES (@GuildID,@Item) IF @@ROWCOUNT <> 1 BEGIN SET @RETURN = 2 SELECT @RETURN RETURN END END SELECT @RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- GUILD ITEM 입력 CREATE PROC USPGuildItemInsert @GuildID INT,@Item BINARY(7300) AS SET NOCOUNT ON DECLARE @RETURN INT SET @RETURN = 0 IF EXISTS (SELECT nGuildID FROM TblGuildItem WHERE nGuildID = @GuildID) BEGIN UPDATE TblGuildItem SET bGuildItem = @Item WHERE nGuildID = @GuildID IF @@ROWCOUNT <> 1 BEGIN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE BEGIN INSERT TblGuildItem VALUES (@GuildID,@Item) IF @@ROWCOUNT <> 1 BEGIN SET @RETURN = 2 SELECT @RETURN RETURN END END SELECT @RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- GUILD MEMBER 입력 CREATE PROC USPGuildMemberInsert @GuildID INT,@CID INT,@Position INT,@PositionInGuild INT AS SET NOCOUNT ON DECLARE @RETURN INT SET @RETURN = 0 BEGIN TRAN INSERT INTO TblGuildMember (nGuildID,nCID,nPosition,nPositionInGuild) VALUES (@GuildID,@CID,@Position,@PositionInGuild) IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END IF ((@CID % 4) = 0) BEGIN UPDATE CHARINFO_1 SET Guild = @GuildID WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE IF ((@CID % 4) = 1) BEGIN UPDATE CHARINFO_2 SET Guild = @GuildID WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE IF ((@CID % 4) = 2) BEGIN UPDATE CHARINFO_3 SET Guild = @GuildID WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE IF ((@CID % 4) = 3) BEGIN UPDATE CHARINFO_4 SET Guild = @GuildID WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END COMMIT TRAN SELECT @RETURN RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO --길드탈퇴.... CREATE PROC USPGuildMemberOut @GuildID INT,@CID INT,@Pos INT AS SET NOCOUNT ON DECLARE @RETURN INT SET @RETURN = 0 -- member 삭제... BEGIN TRAN DELETE TblGuildMember WHERE nGuildID = @GuildID AND nCID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END -- 서열순위 UPDATE TblGuildMember SET nPositionInGuild = nPositionInGuild - 1 WHERE nGuildID = @GuildID AND nPositionInGuild > @Pos IF @@ERROR <> 0 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END IF ((@CID % 4) = 0) BEGIN UPDATE CHARINFO_1 SET Guild = 0 WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE IF ((@CID % 4) = 1) BEGIN UPDATE CHARINFO_2 SET Guild = 0 WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE IF ((@CID % 4) = 2) BEGIN UPDATE CHARINFO_3 SET Guild = 0 WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END ELSE IF ((@CID % 4) = 3) BEGIN UPDATE CHARINFO_4 SET Guild = 0 WHERE UID = @CID IF @@ROWCOUNT <> 1 BEGIN ROLLBACK TRAN SET @RETURN = 1 SELECT @RETURN RETURN END END COMMIT TRAN SELECT @RETURN RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USP_GUILDORDERBY AS SET NOCOUNT ON CREATE TABLE #ROWTABLE ( ROWID INT IDENTITY(1,1) , nGuildID INT ) DECLARE @ROWID INT DECLARE @GUILDID INT INSERT INTO #ROWTABLE (nGuildID) SELECT DISTINCT nGuildID FROM tblguildmember_temp SET @ROWID = @@ROWCOUNT WHILE @ROWID > 0 BEGIN SELECT @GUILDID = nGuildID FROM #ROWTABLE WHERE ROWID=@ROWID CREATE TABLE [#tblguildmember_temp] ( [nGuildID] [int] NOT NULL , [nCID] [int] NOT NULL , [nPosition] [int] NOT NULL , [nPositionInGuild] [int] IDENTITY(0,1) , [tLeaveGuildTime] [smalldatetime] NULL ) INSERT INTO #tblguildmember_temp (nGuildID,nCID,nPosition,tLeaveGuildTime) SELECT nGuildID,nCID,nPosition,tLeaveGuildTime FROM tblguildmember_temp where nGuildID = @GUILDID ORDER BY nPosition INSERT INTO tblguildmember_temp2 SELECT * FROM #tblguildmember_temp DROP TABLE #tblguildmember_temp SET @ROWID = @ROWID - 1 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USP_InsertItemLog ( @UID INT, @CID INT, @ItemSerial BIGINT, @CharName VARCHAR(20), @Qty INT ) AS SET NOCOUNT ON DECLARE @bQty INT SET @bQty = 0 SELECT @bQty = Qty FROM TblItemLog WHERE UID = @UID AND CID = @CID AND ItemSerial = @ItemSerial AND CheckField = 0 IF (@bQty > 0 ) BEGIN IF (@bQty >= @Qty) BEGIN UPDATE TblItemLog SET UpdateTime = GETDATE() WHERE UID = @UID AND CID = @CID AND ItemSerial = @ItemSerial AND CheckField = 0 END ELSE BEGIN UPDATE TblItemLog SET UpdateTime = GETDATE(),Qty = @Qty WHERE UID = @UID AND CID = @CID AND ItemSerial = @ItemSerial AND CheckField = 0 END END ELSE BEGIN INSERT INTO TblItemLog (UID,CID,ItemSerial,CharName,Qty) Values (@UID, @CID, @ItemSerial, @CharName, @Qty) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC USP_UpdateCheckField_ItemLog ( @UID INT, @CID INT, @ItemSerial BIGINT ) AS SET NOCOUNT ON UPDATE TblItemLog SET CheckField = 1,UpdateTime = Getdate() WHERE UID = @UID AND CID = @CID AND ItemSerial = @ItemSerial AND CheckField = 0 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharEquip.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 정보 변경 프로시져 */ CREATE PROCEDURE dbo.UpdateCharEquip @char_uid AS INT, /* 캐릭터 고유 아이디 */ @C_Equip AS BINARY(30) /* 장비 (2 * 15 = 30) */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN SET @Div = @char_uid % 4 IF @Div = 0 UPDATE CharInfo_1 SET Equip = @C_Equip WHERE UID = @char_uid ELSE IF @Div = 1 UPDATE CharInfo_2 SET Equip = @C_Equip WHERE UID = @char_uid ELSE IF @Div = 2 UPDATE CharInfo_3 SET Equip = @C_Equip WHERE UID = @char_uid ELSE UPDATE CharInfo_4 SET Equip = @C_Equip WHERE UID = @char_uid COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharInfo.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 정보 변경 프로시져 */ CREATE PROCEDURE dbo.UpdateCharInfo @char_uid AS INT, /* 캐릭터 고유 아이디 */ @char_Class AS SMALLINT, /* 캐릭터 클래스 */ @char_Fame AS INT, /* 캐릭터 명성 */ @char_Mileage AS INT, /* 캐릭터 마일리지 */ @char_Level AS TINYINT, /* 캐릭터 레벨 */ @char_Gold AS INT, /* 돈 */ @char_IP AS SMALLINT, /* 캐릭터 증가 포인트 */ @char_STR AS SMALLINT, /* 캐릭터 STR */ @char_DEX AS SMALLINT, /* 캐릭터 DEX */ @char_CON AS SMALLINT, /* 캐릭터 CON */ @char_INT AS SMALLINT, /* 캐릭터 INT */ @char_WIS AS SMALLINT, /* 캐릭터 WIS */ @char_HP AS SMALLINT, /* 캐릭터 HP */ @char_MP AS SMALLINT, /* 캐릭터 MP */ @char_Exp AS BIGINT, /* 캐릭터 경험점 */ @char_Chance AS TINYINT, /* 캐릭터 챈스 */ @char_Equip AS BINARY(30) /* 장비 (2 * 15 = 30) */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN SET @Div = @char_uid % 4 IF @Div = 0 UPDATE CharInfo_1 SET Class = @char_Class, Fame = @char_Fame, Mileage = @char_Mileage, Level = @char_Level, Equip = @char_Equip, Gold = @char_Gold, IP = @char_IP, STR = @char_STR, DEX = @char_DEX, CON = @char_CON, Intell = @char_INT, WIS = @char_WIS, HP = @char_HP, MP = @char_MP, Exp = @char_Exp, Chance = @char_Chance WHERE UID = @char_uid ELSE IF @Div = 1 UPDATE CharInfo_2 SET Class = @char_Class, Fame = @char_Fame, Mileage = @char_Mileage, Level = @char_Level, Equip = @char_Equip, Gold = @char_Gold, IP = @char_IP, STR = @char_STR, DEX = @char_DEX, CON = @char_CON, Intell = @char_INT, WIS = @char_WIS, HP = @char_HP, MP = @char_MP, Exp = @char_Exp, Chance = @char_Chance WHERE UID = @char_uid ELSE IF @Div = 2 UPDATE CharInfo_3 SET Class = @char_Class, Fame = @char_Fame, Mileage = @char_Mileage, Level = @char_Level, Equip = @char_Equip, Gold = @char_Gold, IP = @char_IP, STR = @char_STR, DEX = @char_DEX, CON = @char_CON, Intell = @char_INT, WIS = @char_WIS, HP = @char_HP, MP = @char_MP, Exp = @char_Exp, Chance = @char_Chance WHERE UID = @char_uid ELSE UPDATE CharInfo_4 SET Class = @char_Class, Fame = @char_Fame, Mileage = @char_Mileage, Level = @char_Level, Equip = @char_Equip, Gold = @char_Gold, IP = @char_IP, STR = @char_STR, DEX = @char_DEX, CON = @char_CON, Intell = @char_INT, WIS = @char_WIS, HP = @char_HP, MP = @char_MP, Exp = @char_Exp, Chance = @char_Chance WHERE UID = @char_uid COMMIT TRAN IF EXISTS (SELECT CID FROM CharInfo_Time WHERE CID = @char_uid) BEGIN UPDATE CharInfo_Time SET UpdateTime = GETDATE() WHERE CID = @char_uid END ELSE BEGIN INSERT INTO CharInfo_Time(CID,CreateTime,UpdateTime) VALUES (@char_uid,'2003.06.25',GETDATE()) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* 유저 정보 변경 프로시져 */ ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharInfoEx.sql ---------------------------------------------------------------------------------------------------------------------- CREATE PROCEDURE dbo.UpdateCharInfoEx @CharID AS INT, /* 캐릭터 아이디 */ @Total AS INT, /* 전체 시간 */ @ServerID AS INT /* 존 */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN UPDATE CharInfoEx SET Total = @Total, ServerID = @ServerID WHERE UID = @CharID COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharItemQuick.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 정보 변경 프로시져 */ CREATE PROCEDURE dbo.UpdateCharItemQuick @char_uid AS INT, /* 캐릭터 고유 아이디 */ @C_Quick AS BINARY(100) /* 장비 (2 * 15 = 30) */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN UPDATE CharItem SET Quick = @C_Quick WHERE UID = @char_uid COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharParty.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 파티 관계 업데이트 */ CREATE PROCEDURE dbo.UpdateCharParty @UID AS INT, /* 캐릭터 고유 아이디 */ @Party AS INT /* 파티 */ AS SET NOCOUNT ON DECLARE @Div INT SET @Div = @UID % 4 IF @Div = 0 UPDATE CharInfo_1 SET Party = @Party WHERE UID = @UID ELSE IF @Div = 1 UPDATE CharInfo_2 SET Party = @Party WHERE UID = @UID ELSE IF @Div = 2 UPDATE CharInfo_3 SET Party = @Party WHERE UID = @UID ELSE IF @Div = 3 UPDATE CharInfo_4 SET Party = @Party WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharPos.sql ---------------------------------------------------------------------------------------------------------------------- /* 캐릭터 위치 정보 업데이트 프로시져 */ CREATE PROCEDURE dbo.UpdateCharPos @UID AS INT, /* 캐릭터 고유 아이디 */ @LastPointX AS REAL, /* 캐릭터 Last Point X 좌표 */ @LastPointY AS REAL, /* 캐릭터 Last Point Y 좌표 */ @LastPointZ AS REAL, /* 캐릭터 Last Point Z 좌표 */ @SavePointX AS REAL, /* 캐릭터 Last Point X 좌표 */ @SavePointY AS REAL, /* 캐릭터 Last Point Y 좌표 */ @SavePointZ AS REAL /* 캐릭터 Last Point Z 좌표 */ AS SET NOCOUNT ON DECLARE @Div INT SET @Div = @UID % 4 IF @Div = 0 UPDATE CharPos_1 SET LastPointX = @LastPointX, LastPointY = @LastPointY, LastPointZ = @LastPointZ, SavePointX = @SavePointX, SavePointY = @SavePointY, SavePointZ = @SavePointZ WHERE UID = @UID ELSE IF @Div = 1 UPDATE CharPos_2 SET LastPointX = @LastPointX, LastPointY = @LastPointY, LastPointZ = @LastPointZ, SavePointX = @SavePointX, SavePointY = @SavePointY, SavePointZ = @SavePointZ WHERE UID = @UID ELSE IF @Div = 2 UPDATE CharPos_3 SET LastPointX = @LastPointX, LastPointY = @LastPointY, LastPointZ = @LastPointZ, SavePointX = @SavePointX, SavePointY = @SavePointY, SavePointZ = @SavePointZ WHERE UID = @UID ELSE IF @Div = 3 UPDATE CharPos_4 SET LastPointX = @LastPointX, LastPointY = @LastPointY, LastPointZ = @LastPointZ, SavePointX = @SavePointX, SavePointY = @SavePointY, SavePointZ = @SavePointZ WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharSkill.sql ---------------------------------------------------------------------------------------------------------------------- /* 스킬 업데이트 */ CREATE PROCEDURE dbo.UpdateCharSkill @UID AS INT, /* 사용자 아이디 */ @Skill AS BINARY(84) /* 스킬 슬롯 */ AS SET NOCOUNT ON DECLARE @Div INT SET @Div = @UID % 4 IF @Div = 0 UPDATE CharSkill_1 SET Skill = @Skill WHERE UID = @UID ELSE IF @Div = 1 UPDATE CharSkill_2 SET Skill = @Skill WHERE UID = @UID ELSE IF @Div = 2 UPDATE CharSkill_3 SET Skill = @Skill WHERE UID = @UID ELSE IF @Div = 3 UPDATE CharSkill_4 SET Skill = @Skill WHERE UID = @UID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateCharUID.sql ---------------------------------------------------------------------------------------------------------------------- /* 유저 정보 테이블 캐릭터 아이디 업데이트 프로시져 */ CREATE PROCEDURE dbo.UpdateCharUID @user_uid AS INT, /* 유저 고유 아이디 */ @user_pos AS INT, /* 캐릭터 위치 (3개의 슬롯) */ @char_uid AS INT /* 캐릭터 고유 아이디 */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN /* 유저 정보 테이블 업데이트 */ SET @Div = @user_uid % 4 IF @user_pos = 0 BEGIN IF @Div = 0 BEGIN UPDATE UserInfo_1 SET Char1 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 1 BEGIN UPDATE UserInfo_2 SET Char1 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 2 BEGIN UPDATE UserInfo_3 SET Char1 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 3 BEGIN UPDATE UserInfo_4 SET Char1 = @char_uid WHERE UID = @user_uid END END ELSE IF @user_pos = 1 BEGIN IF @Div = 0 BEGIN UPDATE UserInfo_1 SET Char2 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 1 BEGIN UPDATE UserInfo_2 SET Char2 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 2 BEGIN UPDATE UserInfo_3 SET Char2 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 3 BEGIN UPDATE UserInfo_4 SET Char2 = @char_uid WHERE UID = @user_uid END END ELSE IF @user_pos = 2 BEGIN IF @Div = 0 BEGIN UPDATE UserInfo_1 SET Char3 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 1 BEGIN UPDATE UserInfo_2 SET Char3 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 2 BEGIN UPDATE UserInfo_3 SET Char3 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 3 BEGIN UPDATE UserInfo_4 SET Char3 = @char_uid WHERE UID = @user_uid END END ELSE IF @user_pos = 3 BEGIN IF @Div = 0 BEGIN UPDATE UserInfo_1 SET Char4 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 1 BEGIN UPDATE UserInfo_2 SET Char4 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 2 BEGIN UPDATE UserInfo_3 SET Char4 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 3 BEGIN UPDATE UserInfo_4 SET Char4 = @char_uid WHERE UID = @user_uid END END ELSE IF @user_pos = 4 BEGIN IF @Div = 0 BEGIN UPDATE UserInfo_1 SET Char5 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 1 BEGIN UPDATE UserInfo_2 SET Char5 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 2 BEGIN UPDATE UserInfo_3 SET Char5 = @char_uid WHERE UID = @user_uid END ELSE IF @Div = 3 BEGIN UPDATE UserInfo_4 SET Char5 = @char_uid WHERE UID = @user_uid END END COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.UpdateItemStore @user_uid AS INT, /* 유저 고유 아이디 */ @Password AS CHAR(5), /* 패스 워드 */ @Frag AS INT, /* 플래그 */ @Gold AS INT /* 돈 */ AS SET NOCOUNT ON BEGIN TRAN UPDATE ItemStore1 SET Password = @Password, Frag = @Frag, Gold = @Gold WHERE UID = @user_uid COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdatePartyInfo.sql ---------------------------------------------------------------------------------------------------------------------- /* 파티 맴버 추가 프로시져 */ CREATE PROCEDURE dbo.UpdatePartyInfo @Party_uid AS INT, /* 파티 고유 아이디 */ @Party AS BINARY(202) /* 친구 리스트 */ AS SET NOCOUNT ON DECLARE @Div INT /* 파티 정보 테이블 업데이트 */ SET @Div = @Party_uid % 4 IF @Div = 0 BEGIN UPDATE PartyInfo_1 SET Party = @Party WHERE UID = @Party_uid END ELSE IF @Div = 1 BEGIN UPDATE PartyInfo_2 SET Party = @Party WHERE UID = @Party_uid END ELSE IF @Div = 2 BEGIN UPDATE PartyInfo_3 SET Party = @Party WHERE UID = @Party_uid END ELSE IF @Div = 3 BEGIN UPDATE PartyInfo_4 SET Party = @Party WHERE UID = @Party_uid END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------------------------------- -- UpdateUserInfo.sql ---------------------------------------------------------------------------------------------------------------------- /* 유저 정보 변경 프로시져 */ CREATE PROCEDURE dbo.UpdateUserInfo @user_uid AS INT, /* 유저 고유 아이디 */ @user_char1 AS INT, /* 캐릭터 고유 아이디 (첫번째) */ @user_char2 AS INT, /* 캐릭터 고유 아이디 (두번째) */ @user_char3 AS INT /* 캐릭터 고유 아이디 (세번째) */ AS SET NOCOUNT ON DECLARE @Div INT BEGIN TRAN SET @Div = @user_uid % 4 IF @Div = 0 UPDATE UserInfo_1 SET Char1 = @user_char1, Char2 = @user_char2, Char3 = @user_char3 WHERE UID = @user_uid ELSE IF @Div = 1 UPDATE UserInfo_2 SET Char1 = @user_char1, Char2 = @user_char2, Char3 = @user_char3 WHERE UID = @user_uid ELSE IF @Div = 2 UPDATE UserInfo_3 SET Char1 = @user_char1, Char2 = @user_char2, Char3 = @user_char3 WHERE UID = @user_uid ELSE UPDATE UserInfo_4 SET Char1 = @user_char1, Char2 = @user_char2, Char3 = @user_char3 WHERE UID = @user_uid COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC WEB_SelectCharName @UID INT,@CharName1 VARCHAR(20) OUTPUT,@CharName2 VARCHAR(20) OUTPUT, @CharName3 VARCHAR(20) OUTPUT AS SET NOCOUNT ON DECLARE @Char1 INT,@Char2 INT,@Char3 INT SELECT @Char1 = (CASE WHEN Char1 IS NULL THEN 0 ELSE Char1 END), @Char2 = (CASE WHEN Char2 IS NULL THEN 0 ELSE Char2 END), @Char3= (CASE WHEN Char3 IS NULL THEN 0 ELSE Char3 END) FROM UserInfo WHERE UID = @UID IF (@Char1 <> 0 ) SELECT @CharName1 = Name FROM CharInfo WHERE UID = @Char1 IF (@Char2 <> 0 ) SELECT @CharName2 = Name FROM CharInfo WHERE UID = @Char2 IF (@Char3 <> 0 ) SELECT @CharName3 = Name FROM CharInfo WHERE UID = @Char3 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROC agt_CharDelHistory AS SET NOCOUNT ON DECLARE @ROWID INT DECLARE @CID INT DECLARE @GETDATE SMALLDATETIME DECLARE @Table TABLE ( ROWID INT IDENTITY(1,1) PRIMARY KEY , intCID INT ) SET @GETDATE = GETDATE() INSERT INTO @Table SELECT intCID FROM TblCharDelHistory WHERE DateDeleteTime < @GETDATE SET @ROWID = @@ROWCOUNT WHILE @ROWID <> 0 BEGIN SELECT @CID = intCID FROM @Table WHERE ROWID = @ROWID /* 캐릭터 삭제 */ EXEC DeleteCharInfo @CID --INSERT INTO CIDStore (UID) VALUES (@CID) SET @ROWID = @ROWID - 1 END INSERT INTO CharDelHistory.dbo.TblCharDelHistory_LOG SELECT * FROM TblCharDelHistory WHERE DateDeleteTime < @GETDATE DELETE TblCharDelHistory WHERE DateDeleteTime < @GETDATE GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE TRIGGER trig_Charinfo ON CharInfo INSTEAD OF UPDATE AS SET NOCOUNT ON BEGIN DECLARE @UID INT IF EXISTS (SELECT UID FROM inserted) BEGIN SELECT @UID = UID FROM inserted IF EXISTS (SELECT UID FROM CharInfo where UID = @uid) BEGIN IF ((@UID % 4) = 0 ) BEGIN UPDATE CharInfo_1 SET Guild = 0,Party = 0 WHERE UID = @uid END ELSE IF ((@UID % 4) = 1 ) BEGIN UPDATE CharInfo_2 SET Guild = 0,Party = 0 WHERE UID = @uid END ELSE IF ((@UID % 4) = 2 ) BEGIN UPDATE CharInfo_3 SET Guild = 0,Party = 0 WHERE UID = @uid END ELSE IF ((@UID % 4) = 3 ) BEGIN UPDATE CharInfo_4 SET Guild = 0,Party = 0 WHERE UID = @uid END END END END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO