存储过程案例

DROP PROCEDURE IF EXISTS wms.chagecarpr2;
CREATE PROCEDURE wms.`chagecarpr2`()
BEGIN
declare i_rowon int default 0;
declare first_i_rowon int default 0;
declare so_id  decimal(9,0);
declare so_no  decimal(2,0);
declare carr_id decimal(9,0);
declare i_maxon INT;
declare first_i_maxon int;

declare first_cso_curon cursor for select ShipOrderID,SequenceNO,CarrierPrimary from twms_customershiporder  where CSOStatus in(9953,9952) and CarrierPrimary is not null;
select count(*) into first_i_maxon from twms_customershiporder  where CSOStatus in(9953,9952) and CarrierPrimary is not null;


open first_cso_curon;
getdataonfirst:loop
set first_i_rowon=first_i_rowon+1;
if first_i_rowon<first_i_maxon then 
fetch first_cso_curon into so_id,so_no,carr_id;
update twms_customershiporder set SelectedCarrier=carr_id where ShipOrderID=so_id and SequenceNO=so_no;
ITERATE getdataonfirst;
end if;
leave getdataonfirst;
end loop getdataonfirst;
close first_cso_curon;


END;