MySQL游标包括两部分_mysql 动态游标 , 实际上不要使用, 因为两个人同时执行会删除了其中一个人的数据...

CREATE DEFINER=`root`@`localhost` PROCEDURE `MoveOrderUtil_PartMove`(

in company_id_ int ,

in owner_id_ varchar(10),

in warehouse_ varchar(10),

in part_no_ varchar(20),

in lot_batch_no_ varchar(20),

in fr_location_no_ varchar(20),

in to_warehouse_ varchar(10),

in to_location_no_ varchar(20),

in qty_to_move_ int,

in user_id_ varchar(40))

BEGIN

DECLARE totalSum_ int ;

DECLARE qty_onhand_ int ;

DECLARE inv_stk_id_ int ;

DECLARE move_no_ varchar(20);

DECLARE move_id_ int ;

if nvl(lot_batch_no_ , '*') = '*' then

begin

DECLARE get_in_stock CURSOR FOR

SELECT sum(qty_onhand)

FROM inventory_part_in_stock

WHERE logistics_company_id = company_id_

and warehouse = warehouse_

and location_no = fr_location_no_

and part_no = part_no_ ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET totalSum_ = NULL;

OPEN get_in_stock;

FETCH get_in_stock INTO totalSum_;

CLOSE get_in_stock;

end;

else

begin

DECLARE get_in_stock CURSOR FOR

SELECT sum(qty_onhand)

FROM inventory_part_in_stock

WHERE logistics_company_id = company_id_

and warehouse = warehouse_

and location_no = fr_location_no_

and part_no = part_no_

and lot_batch_no = lot_batch_no_ ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET totalSum_ = NULL;

OPEN get_in_stock;

FETCH get_in_stock INTO totalSum_;

CLOSE get_in_stock;

end;

end if;

if(nvl(totalSum_ , 0) < qty_to_move_ ) then

CALL ErrorSys_RecordGeneral('MoveOrder', '库位 :P1 库存 : P2 少于需移库数量 : P3 !',

fr_location_no_,

totalSum_,

qty_to_move_);

end if;

begin

DECLARE get_in_stock CURSOR for( SELECT id , qty_onhand from temp_view);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET inv_stk_id_ = null;

DROP VIEW IF EXISTS temp_view;

SET @sqlstr = "CREATE VIEW temp_view as ";

if nvl(lot_batch_no_ , '*') = '*' then

SET @sqlstr = CONCAT(@sqlstr , "SELECT id , qty_onhand

FROM inventory_part_in_stock

WHERE logistics_company_id = ", company_id_ ,"

and warehouse = '", warehouse_ ,"'

and location_no = '", fr_location_no_ ,"'

and part_no = '", part_no_ ,"' ");

else

SET @sqlstr = CONCAT(@sqlstr , "SELECT id , qty_onhand

FROM inventory_part_in_stock

WHERE logistics_company_id = ", company_id_ ,"

and warehouse = '", warehouse_ ,"'

and location_no = '", fr_location_no_ ,"'

and part_no = '", part_no_ ,"'

and lot_batch_no = '", lot_batch_no_ ,"' ");

end if ;

PREPARE stmt FROM @sqlstr;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

OPEN get_in_stock;

moveLoop : loop

FETCH get_in_stock INTO inv_stk_id_ , qty_onhand_ ;

if inv_stk_id_ is NULL then

leave moveLoop;

end if ;

if qty_onhand_ >= qty_to_move_ then

-- 新增入库单

call MoveOrderUtil_NewOrder(move_no_ ,company_id_ , warehouse_ , inv_stk_id_ , qty_to_move_ , to_warehouse_ ,to_location_no_ , owner_id_ , 1 , user_id_);

leave moveLoop;

else

-- 新增入库单

call MoveOrderUtil_NewOrder(move_no_ ,company_id_ , warehouse_ , inv_stk_id_ , qty_to_move_ , to_warehouse_ ,to_location_no_ , owner_id_ , 1 , user_id_);

set qty_to_move_ = qty_to_move_ - qty_onhand_ ;

end if;

end loop moveLoop ;

close get_in_stock ;

set move_id_ = MoveOrderUtil_GetIdByUnindex(company_id_ ,move_no_ );

-- 执行移库确认

call MoveOrderUtil_ConfirmOrder(move_id_ , user_id_);

-- 执行移库完成

call MoveOrderUtil_ExecMove(move_id_ , user_id_);

end ;

END