- DROP PROCEDURE IF EXISTS updateCustomerCharge;
- CREATE PROCEDURE updateCustomerCharge()
- --
- -- 实例 更新用户信息是否充值
- -- MYSQL存储过程名为:updateCustomerCharge
- --
- BEGIN
- declare _customer_id int(10);
- declare done int;
- -- 定义游标
- DECLARE rs_cursor CURSOR FOR
- select customer_id from virtualcurrency_logs where action='recharge_add' group by customer_id;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
- open rs_cursor;
- cursor_loop:loop
- FETCH rs_cursor into _customer_id; -- 取数据
- if done=1 then
- leave cursor_loop;
- end if;
- -- 更新表
- update customer_entity set is_charge=1 where entity_id=_customer_id ;
- end loop cursor_loop;
- close rs_cursor;
- END;
select min(stock_mv_id) into _stock_mv_id
- 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 ;