MySQL Stored procedure sample code

drop procedure if exists p_sequence;/** temporarily omit package @AUTO Liangrui 2014/6/27 t_pro_product table sort the entire table by ordinal sort the repeating sequence number from the new natural sequence Create date allocation ordinal Test call Set @merid = ' TEST66 '; p_sequence (@merid), **/create procedure p_product_sequence (in v_merchar_id  varchar (+)) begin DECLARE v_id VARCHAR (100);  DECLARE v_rowno VARCHAR (100);  DECLARE flag int;    DECLARE e_error INTEGER DEFAULT 0; ---Define cursor DECLARE c_cur cursor for Select a.id, (@rowNum: [email protected]+1] as RowNo from T_pro_product A, (Se   Lect (@rowNum: =0)) b where merchant_id=v_merchar_id order by ISNULL (a.sequence), A.sequence,a.create_dt;    DECLARE CONTINUE HANDLER for not FOUND SET flag=1;    DECLARE CONTINUE HANDLER for SQLEXCEPTION SET e_error=1;    SET flag=0;    OPEN c_cur;         --Loop all rows REPEAT FETCH c_cur into V_id,v_rowno;       Update t_pro_product SET sequence= v_rowno where id=v_id;   --Cycle end UNTIL flag end REPEAT; --Closes the cursor close c_cur;--transaction if E_error = 1 thenrollback; Elsecommit; END If;end 

The above can actually be simplified

SET @colNo = 0;
UPDATE t_pro_product SET sequence= (@colNo: [email protected]+1) WHERE merchant_id= ' TEST66 ' ORDER by SEQUENCE, Create_dt DE SC;