Задание


Требуется обрабатывать данные о Банках и Банкоматах. Любой банк может иметь несколько банкоматов. Процедура должна удалять банкомат. Триггер должен запрещать удаления для пользователя, не являющегося владельцем таблицы. Включить в пакет еще одну процедуру, которая выводит количество банкоматов для каждого банка, кроме банка, указанного в параметре. Выборку данных производить в ассоциативный массив.
Создать две таблицы, каждая из которых должна иметь первичный ключ и, по крайней мере, один столбец с ограничением

NOT NULL. Таблицы должны быть связаны внешним ключом; тип связи

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

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

ROLLBACK;

END;
PROCEDURE Bank_tables_delete IS

BEGIN

DELETE FROM BANK;

DELETE FROM BANK_MACHINE;

COMMIT;
EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

ROLLBACK;

END;
END Pack_1;
/
CREATE OR REPLACE TRIGGER delete_trigger

BEFORE DELETE ON BANK_MACHINE FOR EACH ROW

DECLARE

owner_of_table VARCHAR2(100);

BEGIN

SELECT OWNER INTO owner_of_table FROM ALL_TABLES WHERE TABLE_NAME = 'BANK_MACHINE';

IF user != owner_of_table THEN

DBMS_OUTPUT.PUT_LINE(user || ' ' || owner_of_table);

raise_application_error(-20001, 'WRONG USER');

END IF;

END;
/
CREATE OR REPLACE PACKAGE Pack_2 IS

PROCEDURE 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 IS

PROCEDURE Bank_tables_init_call AS

BEGIN

Pack_1.Bank_tables_init;

END Bank_tables_init_call;
PROCEDURE Bank_tables_delete_call AS

BEGIN

Pack_1.Bank_tables_delete;

END Bank_tables_delete_call;
PROCEDURE Bankomat_delete(num IN NUMBER) AS

BEGIN

DELETE FROM BANK_MACHINE

WHERE MACHINE_ID = num;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

ROLLBACK;

END Bankomat_delete;
PROCEDURE Bank_show(name1 IN VARCHAR2) AS

CURSOR cur(bank_show VARCHAR2) IS

SELECT BANK_NAME, count(MACHINE_ID) AS count_bankomat FROM BANK JOIN BANK_MACHINE

ON BANK.BANK_ID = BANK_MACHINE.BANK_ID AND BANK_NAME <> bank_show GROUP BY BANK_NAME;

BEGIN

FOR item IN cur(name1) LOOP

DBMS_OUTPUT.PUT_LINE(item.BANK_NAME || ' ' || item.count_bankomat);

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

ROLLBACK;

END Bank_show;
END Pack_2;
/
CREATE OR REPLACE VIEW BANK_VIEW AS

SELECT BANK_NAME AS Банк, MACHINE_NAME AS Банкомат FROM BANK, BANK_MACHINE

WHERE BANK.BANK_ID = BANK_MACHINE.BANK_ID AND BANK_NAME != 'Левобережный';
/
BEGIN

Pack_2.Bank_tables_delete_call;

Pack_2.Bank_tables_init_call;

END;
/
SELECT * FROM BANK;

SELECT * FROM BANK_MACHINE;
/
SELECT * FROM BANK_VIEW;
/
BEGIN

Pack_2.Bank_show('Сбер');

END;
/
BEGIN

Pack_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