CREATE PROCEDURE delete_values(IN ile INT) BEGIN DECLARE adone INT DEFAULT 0; DECLARE a INT DEFAULT 0; DECLARE b INT DEFAULT 0; DECLARE cnt INT DEFAULT 0; DECLARE laste INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT v.user_parameter_value_id, v.user_parameter_values_id FROM user_parameter_value AS v INNER JOIN ( SELECT user_parameter_values_id, COUNT(*) AS cnt FROM `user_parameter_value` GROUP BY user_parameter_values_id ) AS costam ON costam.cnt > 3 AND v.user_parameter_values_id = costam.user_parameter_values_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET adone = 1; OPEN cur1; REPEAT FETCH cur1 INTO a, b; IF NOT adone THEN IF b <> laste THEN SET cnt = 0; SET laste = b; ELSE IF cnt > ile THEN DELETE FROM user_parameter_value WHERE user_parameter_value_id = a; END IF; END IF; SET cnt = cnt+1; END IF; UNTIL adone END REPEAT; CLOSE cur1; END
Nie wiem czemu, ale nie mogę stworzyć procedury. Na mysql 5.0.19 ten kod powoduje błąd:
Kod
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma
nual that corresponds to your MySQL server version for the right syntax to use n
ear 'INT DEFAULT 0' at line 3
nual that corresponds to your MySQL server version for the right syntax to use n
ear 'INT DEFAULT 0' at line 3
Dlaczego? Declare jest w porządku, tak jak nakazuje dokumentacja..