在试运行项目中,因某种原因需要增加一个自增长的字段以适应客户方新需求,由于正式环境运维部门不让开发人员直接连数据库进行操作,所有过程均需通过脚本执行。为了完成该字段的新增,徐要首要对新增的自增字段历史记录填充值,故采用存储过程实现。
调试好的存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `T_FILE_AUTO_INCREMENT_ORDER_NO` $$
CREATE PROCEDURE T_FILE_AUTO_INCREMENT_ORDER_NO()
BEGIN
DECLARE count_row int DEFAULT 1;
DECLARE pid_tmp VARCHAR(50);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_record CURSOR FOR
select `pid` from `t_file`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_record;
read_loop: LOOP
FETCH cur_record INTO pid_tmp;
IF done THEN
LEAVE read_loop;
END IF;
update `t_file` set `t_file`.`order_no` = count_row where `t_file`.`pid` = pid_tmp;
set count_row = count_row + 1;
END LOOP;
CLOSE cur_record;
END $$
DELIMITER ;
调整过程中碰到的问题:
1、1054错误:
原代码片段:
DECLARE count_row int DEFAULT ‘1’;
修改后代码:
DECLARE count_row int DEFAULT 1;
原代码片段:
DECLARE count_row int DEFAULT 1;
DECLARE pid_tmp VARCHAR(50);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_record CURSOR FOR
select `pid` from `t_file`;
修改后代码:
DECLARE count_row int DEFAULT 1;
DECLARE pid_tmp VARCHAR(50);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_record CURSOR FOR
select `pid` from `t_file`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;