mysql存储过程示例代码_MySQL

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