写示例的意义在于熟悉,下次需要的使用的可以快速查询,已经包含大部分语法,个人觉得可以解决80%的类似问题
DELIMITER $$
CREATE PROCEDURE update_core_prod_sequence ()
BEGIN
DECLARE prod_id_temp INT DEFAULT 0 ;
DECLARE var INT DEFAULT 0 ;
DECLARE prod_no_Temp VARCHAR (100) ;
DECLARE done INT ;
DECLARE temp CURSOR FOR
SELECT
prod_id
FROM
core_product
WHERE DELETE_FLAG = '1' ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
OPEN temp ;
REPEAT
FETCH temp INTO prod_id_temp ;
SET var = var + 1 ;
IF var > 10000
THEN SET prod_no_Temp = CONCAT('WP', var) ;
ELSEIF var >= 1000
THEN SET prod_no_Temp = CONCAT('WP0', var) ;
ELSEIF var >= 100
THEN SET prod_no_Temp = CONCAT('WP00', var) ;
ELSEIF var >= 10
THEN SET prod_no_Temp = CONCAT('WP000', var) ;
ELSEIF var > 0
THEN SET prod_no_Temp = CONCAT('WP0000', var) ;
END IF ;
UPDATE
CORE_PRODUCT
SET
PROD_NO = prod_no_Temp
WHERE prod_id = prod_id_temp
ORDER BY prod_id ASC ;
commit;
UNTIL done
END REPEAT ;
CLOSE temp ;
END $$
DELIMITER ;
CALL update_core_prod_sequence();