CREATE PROCEDURE DELETE_DOUBLE_CHECKNUMBER()
BEGIN
DECLARE is_curson_over boolean;
DECLARE count_value INT DEFAULT 0;
DECLARE unit_number_value INT DEFAULT 0;
DECLARE checknumber_value VARCHAR(50);
DECLARE temp_checknumber_value_1 VARCHAR(50);
DECLARE temp_checknumber_value_2 VARCHAR(50);
DECLARE temp_checknumber_value_3 VARCHAR(50);
DECLARE unit_id_value INT DEFAULT 0;
DECLARE curson_checknumber CURSOR FOR SELECT unit_id,checknumber FROM ancientbook GROUP BY checknumber having COUNT(checknumber) > 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_curson_over = true;
SELECT COUNT(*) INTO @count_value FROM ancientbook where checknumber in (SELECT checknumber FROM ancientbook GROUP BY checknumber HAVING COUNT(checknumber) > 1);
IF @count_value > 0 THEN
OPEN curson_checknumber;
LOOP_CHECKNUMBER : LOOP
IF is_curson_over THEN
LEAVE LOOP_CHECKNUMBER;
END IF;
FETCH curson_checknumber INTO unit_id_value,checknumber_value;
IF unit_id_value <=> NULL THEN
SET @temp_checknumber_value_1 = CONCAT('\'',checknumber_value,'\'');
SET @temp_checknumber_value_2 = CONCAT('\'',RIGHT(checknumber_value,7),'\'');
SET @temp_checknumber_value_3 = SUBSTRING(checknumber_value,8,4);
SELECT @temp_checknumber_value_1;
SELECT @temp_checknumber_value_2;
SELECT @temp_checknumber_value_3;
DELETE FROM ancientbook WHERE checknumber = @temp_checknumber_value_1;
DELETE FROM t_checknumber WHERE checknumber = @temp_checknumber_value_2 AND unit_number = @temp_checknumber_value_3;
COMMIT;
ELSE
SELECT unit_id_value;
END IF;
END LOOP LOOP_CHECKNUMBER;
CLOSE curson_checknumber;
ELSE
SELECT * FROM ANCIENTBOOK;
END IF;
END;DELETE FROM ancientbook WHERE checknumber = @temp_checknumber_value_1;这一句删除应该没问题吧?但是数据库没反应。
但是把后面的@temp_checknumber_value_1变量换成实际值却可以删除,而@temp_checknumber_value_1这个值能够正常得到,实在无解,应该可以排序事务的原因吧。
那你是试试select * FROM ancientbook WHERE checknumber = @temp_checknumber_value_1;看看有没有结果集
那就应该是@temp_checknumber_value_1赋值不成功。
但是SELECT @temp_checknumber_value_1;打印出来的值是对的。奇怪。
BEGIN
DECLARE is_curson_over boolean;
DECLARE count_value INT DEFAULT 0;
DECLARE unit_number_value INT DEFAULT 0;
DECLARE checknumber_value VARCHAR(50);
DECLARE unit_id_value INT DEFAULT 0;
DECLARE curson_checknumber CURSOR FOR SELECT unit_id,checknumber FROM ancientbook GROUP BY checknumber having COUNT(checknumber) > 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_curson_over = true;
SELECT COUNT(*) INTO @count_value FROM ancientbook where checknumber in (SELECT checknumber FROM ancientbook GROUP BY checknumber HAVING COUNT(checknumber) > 1);
IF @count_value > 0 THEN
OPEN curson_checknumber;
LOOP_CHECKNUMBER : LOOP
IF is_curson_over THEN
LEAVE LOOP_CHECKNUMBER;
END IF;
FETCH curson_checknumber INTO unit_id_value,checknumber_value;
IF unit_id_value <=> NULL THEN
SELECT RIGHT(checknumber_value,7);
SELECT SUBSTRING(checknumber_value,8,4);
DELETE FROM ancientbook WHERE checknumber = checknumber_value;
DELETE FROM t_checknumber WHERE checknumber = RIGHT(checknumber_value,7) AND unit_number = SUBSTRING(checknumber_value,8,4);
COMMIT;
ELSE
SELECT unit_id_value;
END IF;
END LOOP LOOP_CHECKNUMBER;
CLOSE curson_checknumber;
ELSE
SELECT * FROM ANCIENTBOOK;
END IF;
END;去掉 CONCAT('\'',checknumber_value,'\''); 直接赋值无需加引号,这个地方很奇怪哦。