mysql存儲(chǔ)過(guò)程調(diào)試記實(shí)
在試運(yùn)行項(xiàng)目中,因某種原因需要增加一個(gè)自增長(zhǎng)的字段以適應(yīng)客戶方新需求,由于正式環(huán)境運(yùn)維部門(mén)不讓開(kāi)發(fā)人員直接連數(shù)據(jù)庫(kù)進(jìn)行操作,所有過(guò)程均需通過(guò)腳本執(zhí)行。為了完成該字段的新增,徐要首要對(duì)新增的自增字段歷史記錄填充值,故采用存儲(chǔ)過(guò)程實(shí)現(xiàn)。
調(diào)試好的存儲(chǔ)過(guò)程:
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 ;
調(diào)整過(guò)程中碰到的問(wèn)題:
1、1054錯(cuò)誤:
原代碼片段:
DECLARE count_row int DEFAULT ‘1’;
修改后代碼:
DECLARE count_row int DEFAULT 1;
錯(cuò)誤原因:缺省值類型和變量類型不一致導(dǎo)致;
2、1338錯(cuò)誤:
原代碼片段:
BEGIN
DECLARE count_row int DEFAULT 1;
DECLARE pid_tmp VARCHAR(50);
DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE cur_record CURSOR FOR
select `pid` from `t_file`;
修改后代碼:
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; 錯(cuò)誤原因:游標(biāo)的error handler定義在游標(biāo)的定義語(yǔ)句之前導(dǎo)致;
3、變量的改值:
mysql和oracle的改值不同,在MySQL中變量的修改需要添加關(guān)鍵字set,如下:
set count_row = count_row + 1;
想了解更多?現(xiàn)在就開(kāi)始免費(fèi)體驗(yàn)