DROP TABLE rgr_сruises;
DROP TABLE rgr_ports;

drop sequence rgr_seq_p;
drop sequence rgr_seq_c;

create sequence rgr_seq_p start with 1 increment by 1;
create sequence rgr_seq_c start with 1 increment by 1;

CREATE TABLE rgr_сruises
(
    cr_id INTEGER NOT NULL, 
    c_name VARCHAR2(50) NOT NULL, 
    c_ship VARCHAR2(20) NOT NULL,
    c_departure_date DATE NOT NULL, 
    c_return_date DATE NOT NULL,
    --p_id INTEGER NOT NULL,
    --dest_port_id INTEGER NOT NULL

    CONSTRAINT rgr_cr_id_pk PRIMARY KEY(cr_id)
);

CREATE TABLE rgr_ports
 (
    p_id INTEGER NOT NULL,
    p_name VARCHAR2(20) NOT NULL,
    p_country VARCHAR2(20) NOT NULL,
    p_capacity INTEGER NOT NULL,
    p_dest INTEGER NOT NULL,

    CONSTRAINT rgr_p_id_pk PRIMARY KEY(p_id),
    CONSTRAINT rgr_p_dest_fk FOREIGN KEY(p_dest) REFERENCES rgr_сruises(cr_id) ON DELETE CASCADE
);


CREATE OR REPLACE PACKAGE dataPack AS
    PROCEDURE filling;
    PROCEDURE cleaning;
END dataPack;
/

CREATE OR REPLACE PACKAGE BODY dataPack AS
    PROCEDURE filling IS
    BEGIN
        dataPack.cleaning;
        INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Средиземноморские жемчужины', 'Costa Diadema', to_date('15.06.2022', 'dd.mm.yyyy'), to_date('30.06.2022', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Барселона', 'Испания', '20000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Рим', 'Италия', '40000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Неаполь', 'Италия', '30000', rgr_seq_c.CURRVAL);

        INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Карибские приключения', 'Harmony of the Seas', to_date('01.06.2023', 'dd.mm.yyyy'), to_date('10.06.2023', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Майями', 'США', '20000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Ямайка', 'Ямайка', '10000', rgr_seq_c.CURRVAL);

        INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Северное сияние', 'Hurtigruten MS Roald Amundsen', to_date('20.09.2023', 'dd.mm.yyyy'), to_date('05.10.2023', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Осло', 'Норвегия', '20000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Тромсе', 'Норвегия', '15000', rgr_seq_c.CURRVAL);

        INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Австралийские просторы', 'Pacific Explorer', to_date('10.10.2023', 'dd.mm.yyyy'), to_date('25.10.2023', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Сидней', 'Австралия', '50000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Мельбурн', 'Австралия', '15000', rgr_seq_c.CURRVAL)

        INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Южная Америка', 'Empress of the Seas', to_date('01.02.2024', 'dd.mm.yyyy'), to_date('15.02.2024', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Рио-де-Жанейро', 'Бразилия', '40000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Буэнос-Айрес', 'Аргентина', '25000', rgr_seq_c.CURRVAL)

        INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Азиатские тайны', 'Diamond Princess', to_date('10.05.2024', 'dd.mm.yyyy'), to_date('25.05.2024', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Токио', 'Япония', '25000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Шанхай', 'Китай', '40000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Сингапур', 'Сингапур', '30000', rgr_seq_c.CURRVAL);

       INSERT INTO rgr_сruises
         VALUES(rgr_seq_c.NEXTVAL, 'Полярное сияние', 'MS Spitsbergen', to_date('15.08.2024', 'dd.mm.yyyy'), to_date('30.08.2024', 'dd.mm.yyyy'));
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Лонгйир', 'Норвегия', '25000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Свальбард', 'Норвегия', '40000', rgr_seq_c.CURRVAL);
        INSERT INTO rgr_ports VALUES(rgr_seq_p.NEXTVAL, 'Хоннинсгвог', 'Норвегия', '30000', rgr_seq_c.CURRVAL);

    EXCEPTION
     WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'Не удалось заполнить таблицы данными', TRUE);
    END filling;
    
    PROCEDURE cleaning IS
    BEGIN
        -- DELETE FROM rgr_ports;
        -- DELETE FROM rgr_ports;

        EXECUTE IMMEDIATE 'TRUNCATE TABLE rgr_ports';
        EXECUTE IMMEDIATE 'TRUNCATE TABLE rgr_cruises';
        EXECUTE IMMEDIATE 'DROP SEQUENCE rgr_seq_p';
        EXECUTE IMMEDIATE 'DROP SEQUENCE rgr_seq_c';
        EXECUTE IMMEDIATE 'CREATE SEQUENCE rgr_seq_p';
        EXECUTE IMMEDIATE 'CREATE SEQUENCE rgr_seq_c';
    EXCEPTION
     WHEN OTHERS THEN
            ROLLBACK;
            RAISE_APPLICATION_ERROR(-20002, 'Не удалось очистить таблицы', TRUE);
    END cleaning;
END dataPack;
/


CREATE OR REPLACE PACKAGE workPack AS
    TYPE T_VALIDITY_RECORD IS RECORD (
        cr_id INTEGER,
        c_name VARCHAR2(50),
        c_ship VARCHAR2(20),
        c_departure_date DATE, 
        c_return_date DATE,
    );
    TYPE T_VALIDITY_TABLE IS TABLE OF T_VALIDITY_RECORD;
    PROCEDURE filling_data;
    PROCEDURE clearing_data;
    PROCEDURE delete_cruise(del_id IN INTEGER);
    --PROCEDURE participants_with_min_conference;
END;
/

CREATE OR REPLACE PACKAGE BODY workPack AS

    PROCEDURE filling_data IS
    BEGIN
        dataPack.filling;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE_APPLICATION_ERROR(-20005, 'Ошибка заполнения', TRUE);
    END filling_data;

    PROCEDURE clearing_data IS
    BEGIN
        dataPack.cleaning;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE_APPLICATION_ERROR(-20005,'Ошибка очистки', TRUE);
    END clearing_data;

    PROCEDURE delete_cruise(del_id IN INTEGER) IS
    BEGIN
        DELETE FROM rgr_cruises
         WHERE rgr_cruises
        .cr_id = del_id;
        -- DELETE FROM rgr_ports WHERE rgr_ports.c_speaker = del_id;
        -- DELETE FROM rgr_ports
         WHERE rgr_cruises
        .cr_id = del_id;
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20404, 'Данные о круизе с id #' || del_id || ' не найдены в таблице "rgr_ports"', TRUE);
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20005, 'Ошибка при удалении круиза #' || del_id, TRUE);

    END delete_cruise;
END workPack;
/


CREATE OR REPLACE VIEW view_table AS
    SELECT 
        rgr_ports.p_id, rgr_ports.p_name AS Название_порта,
        rgr_ports.p_country AS Страна,
        rgr_ports.p_capacity AS Вместительность,
        rgr_ports.p_dest AS Порт назначения,
        rgr_cruises.c_name AS Название круиза,
        rgr_cruises.c_ship AS Корабль
    FROM rgr_ports, rgr_cruises

    WHERE rgr_ports.p_dest = rgr_cruises.cr_id  
    ORDER BY rgr_ports.p_id ASC;

-- Права
GRANT SELECT ON view_table TO public;
GRANT EXECUTE ON workPack TO WKSP_MAX_KUT898;
SELECT * FROM USER_TAB_PRIVS;

-- Заполение данными
BEGIN
    workPack.filling_data;
    workPack.clearing_data;
    workPack.clearing_data;
    workPack.filling_data;
    workPack.filling_data;
    
END;
/ Error at line 8: PLS-00103: Encountered the symbol ")" when expecting one of the following: <an identifier> <a double-quoted delimited-identifier> current delete exists prior Как исправить данную ошибку?