PL/SQL: коллекции (массивы переменной длины, вложенные таблицы, ассоциативные массивы), назначение, принципы работы с коллекциями, различия, общие методы и встроенные исключения. Операторы MULTISET.

PL/SQL-коллекции:

· массивы переменной длинны – объект содержащий объекты одного типа (размер ограничен сверху, т.е. нужно его указать заранее)

· вложенные таблицы – таблицы, содержащие в себе строки, являющиеся объектами (в отличие от массивов переменной длинны размер не ограничен сверху)

· ассоциативные массивы – индексированные таблицы (массивы, доступ к которым осуществляется не по индексу, а по ключу)

оператор над множествами MULTISET EXCEPT для извлечения из вложенной таблицы happyfamily родителей и поместим их имена во вложенную таблицу parents. Выведем содержимое таблицы parents.

PL/SQL: динамический SQL, оператор EXECUTE IMMEDIATE, опции USING (входные и выходные параметры), опция RETURNING.

Динамический sql – операторы sql, которые формируются и исполняются во время выполнения программы. Применяется для понимания кода, адаптирующегося к структуре таблиц.

Предложение EXECUTE IMMEDIATE подготавливает (делает синтаксический разбор) и сразу же выполняет динамическое SQL-предложение или анонимный PL/SQL-блок.

Основные преимущества динамического SQL:

- можно выполнять в PL/SQL-блоках операторы DDL;

- можно писать программы, адаптирующиеся к структуре таблиц;

 

29. Хранимые процедуры и функции: назначение, принцип применения, необходимые привилегии, операторы создания, опция AUTHID CURRENT_USER/DEFINER, объектная привилегия EXECUTE, представления словаря.

Хранимые процедуры - (именованный PL/SQL-блок) – группа связанных между собой операторов sql или функций и сохраняемой в откомпилированном виде.

EXECUTE -Отменяется право запускать хранимую процедуру, пользовательскую функцию или пакет.

AUTHID — как будет выполняться программа и как должны разрешаться ссылки на объекты: DEFINER — в соответствии с привилегиями владельца объекта, либо CURRENT_USER — в соответствии с привилегиями пользователя вызывающего программу. По умолчанию считается DEFINER.

USER_PROCEDURES, USER_SOURCE

grant create procedure to kvcore

 

30. Пакеты: назначение, принцип применения, необходимые привилегии, операторы создания, опция AUTHID CURRENT_USER/DEFINER, объектная привилегия EXECUTE, представления словаря.

Пакет – совокупность некоторых функций и процедур, объявленных под общим именем.

EXECUTE -Отменяется право запускать хранимую процедуру, пользовательскую функцию или пакет.

AUTHID — как будет выполняться программа и как должны разрешаться ссылки на объекты: DEFINER — в соответствии с привилегиями владельца объекта, либо CURRENT_USER — в соответствии с привилегиями пользователя вызывающего программу. По умолчанию считается DEFINER.

USER_PROCEDURES, USER_SOURCE

grant create procedure to kvcore

 

Триггеры: DLL/DML – триггеры, BEFORE/AFTER-триггеры, триггеры уровней строки и оператора, необходимые привилегии, операторы создания, предикаты INSERTING, UPDATATING, DELETING, INSTEAD OF-триггеры, NEW/OLD-префиксы, системные (DDL) триггеры.

Триггер базы данных: особый вид именованных блоков PL/QL (особый вид процедур) которые срабатывают (firing – стрелять, запускать) по запускающему их событию.

Триггер базы данных: выполняется под правами создателя триггера.

Запускающие события: DML – события, системные события.

Применение триггеров:

для реализации сложных ограничений целостности базы данных;

для аудита (контроля хранимой и изменяемой информации – дельного аудита);

для автоматического оповещения программ о произошедших событиях;

Типы триггеров: DML – триггеры, триггеры замещения instead-of), системные триггеры.

Привилегии на триггер: назначаются напрямую USER у, а не через роль.

CREATE TRIGGER (создавать, удалять изменять в своей подсхеме);

CREATE ANY TRIGGER (создать любой триггер в любой схеме, кроме SYS, не рекомендуется для словаря, не разрешает менять текст триггера);

ALTER ANY TRIGGER (разрешать, запрещать, изменять, компилировать, любые, кроме SYS-триггеров, триггеры);

DROP ANY TRIGGER (удалять любой триггер, кроме SYS-триггеров);

ADMINISTER DATABASE TRIGGER (создавать, изменять, удалять системные триггеры, должен иметь привилегию CREATE TRIGGER или CREATE ANY TRIGGER).

DML-события: оператор: INSERT, DELETE, UPDAТE.

DML-триггеры: только для таблиц, для представлений нельзя

DML-триггеры: момент времени: AFTER (после события), BEFFORE (до события). BEFORE – до записи в журнал, AFTER – после записи в журнал.

DML-триггеры: уровень: FOR EACH ROW (для каждой строки), ПО УМОЛЧАНИЮ (операторный уровень).

DML-триггеры: всего типов триггеров = 28 = (7 комбинаций операторов)× 2 момента × 2 уровня.

DML-триггеры: для таблицы может быть любое количество.

DML-триггер – это часть транзакции: не может выдавать COMMIT/ROLBACK (может только, если в теле триггера автономная транзакция),может выдавать RAISE_SPPLICATION_ERROR.

Системные триггеры: BEFORE, AFTER (момент выполнения)

Системные триггеры: DATABASE, SCHEMA (уровень триггера)

Системные триггеры: 1)серверные события; 2) DDL-события; 3)события сбора статистики; 4) события аудита; 5) DCL-события.

Системные триггеры: все кроме LOGOFF работают в режиме автофиксации; LOGOFF входит в транзакцию отключения.

Системный триггер: может генерировать исключение RAISE.