bitscn.com drop procedure if exists p_sequence;/** 暂省略包 @auto liangrui 2014/6/27 t_pro_product 表 排序 对整个表进行按序号排序 根据序号从新自然排序 重复序号的安创建日期分配序号 测试阶段 测试调用 set @merid='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; -- 定义游标 declare c_cur cursor for select a.id ,(@rownum:=@rownum+1) as rowno from t_pro_product a ,(select (@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; -- 循环所有的行 repeat fetch c_cur into v_id,v_rowno; update t_pro_product set sequence= v_rowno where id=v_id; -- 循环结束 until flag end repeat; -- 关闭游标 close c_cur; -- 事务处理 if e_error = 1 then rollback; else commit; end if;end
上面的其实可以进行简化
set @colno = 0;
update t_pro_product set sequence=(@colno:=@colno+1) where merchant_id='test66' order by sequence, create_dt desc;
bitscn.com