DROP PROCEDURE DELETE_FROM_TABLE_T_SC_SERVICE_METHOD;
DELIMITER //
CREATE PROCEDURE DELETE_FROM_TABLE_T_SC_SERVICE_METHOD()
BEGIN
DECLARE method_svi_id varchar(40);
DECLARE count int DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE method_svi_id_cursor CURSOR FOR SELECT DISTINCT(FSMTH_SVI_ID) FROM T_SC_SERVICE_METHOD;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN method_svi_id_cursor;
loop1:LOOP
FETCH method_svi_id_cursor INTO method_svi_id;
SELECT COUNT(*) INTO count FROM T_SC_APP_SERVICE WHERE FSVI_ID=method_svi_id;
IF count<1 THEN
DELETE FROM T_SC_SERVICE_METHOD WHERE FSMTH_SVI_ID=method_svi_id;
END IF;
IF done>0 THEN
LEAVE loop1;
END IF;
END LOOP;
CLOSE method_svi_id_cursor;
END//
DELIMITER ;
show create procedure DELETE_FROM_TABLE_T_SC_SERVICE_METHOD;
CALL DELETE_FROM_TABLE_T_SC_SERVICE_METHOD();