mysql存储过程参考

DROP procedure IF EXISTS 17FEI_UPDATE_INSIDENUMBER;
delimiter //
create procedure 17FEI_UPDATE_INSIDENUMBER() ## 创建存储过程
begin 
declare box_id varchar(50);
declare orderId varchar(50);
declare orderInside varchar(100);
declare done int default false;
declare tempIndex int;
declare tmepBoxId varchar(32);
declare boxIndex int;

DECLARE box_inside_number_isNull CURSOR FOR select distinct oo.id,oo.inside_number from orde_box as box inner join orde_order as oo on box.order_id=oo.id  where box.box_inside_number is null;


declare continue HANDLER for not found set done = true;

	open box_inside_number_isNull;

	read_loop:loop

			fetch box_inside_number_isNull into orderId,orderInside;
			if done then
				leave read_loop;
			end if;
			
			select count(1) into boxIndex from orde_box where order_id=orderId;

			while boxIndex>0 do

				set tempIndex = boxIndex-1;
				select bi.id into tmepBoxId from orde_box as bi where bi.order_id=orderId  order by id asc limit tempIndex,1;
				 
				update orde_box  set box_inside_number=concat(orderInside,"-",boxIndex), last_updated_date = NOW() where id=tmepBoxId;
				
				select concat(orderId,'-',boxIndex);
				
				set boxIndex=boxIndex-1;

			end while;
	
	end loop;

CLOSE  box_inside_number_isNull;

end;//

delimiter;
call 17FEI_UPDATE_INSIDENUMBER();