Mysql stored procedure example code _ MySQL

Sample mysql stored procedure code

Drop procedure if existsP_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) beginDECLARE v_idVARCHAR (100); DECLARE v_rowNo VARCHAR (100); DECLARE flag int; DECLARE e_error integer default 0; -- defines the cursor DECLARE c_cur CURSORforSelect. id, (@ rowNum: = @ rowNum + 1) as rowNoFrom T_PRO_PRODUCT a, (Select (@ rowNum: = 0) bwhere MERCHANT_ID = v_merchar_idorder 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; -- REPEATFETCH c_cur INTO v_id, v_rowNo; update T_PRO_PRODUCT SET sequence = v_rowNo where ID = v_id; -- loop ends UNTIL flag end repeat; -- close the cursor CLOSEc_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;