3>存储过程维护项目数据

DROP PROCEDURE IF EXISTS repalceSizeService;
CREATE PROCEDURE repalceSizeService()
BEGIN
declare _size_level int(10); 
declare _price int(10); 
declare _code_business VARCHAR(30); 
declare done int;

-- 定义游标
DECLARE rs_cursor CURSOR FOR 
select item_code_business,size_level,price from imc_channelbusiness_service_item_size_price where type_code=@old;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

open rs_cursor; 
cursor_loop:loop
   FETCH rs_cursor into _code_business,_size_level,_price; -- 取数据
   
   if done=1 then
    leave cursor_loop;
   end if;
   	
-- 更新表
   update imc_channelbusiness_service_item_size_price set price=_price where size_level =_size_level and item_code_business=_code_business and type_code=@new;

end loop cursor_loop;
close rs_cursor;

END;

 set @old = 'ZETY5317788667869077';
 set @new = 'ZETY5317788849451086';
 call repalceSizeService();

带参数

DROP PROCEDURE IF EXISTS copyPhoneJson;
CREATE PROCEDURE copyPhoneJson(in _month VARCHAR(30), in _vendor_code VARCHAR(30))
BEGIN
declare _phone VARCHAR(40); 
declare _price_json VARCHAR(3000); 
declare done int;
 
-- 定义游标
DECLARE rs_cursor CURSOR FOR 
select phone,price_json from bms_cost_phones where month='2019-08' and call_vendor=_vendor_code and price_json is not null;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
 
open rs_cursor; 
cursor_loop:loop
   FETCH rs_cursor into _phone,_price_json; -- 取数据
   
   if done=1 then
    leave cursor_loop;
   end if;
   	
-- 更新表
   update bms_cost_phones set price_json=_price_json where month=_month and call_vendor=_vendor_code and phone=_phone;
 
end loop cursor_loop;
close rs_cursor;
 
END;

DELIMITER $$

DROP PROCEDURE IF EXISTS updateStockMoves $$

CREATE PROCEDURE updateStockMoves()
-- 
-- 实例
-- MYSQL存储过程名为：updateStockMoves
--
BEGIN
declare _stock_sup_id int(10);
declare _enter_time datetime ; 
declare _qty double ;
declare _out_time datetime ;
declare _stock_mv_id int(10);    
declare done int;

-- 定义游标
DECLARE rs_cursor CURSOR FOR 
SELECT a.stock_sup_id,a.enter_time,a.qty,b.out_time 
from pre_doc_item a 
left join prepare_doc b on a.pre_doc_id=b.pre_doc_id order by a.pre_item_id asc;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

-- 获取昨天的日期
--if date_day is null then
--   set date_day = date_add(now(),interval -1 day);
--end if;

open rs_cursor; 
cursor_loop:loop
   FETCH rs_cursor into _stock_sup_id, _enter_time, _qty, _out_time; -- 取数据

   if done=1 then
    leave cursor_loop;
   end if;
     
	 select min(stock_mv_id) into _stock_mv_id from stock_moves where mv_time is null and job_id is null 
	 and stock_sup_id=_stock_sup_id 
	 and in_time=_enter_time and abs(qty)= _qty;

	 insert into temp_updateStockMoves_select(stock_sup_id,enter_time,qty,out_time,stock_mv_id) SELECT _stock_sup_id,_enter_time,_qty,_out_time,_stock_mv_id; 
	
-- 更新表
   update stock_moves set mv_time=_out_time where mv_time is null and job_id is null 
	 and stock_sup_id=_stock_sup_id 
	 and in_time=_enter_time and abs(qty)= _qty and stock_mv_id=_stock_mv_id;
   update stock_moves set mv_time=_out_time where mv_time is null and job_id is null 
	 and stock_sup_id=_stock_sup_id 
	 and in_time=_enter_time and abs(qty)= _qty and stock_mv_id=(_stock_mv_id+1);

insert into temp_updateStockMoves_update(stock_mv_id,mv_time) select _stock_mv_id,_out_time;

insert into temp_updateStockMoves_update(stock_mv_id,mv_time) select _stock_mv_id+1,_out_time;
	
end loop cursor_loop;
close rs_cursor;

END$$

DELIMITER ;