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. В пакет также поместить процедуру, которая выбирает указанный в параметре круиз и его порты; выборка должна выполняться в коллекцию. Включить в пакет еще одну процедуру, которая выводит переданную в параметре коллекцию с данными о круизе и портах. Значения изменяемых данных должны передаваться в процедуру как параметры. В процедурах предусмотреть обработку исключений.