查复制SQL
CREATE TABLE ITEM_DECODED(strUserID char(21) NULL DEFAULT ('-'),
strAccountID char(21) NULL DEFAULT ('-'),
Name varchar(50),
dwID int NULL,
strSerial varchar(50) null,
Durability int NULL,
StackSize smallint NULL,
ii NULL
)
CREATE TABLE DUPERS_LOGGED(
strUserID varchar(50) NULL DEFAULT ('-'),
strAccountID varchar(21) NULL DEFAULT ('-'),
Name char(50) NULL,
dwID int NULL,
strSerial varchar(50) null,
time smalldatetime NULL DEFAULT (getdate()),
ii smallint NULL
)
CREATE PROCEDURE xiandai_clear
AS
BEGIN
SET NOCOUNT ON;
declare @StrUserID char(21)
truncate table ITEM_clone
truncate table item_decoded
DECLARE Backup_Cursor CURSOR
FOR
SELECT struserID from userdata
OPEN Backup_Cursor
FETCH NEXT FROM Backup_Cursor INTO @StrUserID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @row int, @i int, @count int, @dwid int, @type
varbinary(4), @StackSize int, @strSerial char(50), @name char(50)
SET @i = 1
set @name = ''
WHILE @i <401
BEGIN
SELECT @dwid = cast(cast(substring(cast(strItem as varbinary(400)), @i + 4, 1)+substring(cast(strItem as varbinary(400)), @i + 3, 1)+substring(cast(strItem as varbinary(400)),@i + 2 , 1)+substring(cast(strItem as varbinary(400)),@i + 1 , 1) as varbinary(4)) as int(4)),
@strSerial = cast(cast(substring(cast(strSerial as varbinary(400)),@i + 8,1)+substring(cast(strSerial as varbinary(400)),@i + 7 , 1)+substring(cast(strSerial as varbinary(400)),@i + 6 , 1)+substring(cast(strSerial as varbinary(400)), @i + 5 , 1)+substring(cast(strSerial as varbinary(400)),@i + 4 , 1)+substring(cast(strSerial as varbinary(400)),@i + 3 , 1)+substring(cast(strSerial as varbinary(400)), @i + 2 , 1)+substring(cast(strSerial as varbinary(400)),@i + 1 , 1) as varbinary(8)) as bigint),
@type = cast(substring(strItem, @i+4, 2) as varbinary(2)),
@StackSize = cast(cast(cast( substring(strItem, @i+7, 1) as
varbinary(1))+cast(substring(strItem, @i+6, 1) as varbinary(1)) as
varbinary(2)) as smallint)
FROM USERDATA
WHERE strUserID = @StrUserID
select @name = strname from item where num = @dwid
IF @dwid <> 0
INSERT INTO ITEM_DECODED (strUserID, Name, dwid, strSerial,
Durability, StackSize, ii)
VALUES (@StrUserId, @Name, @dwid, @strSerial, @type, @StackSize,
(@i-1) / 8)
SET @i = @i + 8
END
FETCH NEXT FROM Backup_Cursor INTO @StrUserID
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
--仓库里复制的东西
DECLARE Backup_Cursor CURSOR
FOR
SELECT struserID from userdata
OPEN Backup_Cursor
FETCH NEXT FROM Backup_Cursor INTO @StrUserID
WHILE @@FETCH_STATUS = 0
BEGIN
-- DECLARE @row int, @i int, @count int, @dwid int, @type
-- varbinary(4), @StackSize int, @strSerial char(50), @name char(50)
SET @i = 1
set @name = ''
WHILE @i <192
BEGIN
SELECT @dwid = cast(cast(substring(cast(WarehouseData as varbinary(1600)),@i + 4 , 1)+substring(cast(WarehouseData as varbinary(1600)), @i+ 3 , 1)+substring(cast(WarehouseData as varbinary(1600)), @i + 2 , 1)+substring(cast(WarehouseData as varbinary(1600)),@i+ 1 , 1) as varbinary(4)) as int(4)),
@strSerial = cast(cast(substring(cast(strSerial as varbinary(1600)),@i + 8 , 1)+substring(cast(strSerial as varbinary(1600)),@i + 7 , 1)+substring(cast(strSerial as varbinary(1600)),@i + 6, 1)+substring(cast(strSerial as varbinary(1600)),@i + 5 , 1)+substring(cast(strSerial as varbinary(1600)), @i + 4 , 1)+substring(cast(strSerial as varbinary(1600)),@i + 3, 1)+substring(cast(strSerial as varbinary(1600)), @i+ 2 , 1)+substring(cast(strSerial as varbinary(1600)),@i+ 1 , 1) as varbinary(8)) as bigint),
@type = cast(cast(substring(cast(WarehouseData as varbinary(1600)),@i + 6 , 1)+substring(cast(WarehouseData as varbinary(1600)), @i + 5 , 1) as varbinary(2)) as smallint(2)) ,
@StackSize = cast(cast(substring(cast(WarehouseData as varbinary(1600)),@i + 8 , 1)+substring(cast(WarehouseData as varbinary(1600)), @i + 7 , 1) as varbinary(2)) as smallint(2))
from WAREHOUSE inner join account_char on warehouse.strAccountId=account_char.strAccountId
WHERE account_char.strCharId1= @StrUserID
select @name = strname from item where num = @dwid
IF @dwid <> 0
INSERT INTO ITEM_DECODED (strUserID, Name, dwid, strSerial,
Durability, StackSize, ii)
VALUES (@StrUserId, @Name, @dwid, @strSerial, @type, @StackSize,
(@i-1) / 8)
SET @i = @i + 8
END
FETCH NEXT FROM Backup_Cursor INTO @StrUserID
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
--清装备
DECLARE @rowcount int
declare @sSerialchar(50)
declare @fz int
declare @strAccoutID char(30)
select @rowcount=max(Id) from item_decoded
SET @i = 1
WHILE @i <@rowcount
BEGIN
select @StrUserID=struserID,@sSerial=strSerial from item_decoded where id =@i
select @fz=count(strSerial) from item_decoded where strSerial=@sSerial and strSerial<>'0' and id<>@i and strSerial <> '74601890051852267'
if @fz>0
begin
--select @sSerial
--select @fz
update USERDATA set stritem='',strserial='',gold=0 where struserId=@StrUserID
select @strAccoutID=straccountId from account_char where strCharId1=@StrUserID
update WAREHOUSE set strserial='',warehousedata='' where strAccountId=@strAccoutID
INSERT INTO ITEM_clone (strUserID, , dwid, strSerial,
ii)
VALUES (@StrUserId, '','', @sSerial, (@i-1) / 8)
end
SET @i = @i + 1
END
END
GO
页:
[1]