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 THENROLLBACK;ELSECOMMIT;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;