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;