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

2019独角兽企业重金招聘Python工程师标准>>>

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

 

转载于:https://my.oschina.net/u/3556610/blog/3018801