DELIMITER $$
CREATE PROCEDURE check_delete(check_time datetime, stationid int, deleteid int)
        BEGIN
                DECLARE record_not_found INTEGER DEFAULT 0;
                DECLARE yml_time datetime DEFAULT "";
                DECLARE yml_id int DEFAULT -1;
                DECLARE yml_cursor CURSOR FOR select id, time from onces where time = check_time and station_id = stationid;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;  //cursor 终止的一个FLAG  这句声明必须紧跟cursor for,如果没有这句将会报错;
                OPEN yml_cursor;
                loop_lable2: LOOP
                        FETCH yml_cursor INTO yml_id, yml_time;
                        IF record_not_found THEN
                                LEAVE loop_lable2;
                        END IF;
                        IF yml_id != deleteid THEN
                                DELETE from times where once_id = yml_id;
                                DELETE from onces where id = yml_id;
                        END IF;
                END LOOP loop_lable2;
                CLOSE yml_cursor;
        END $$

dreambei
132 声望4 粉丝

下一篇 »
mysql ibdata1