فراخوانی StroreProcedure در Hibernate
دارم به یه مساله ای فکر میکنم: من یک StoreProcedure نوشتم که میاد عملیات FEFO را در انبار انجام میدهد.آی دی درخواستی که به انبار اومده بعنوان ورودی بهش پاس داده میشه و میره اون کالایی که تاریخ انقضاش زودتره رو برمیداره و آماده خروج از انبار میکنه.
این StoreProcedure الان داره کار میکنه بنظر شما اگه بخوام این تراکنش ها رو با کمک هایبرنیت پیاده سازی کنم قضیه چه جوریه :
ALTER PROCEDURE [dbo].[SP14_FEFO]
@OrderID VARCHAR(200)
AS
BEGIN
--################################################################
DELETE FROM dbo.OrderBatchLack WHERE OrderID=@OrderID --delete kasri allocate systemi
--################################################################
DECLARE crsA CURSOR LOCAL FAST_FORWARD FOR
SELECT
Goods.ID AS GoodsID
, Goods.GoodCode
, Goods.GoodName
, OrderDetail.ODPUQU - dbo.FN22_FindDisplacedForOrder(@OrderID,Goods.ID) AS GoodsQty
FROM
Goods INNER JOIN
OrderDetail ON Goods.ID = OrderDetail.GoodsID INNER JOIN
OrderHeader ON OrderDetail.OrderID = OrderHeader.ID
WHERE
OrderHeader.Activation = 1
AND OrderDetail.Activation = 1
AND OrderHeader.ID = @OrderID
AND OrderDetail.ODPUQU - dbo.FN22_FindDisplacedForOrder(@OrderID,Goods.ID) <> 0
--=====================================================================================
OPEN crsA;
declare @crsA_GoodsID varchar(50)
declare @crsA_GoodCode varchar(50)
declare @crsA_GoodName varchar(50)
declare @crsA_GoodsQty varchar(50)
FETCH NEXT FROM crsA INTO @crsA_GoodsID, @crsA_GoodCode, @crsA_GoodName , @crsA_GoodsQty
--=====================================================================================
WHILE @@FETCH_STATUS = 0
BEGIN
declare @x bigint
set @x = CAST(@crsA_GoodsQty as varchar(50))
PRINT('init @x= '+cast(@x as varchar(50)));
--#######################################################################
DECLARE crsB CURSOR LOCAL FAST_FORWARD FOR -- Nested cursor
SELECT ID, OnHandy FROM Batch WHERE
Activation = 1
AND OnHandy <> 0
AND GoodsID = @crsA_GoodsID
ORDER BY BExpireDate , BBatchNo
OPEN crsB
declare @crsB_BatchID varchar(50)
declare @crsB_OnHandy bigint
FETCH NEXT FROM crsB INTO @crsB_BatchID , @crsB_OnHandy
WHILE @@FETCH_STATUS = 0
BEGIN
--========================================================
IF(@x = 0) BEGIN BREAK; END
PRINT('@crsB_BatchID = '+@crsB_BatchID);
PRINT('@crsB_OnHandy = '+cast(@crsB_OnHandy as varchar(50)) );
--========================================================
set @x = @x - @crsB_OnHandy
--========================================================
declare @khoruj bigint
if(@x>=0)
begin
set @khoruj = @crsB_OnHandy
end;
else if(@x <0)
begin
set @khoruj = @x + @crsB_OnHandy
set @x=0;
end;
--========================================================
--PRINT('@khoruj = '+CAST(@khoruj AS VARCHAR(50)));
declare @khorujCU as varchar(50)
select @khorujCU = dbo.FN12_GetQty('Case.Unit',@crsA_GoodsID,@khoruj)
--PRINT('@khorujCU = '+@khorujCU);
exec dbo.SP07_OrderBatch 'INSERT',@OrderID,'',@crsA_GoodsID,@crsB_BatchID ,@khorujCU
--========================================================
FETCH NEXT FROM crsB INTO @crsB_BatchID , @crsB_OnHandy
END
CLOSE crsB
DEALLOCATE crsB
--#######################################################################
PRINT('final @x = '+cast(@x as varchar(50)));
if(@x<>0)
begin
insert into dbo.OrderBatchLack(OrderID,GoodsID,GoodsCode,GoodsName,ItemQty) values (@OrderID,@crsA_GoodsID,@crsA_GoodCode, @crsA_GoodName,@x)
end
--#######################################################################
FETCH NEXT FROM crsA INTO @crsA_GoodsID, @crsA_GoodCode, @crsA_GoodName , @crsA_GoodsQty
END
CLOSE crsA;
DEALLOCATE crsA;
--=====================================================================================
END
امیر حسین میثمی 
