这个存储过程会阻塞啊。。谁给看一下。分析分析～

book_xiny120 2012-05-31 01:28:27

CREATE PROCEDURE [dbo].aspnet_Vstock_Order_MakeDealStr
@xmlData varchar(8000)
AS
BEGIN

declare @iError int
declare @idoc int
declare @MarketId int
declare @StockId varchar(50)
declare @fPrice decimal(38,5)
declare @BuyTax decimal(38,10)
declare @BuyFee decimal(38,10)
declare @SellTax decimal(38,10)
declare @SellFee decimal(38,10)
declare @NowTime datetime
declare @IntTime int

select @iError = 0

-- 先检查是不是超过了交易时间！超过了就不撮合！
select @NowTime = GetDate()
select @IntTime = DatePart(hh,@NowTime) * 100 + DatePart(mi,@NowTime)

if @IntTime < 929
return 0
if @IntTime > 1531
return 0
if @IntTime > 1131 and @IntTime < 1259
return 0

exec sp_xml_preparedocument @idoc OUTPUT, @xmlData

TRUNCATE TABLE dbo.aspnet_Vstock_StockReport

insert into dbo.aspnet_Vstock_StockReport (MarketId,StockId,NewPrice) select MarketId, StockId, NewPrice from OPENXML (@idoc, '/R/M/C',2)
WITH (
MarketId int '../@v',
StockId varchar(20) '@s',
NewPrice decimal(38,5) '@p'
)

exec sp_xml_removedocument @idoc

insert aspnet_vstock_StockNewPrice
select MarketId, StockId, NewPrice from aspnet_vstock_StockReport b
where not exists(select 1 from aspnet_vstock_StockNewPrice a where a.MarketId=b.MarketId and a.StockId=b.StockId)

print @@ROWCOUNT

Update aspnet_Vstock_StockNewPrice
set aspnet_Vstock_StockNewPrice.NewPrice = b.NewPrice
from dbo.aspnet_Vstock_StockReport b
where aspnet_Vstock_StockNewPrice.MarketId = b.MarketId and aspnet_Vstock_StockNewPrice.StockId = b.StockId

print @@ROWCOUNT

--set xact_abort on
select @iError = 0
-- 处理买单撮合
begin tran
-- 已成交，插入成交列表

Insert into dbo.aspnet_Vstock_Deal ([UserId], [MarketId], [StockId], [StockName],[BuyId], [BuyPrice], [BuyVol], [BuyTime], BuyTax, BuyFee)
SELECT UserId, MarketId, StockId, StockName, BuyId , (case when BuyPrice = 0 then NewPrice else BuyPrice end) , BuyVol, BuyTime,
/*b.BuyTax*/ ((case when BuyPrice = 0 then NewPrice else BuyPrice end) * BuyVol * BuyTax), /*b.BuyFee*/ ((case when BuyPrice = 0 then NewPrice else BuyPrice end) * BuyVol * BuyFee)
from aspnet_Vstock_BuyOrder_NewPrice_BuyTax_BuyFee
where BuyPrice >= NewPrice or BuyPrice = 0

select @iError = @iError + @@Error

-- 已成交，减去购买成本

update dbo.aspnet_Users set Vstock_TotalMoney = Vstock_TotalMoney - b.CostMoney
from aspnet_Vstock_BuyOrder_NewPrice_BuyTax_BuyFee_SumByUserId b
where dbo.aspnet_Users.UserId = b.UserId --and ( b.BuyPrice >= b.NewPrice or b.BuyPrice = 0 )
select @iError = @iError + @@Error

update dbo.aspnet_Membership set Vstock_Buyed = 1
from aspnet_Vstock_BuyOrder_NewPrice_BuyTax_BuyFee_SumByUserId b
where dbo.aspnet_Membership.UserId = b.UserId --and ( b.BuyPrice >= b.NewPrice or b.BuyPrice = 0 )

select @iError = @iError + @@Error

-- 已成交，从申买列表中删除
delete from dbo.[aspnet_Vstock_BuyOrder] where Id in (
SELECT b.BuyId
from aspnet_Vstock_BuyOrder_NewPrice_BuyTax_BuyFee b
where (BuyPrice >= NewPrice or BuyPrice = 0))

select @iError = @iError + @@Error

if @iError = 0
begin
commit
print '--------001@\r\n'
end
else
begin
rollback
print '--------001*\r\n'
end

-- 处理卖单撮合
select @iError = 0
begin tran

-- 一个买卖完成，结算赢利并放到总资金中

update dbo.aspnet_Users set Vstock_TotalMoney = Vstock_TotalMoney + b.CostMoney
from aspnet_Vstock_SellOrder_NewPrice_Tax_Fee_SumByUserId b
where dbo.aspnet_Users.UserId = b.UserId --and ( b.BuyPrice >= b.NewPrice or b.BuyPrice = 0 )

select @iError = @iError + @@Error

-- 一个买卖完成，加到历史记录中

Insert into dbo.aspnet_Vstock_DealHistory (SellOrderId, [UserId], [OrderType], [MarketId], [StockId], [StockName], [SellId], [SellPrice], [SellVol], [SellTime], [BuyId], [BuyPrice], [BuyTime], [DealId], [DealTime],
BuyTax, BuyFee,
SellTax, SellFee)
SELECT b.SellOrderId, b.UserId, b.OrderType, b.MarketId, b.StockId, b.StockName,b.[SellId], b.SellPrice , b.SellVol, b.SellTime, b.BuyId, b.BuyPrice, b.BuyTime, b.DealId, b.DealTime,
b.BuyTax, b.BuyFee,
(case when b.SellPrice = 0 then b.NewPrice else b.SellPrice end) * b.SellVol * b.SellTax,
(case when b.SellPrice = 0 then b.NewPrice else b.SellPrice end) * b.SellVol * b.SellFee
from aspnet_Vstock_SellOrder_NewPrice_Tax_Fee b
where DateDiff(hh, b.DealTime,GetDate()) >= 12 and (b.NewPrice >= b.SellPrice or b.SellPrice = 0)

select @iError = @iError + @@Error

-- 一个买卖完成，删除持仓数据
delete from dbo.[aspnet_Vstock_Sellorder] where Id in (
SELECT b.SellId
from aspnet_Vstock_SellOrder_NewPrice_Tax_Fee b
where DateDiff(hh, b.DealTime,GetDate()) >= 12 and (b.NewPrice >= b.SellPrice or b.SellPrice = 0))

select @iError = @iError + @@Error

if @iError = 0
begin
commit
print '--------001@\r\n'
end
else
begin
rollback
print '--------001*\r\n'
end

RETURN 0
END
GO

...全文