31 lines
1.1 KiB
SQL
31 lines
1.1 KiB
SQL
CREATE DEFINER=`ds`@`localhost` PROCEDURE `delete_data`()
|
|
BEGIN
|
|
DECLARE shipcall_id_var int;
|
|
DECLARE done INT DEFAULT FALSE;
|
|
|
|
DECLARE shipcall_iter CURSOR FOR
|
|
SELECT shipcall.id FROM shipcall
|
|
LEFT JOIN times ON
|
|
times.shipcall_id = shipcall.id AND times.participant_type = 8
|
|
WHERE
|
|
-- ARRIVAL
|
|
(type = 1 AND GREATEST(shipcall.eta, COALESCE(times.eta_berth, 0)) <= CURRENT_DATE() - INTERVAL 1 MONTH) OR
|
|
-- DEPARTURE / SHIFTING
|
|
(type != 1 AND GREATEST(shipcall.etd, COALESCE(times.etd_berth, 0)) <= CURRENT_DATE() - INTERVAL 1 MONTH);
|
|
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
|
|
|
OPEN shipcall_iter;
|
|
|
|
delete_loop: LOOP
|
|
FETCH shipcall_iter INTO shipcall_id_var;
|
|
IF done THEN
|
|
LEAVE delete_loop;
|
|
END IF;
|
|
DELETE FROM shipcall_participant_map WHERE shipcall_id = shipcall_id_var;
|
|
DELETE FROM shipcall_tug_map WHERE shipcall_id = shipcall_id_var;
|
|
DELETE FROM times WHERE shipcall_id = shipcall_id_var;
|
|
DELETE FROM shipcall WHERE id = shipcall_id_var;
|
|
END LOOP;
|
|
CLOSE shipcall_iter;
|
|
END |