一个简单的存储过程问题,谢谢大家帮我看一下,有个地方老是报错
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 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 <> 0 THEN
SELECT count(*) into @count_value FROM ANCIENTBOOK;
SELECT @count_value;
ELSE
SELECT uu.unit_number INTO @unit_number_value FROM userunit uu where uu.unit_id = @unit_id_value;
DELETE FROM ancientbook ab WHERE ab.checknumber = +"'"+@checknumber_value+"'";
DELETE FROM t_checknumber tu WHERE tu.checknumber = +"'"+RIGHT(@checknumber_value,7)+"'" and tu.unit_number = @unit_number_value;
END IF;
END LOOP LOOP_CHECKNUMBER;
CLOSE curson_checknumber;
ELSE
SELECT * FROM ANCIENTBOOK;
END IF;
END;DELETE FROM ancientbook ab WHERE ab.checknumber = +"'"+@checknumber_value+"'"; 这一句和下面一句总提示语法错误。
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 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 <> 0 THEN
SELECT count(*) into @count_value FROM ANCIENTBOOK;
SELECT @count_value;
ELSE
SELECT uu.unit_number INTO @unit_number_value FROM userunit uu where uu.unit_id = @unit_id_value;
DELETE FROM ancientbook ab WHERE ab.checknumber = +"'"+@checknumber_value+"'";
DELETE FROM t_checknumber tu WHERE tu.checknumber = +"'"+RIGHT(@checknumber_value,7)+"'" and tu.unit_number = @unit_number_value;
END IF;
END LOOP LOOP_CHECKNUMBER;
CLOSE curson_checknumber;
ELSE
SELECT * FROM ANCIENTBOOK;
END IF;
END;DELETE FROM ancientbook ab WHERE ab.checknumber = +"'"+@checknumber_value+"'"; 这一句和下面一句总提示语法错误。
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 <> 0 THEN
SELECT count(*) into @count_value FROM ANCIENTBOOK;
SELECT @count_value;
ELSE
SELECT uu.unit_number INTO @unit_number_value FROM userunit uu where uu.unit_id = @unit_id_value;
DELETE FROM ancientbook ab WHERE ab.checknumber = concat("'",@checknumber_value,"'");
DELETE FROM t_checknumber tu WHERE tu.checknumber = concat("'",RIGHT(@checknumber_value,7),"'") and tu.unit_number = @unit_number_value;
END IF;
END LOOP LOOP_CHECKNUMBER;
CLOSE curson_checknumber;
ELSE
SELECT * FROM ANCIENTBOOK;
END IF;
END;
DROP PROCEDURE IF EXISTS DELETE_DOUBLE_CHECKNUMBER$$
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 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 <> 0 THEN
SELECT COUNT(*) INTO @count_value FROM ANCIENTBOOK;
SELECT @count_value;
ELSE
SELECT uu.unit_number INTO @unit_number_value FROM userunit uu WHERE uu.unit_id = @unit_id_value;
DELETE FROM ancientbook WHERE checknumber=CONCAT('\'',@checknumber_value,'\'');
DELETE FROM t_checknumber WHERE checknumber = CONCAT('\'',RIGHT(@checknumber_value,7),'\'') AND tu.unit_number = @unit_number_value;
END IF;
END LOOP LOOP_CHECKNUMBER;
CLOSE curson_checknumber;
ELSE
SELECT * FROM ANCIENTBOOK;
END IF;
END$$
DELIMITER ;