ЗаданиеТребуется обрабатывать данные о Банках и Банкоматах. Любой банк может иметь несколько банкоматов. Процедура должна удалять банкомат. Триггер должен запрещать удаления для пользователя, не являющегося владельцем таблицы. Включить в пакет еще одну процедуру, которая выводит количество банкоматов для каждого банка, кроме банка, указанного в параметре. Выборку данных производить в ассоциативный массив.Создать две таблицы, каждая из которых должна иметьпервичныйключи,покрайнеймере,одинстолбецсограничениемNOTNULL.Таблицыдолжныбытьсвязанывнешнимключом;типсвязи-"один-ко-многим".Создатьпакет,содержащийпроцедуруначальногозаполнения таблиц данными (по 7-10 записей в таблице) и процедуру

---

очистки таблиц (удаления записей).

Для одной из таблиц разработать триггер для обеспечения дополнительных ограничений на изменение данных таблицы (см. свой вариант задания).

Создать представление, которое позволяет запрашивать данные из обеих (связанных) таблиц. Представление должно ограничивать доступ к данным по столбцам и строкам.

Написать второй пакет, в состав которого включить вызовы процедур из первого пакета. В пакет также поместить процедуру изменения данных в таблицах (см. свой вариант задания).

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

Обеспечить подтверждение транзакций при их успешном выполнении и откат - в случае возникновения исключительной ситуации.

Предоставить привилегии всем пользователям базы данных Oracle на использование представления для просмотра данных. Предоставить привилегию конкретному пользователю на выполнение процедуры изменения данных.
Код сценария


DROP TABLE BANK;

DROP TABLE BANK_MACHINE;
CREATE TABLE BANK

(

BANK_ID NUMBER(4),

BANK_NAME VARCHAR2(100) NOT NULL,

CONSTRAINT bank_pk PRIMARY KEY (BANK_ID)

);
CREATE TABLE BANK_MACHINE

(

MACHINE_ID NUMBER(4),

BANK_ID NUMBER(4),

MACHINE_NAME VARCHAR2(100) NOT NULL,

CONSTRAINT bank_machine_pk PRIMARY KEY (MACHINE_ID),

CONSTRAINT bank_machine_fk FOREIGN KEY (BANK_ID) REFERENCES BANK(BANK_ID) ON DELETE CASCADE

);
/
DROP SEQUENCE BANK_SEQUENCE;

DROP SEQUENCE BANK_MACHINE_SEQUENCE;
CREATE SEQUENCE BANK_SEQUENCE START WITH 1;

CREATE SEQUENCE BANK_MACHINE_SEQUENCE START WITH 1;
/
CREATE OR REPLACE PACKAGE Pack_1 IS

PROCEDURE Bank_tables_init;

PROCEDURE Bank_tables_delete;

END Pack_1;
/
CREATE OR REPLACE PACKAGE BODY Pack_1 IS

PROCEDURE Bank_tables_init IS

BEGIN

INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Альфа');

INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'ВТБ');

INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Сбер');

INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Тинькофф');

INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Райффейзен');

---

INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Открытие');INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'УРАЛСИБ');INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Левобережный');INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Россельхоз');INSERT INTO BANK VALUES (BANK_SEQUENCE.NEXTVAL, 'Промсвязь');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 1, 'Альфа-банкомат №1');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 3, 'Сбер-банкомат №1');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 4, 'Тинькофф-банкомат');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 7, 'УРАЛСИБ-банкомат');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 10, 'Промвязь-банкомат');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 2, 'ВТБ-банкомат');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 1, 'Альфа-банкомат №2');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 3, 'Сбер-банкомат №2');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 6, 'Открытие-банкомат');INSERT INTO BANK_MACHINE VALUES (BANK_MACHINE_SEQUENCE.NEXTVAL, 5, 'Райффайзен-банкомат');COMMIT;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);ROLLBACK;END;PROCEDURE Bank_tables_delete ISBEGINDELETE FROM BANK;DELETE FROM BANK_MACHINE;COMMIT;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);ROLLBACK;END;END Pack_1;/ CREATE OR REPLACE TRIGGER delete_triggerBEFORE DELETE ON BANK_MACHINE FOR EACH ROWDECLAREowner_of_table VARCHAR2(100);BEGINSELECT OWNER INTO owner_of_table FROM ALL_TABLES WHERE TABLE_NAME = 'BANK_MACHINE';IF user != owner_of_table THENDBMS_OUTPUT.PUT_LINE(user || ' ' || owner_of_table);raise_application_error(-20001, 'WRONG USER');END IF;END;/CREATE OR REPLACE PACKAGE Pack_2 ISPROCEDURE Bank_tables_init_call;PROCEDURE Bank_tables_delete_call;PROCEDURE Bankomat_delete(num IN NUMBER);PROCEDURE Bank_show(name1 IN VARCHAR2);END Pack_2;/CREATE OR REPLACE PACKAGE BODY Pack_2 ISPROCEDURE Bank_tables_init_call ASBEGINPack_1.Bank_tables_init;END Bank_tables_init_call;PROCEDURE Bank_tables_delete_call ASBEGINPack_1.Bank_tables_delete;END Bank_tables_delete_call;PROCEDURE Bankomat_delete(num IN NUMBER) ASBEGINDELETE FROM BANK_MACHINEWHERE MACHINE_ID = num;COMMIT;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);ROLLBACK;END Bankomat_delete;PROCEDURE Bank_show(name1 IN VARCHAR2) ASCURSOR cur(bank_show VARCHAR2) ISSELECT BANK_NAME, count(MACHINE_ID) AS count_bankomat FROM BANK JOIN BANK_MACHINEON BANK.BANK_ID = BANK_MACHINE.BANK_ID AND BANK_NAME <> bank_show GROUP BY BANK_NAME;BEGINFOR item IN cur(name1) LOOPDBMS_OUTPUT.PUT_LINE(item.BANK_NAME || ' ' || item.count_bankomat);END LOOP;COMMIT;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);ROLLBACK;END Bank_show;END Pack_2;/CREATE OR REPLACE VIEW BANK_VIEW ASSELECT BANK_NAME AS Банк, MACHINE_NAME AS Банкомат FROM BANK, BANK_MACHINEWHERE BANK.BANK_ID = BANK_MACHINE.BANK_ID AND BANK_NAME != 'Левобережный';/BEGINPack_2.Bank_tables_delete_call;Pack_2.Bank_tables_init_call;END;/SELECT * FROM BANK;SELECT * FROM BANK_MACHINE;/SELECT * FROM BANK_VIEW;/BEGINPack_2.Bank_show('Сбер');END;/BEGINPack_2.Bankomat_delete(2);END; / SELECT * FROM BANK_MACHINE; / grant select on BANK_VIEW to public; grant execute on WKSP_SBDLABS.Pack_2 to WKSP_REITAR;

---

select * from user_tab_privs; revoke execute on WKSP_SBDLABS.Pack_2 from WKSP_REITAR; revoke select on BANK_VIEW from public; select * from user_tab_privs;Результаты работы сценарияРис.1-2СозданиеизаполнениетаблицРис.3-4Работапроцедуры удаления банкоматовРис.5Работатриггера,которыйнепозволяет удалять другим пользователямРис.6Выводданныхпредставления Рис.7Выдачапривилегий Рис.8 Работа процедуры вывода банка и количества банкоматов

---

Смотрите также файлы

• Зрительный анализатор.docx

• 3. Продуценты.docx

• Изучение нового материала.docx

• Решением методической комиссии по специальности 38. 05. 01 Экономическая безопасность сзиу ранхигс протокол 1 от 26 августа 2019.docx

• Гидросфера.docx