Sample mysql Stored Procedure Code

Drop procedure if exists P_SEQUENCE; /** temporarily omitting the package @ auto liangrui 2014/6/27 T_PRO_PRODUCT table sorting: sort the entire table by sequence number. Sort the sequence number by sequence number from the creation date of the repeat sequence number. In the test phase, set @ merid is called. = 'test66 '; call P_SEQUENCE (@ merid); **/create procedure P_PRODUCT_SEQUENCE (in v_merchar_id VARCHAR (100) begin DECLARE v_id VARCHAR (100); DECLARE v_rowNo VARCHAR (100); DECLARE flag int; DECLARE e_error integer default 0; -- defines the cursor declare c_cur CURSOR for Select. id, (@ rowNum: = @ rowNum + 1) as rowNo From T_PRO_PRODUCT a, (Select (@ rowNum: = 0) B where MERCHANT_ID = v_merchar_id order by ISNULL (. sequence),. sequence,. 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; -- loop end until flag end repeat; -- CLOSE the cursor CLOSE c_cur; -- transaction processing IF e_error = 1 THENROLLBACK; ELSECOMMIT; end if; end

The above can be simplified.

SET @ colNo = 0;
UPDATE T_PRO_PRODUCT set sequence = (@ colNo: = @ colNo + 1) WHERE MERCHANT_ID = 'test66' order by sequence, CREATE_DT DESC;