记录一次存储过程的使用（1）

DELIMITER //
DROP PROCEDURE
IF EXISTS UPDATE_SERIAL_NUMBER//

CREATE PROCEDURE UPDATE_SERIAL_NUMBER ()
BEGIN
  DECLARE done  INT DEFAULT 0;/*游标循环标志*/
  DECLARE v_id BIGINT (20) ;

    DECLARE ids CURSOR FOR
        select '-1' as id
        UNION all
        select tb.id from t_dic tb where tb.parent_id='-1' and TYPE = 'DIC_PRODUCTTYPE' AND tb.validity='1'
        UNION all
        select tb.id from t_dic tb where tb.parent_id is null and TYPE = 'DIC_PRODUCTTYPE' AND tb.validity='1';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;

    OPEN ids ;
    id : LOOP
    FETCH ids INTO v_id;
      IF done = 1 THEN
        LEAVE id ;
      END IF ;
          BEGIN
                IF v_id is not null and v_id <> '' THEN
                        select @num:=IF(max(serial_number) is NULL,0,max(serial_number)) from t_product;
                        UPDATE t_product p RIGHT JOIN (
                        SELECT ID FROM t_product where typeId = v_id ORDER BY `timeStorage` DESC
                        ) a on p.ID = a.ID, (SELECT @orderItem:=0) b set p.serial_number = @num + (@orderItem:=@orderItem+1);
                    SET done = 0;
                END IF ;
            END;
    END LOOP id ;
    CLOSE ids ;
END ;

call UPDATE_SERIAL_NUMBER();

DROP PROCEDURE IF EXISTS UPDATE_SERIAL_NUMBER;