ALTER TABLE Ports DROP CONSTRAINT fk_cruise;
DROP TABLE Cruises;
DROP TABLE Ports;

drop sequence cruises_seq;
drop sequence ports_seq;

CREATE TABLE Cruises
(
cr_id NUMBER PRIMARY KEY,
c_name VARCHAR2(60) NOT NULL,
c_ship VARCHAR2(60) NOT NULL,
c_departure_date DATE NOT NULL, 
c_return_date DATE NOT NULL
);

CREATE TABLE Ports
(
p_id NUMBER PRIMARY KEY,
p_name VARCHAR2(60) NOT NULL,
cr_id NUMBER,
CONSTRAINT fk_cruise FOREIGN KEY (cr_id) REFERENCES Cruises(cr_id) ON DELETE CASCADE
);

CREATE SEQUENCE cruises_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ports_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PACKAGE CruiseDataPackage AS
PROCEDURE FillData;
PROCEDURE ClearData;
PROCEDURE DeleteCruise(p_cr_id IN INT);
END CruiseDataPackage;
/

CREATE OR REPLACE PACKAGE BODY CruiseDataPackage AS
PROCEDURE FillData IS
BEGIN

INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(1, ‘Средиземноморские жемчужины’, ‘Costa Diadema’, to_date(‘15.06.2022’, ‘dd.mm.yyyy’), to_date(‘30.06.2022’, ‘dd.mm.yyyy’));
INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(2, ‘Карибские приключения’, ‘Harmony of the Seas’, to_date(‘01.06.2022’, ‘dd.mm.yyyy’), to_date(‘10.06.2022’, ‘dd.mm.yyyy’));
INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(3, ’ Северное сияние’, ‘Hurtigruten MS Roald Amundsen’, to_date(‘20.09.2022’, ‘dd.mm.yyyy’), to_date(‘05.10.2022’, ‘dd.mm.yyyy’));
INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(4, ‘Австралийские просторы’, ‘Pacific Explorer’, to_date(‘10.10.2022’, ‘dd.mm.yyyy’), to_date(‘20.10.2022’, ‘dd.mm.yyyy’));
INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(5, ’ Южная Америка’, ’ Empress of the Seas’, to_date(‘01.11.2022’, ‘dd.mm.yyyy’), to_date(‘15.11.2022’, ‘dd.mm.yyyy’));
INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(6, 'Азиатские тайны ', ‘Diamond Princess’, to_date(‘15.12.2022’, ‘dd.mm.yyyy’), to_date(‘30.12.2022’, ‘dd.mm.yyyy’));
INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
(7, ‘Полярное сияние’, ‘MS Spitsbergen’, to_date(‘15.01.2023’, ‘dd.mm.yyyy’), to_date(‘30.01.2023’, ‘dd.mm.yyyy’));

INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(1, ‘Барселона’, 1);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(2, ‘Рим’, 1);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(3, ‘Майями’, 2);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(4, ‘Ямайка’, 2);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(5, ‘Осло’, 3);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(6, ‘Сидней’, 4);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(7, ‘Рио-де-Жанейро’, 5);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(8, ‘Буэнос-Айрес’, 5);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(9, ‘Токио’, 6);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(10, ‘Шанхай’, 6);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(11, ‘Сингапур’, 6);
INSERT INTO Ports (p_id, p_name, cr_id) VALUES
(12, ‘Тромсе’, 7);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, ‘Failed to fill tables with data’, TRUE);
END FillData;


PROCEDURE ClearData IS
BEGIN
DELETE FROM Ports;
--Перенос данных перед удалением
INSERT INTO temp_cruises SELECT * FROM Cruises;
DELETE FROM Cruises;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20002, ‘Failed to clear tables’, TRUE);
END ClearData;

PROCEDURE DeleteCruise(p_cr_id IN INT) IS
BEGIN
– Перенос данных перед удалением
INSERT INTO temp_cruises SELECT * FROM Cruises WHERE cr_id = p_cr_id;
DELETE FROM Cruises WHERE cr_id = p_cr_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20003, ‘Failed to delete cruise’, TRUE);
END DeleteCruise;
END CruiseDataPackage;
/

CREATE OR REPLACE TRIGGER prevent_delete_cruise
BEFORE DELETE ON temp_cruises
FOR EACH ROW
DECLARE
cnt_cruises NUMBER;
BEGIN
SELECT COUNT(*) INTO cnt_cruises FROM temp_cruises;
IF cnt_cruises < 3 THEN
RAISE_APPLICATION_ERROR(-20004, ‘Cannot delete cruise. There should be at least three cruises in the table.’, TRUE);
END IF;
END;
/

BEGIN 
CruiseDataPackage.FillData();
CruiseDataPackage.ClearData();
CruiseDataPackage.FillData();
CruiseDataPackage.DeleteCruise(1);
END;
/

Для данной программы написать второй пакет, в состав которого включить вызовы процедур из первого пакета CruiseDataPackage. В пакет также поместить процедуру, которая выбирает указанный в параметре круиз и его порты; выборка должна выполняться в коллекцию. Включить в пакет еще одну процедуру, которая выводит переданную в параметре коллекцию с данными о круизе и портах. Значения изменяемых данных должны передаваться в процедуру как параметры. В процедурах предусмотреть обработку исключений.