Обзор и администрирование PL/SQL объектов
PL/SQL это язык третьего уровня которые работает внутри БД. Вы может использовать его для получения и обработки данных с использованием SQL вместе с использованием конструкций функуионального программирования такие как IF…THEN…ELSE и циклов такие как FOR или WHILE. Код программы PL/SQL может храниться на пользовательской машине и отсылаться на сервер для выполнения или хранится внутри БД как именованный блок кода.
Exam tip
PL/SQL всегда выполняется внутри БД, вне зависимости от того где он хранится. Java может работать как на пользовательской машине так и на стороне БД.
Хранимый и анонимный код PL/SQL
PL/SQL выполняется на стороне БД но он может храниться как на клиентской стороне так и на стороне сервера. PL/SQL код можно также вводить из командной строки. Хранимый PL/SQL загрудается в базу данных и хранится в словаре данных как именованный объект. Когда он сохраняется в БД – он компилируется: процесс компиляции проверяет синтаксические ошибки и ошибки связанные с объектами данных к которым обращается код. Это помогает экономить время когда код выполняется и программисты должны убрать ошибки в момент компиляции, чтобы пользователи не сталкивались с ними. Код который хранится удалённо или код вводимый в командной строке называется анонимным PL/SQL. Он компилируется динамически что сказывается на производительности и появляется возможность непредвиденных ошибок.
На рисунке 8-5 показаны примеры выполнения анонимного PL/SQL блока и созданиеи выполнение хранимой процедуры.
Анонимный блок создаёт переменную с именем INCREASE с помощью команды DECLARE и устанавливает значение в 10. Затем функциональный код (между BEGIN и END) использует переменную для SQL запроса который обновляет значение столбца в таблице. На втором примере создаётся процедура с именем INC_SAL, хранимая в словаре данных. Она принимает численные аргумент с именем INCREASE и использует его в команде SQL. Затем процедура вызывается с помощью команды EXECUTE и указанием значения для аргумента.
Эти примеры очень простые но они показывают что анонимный блок выполняется единожды и должен быть скомпилирован во время выполнения, а хранимый PL/SQL может быть заранее скомпилирован и затем выполняться много раз.
Объекты PL/SQL
Часто используется пять типов PL/SQL объектов: процедуры, функции, пакеты, тело пакета, триггер.
Все они являются объектами схемы и хранятся в словаре данных. Процедуры и функции это подпрограммы обычно предназначенные для выполнения последовательных инструкций. Пакеты это коллекции процедур и функций собранных вместе для более лёгкой управляемости. Триггеры нельзя поместить в пакет: они ассоциируются с таблицами и выполняются в момент выполнения определённой DML команды к этой таблице.
Процедуры и функции
Процедура это блок кода который выполняет определённые действия. Он может определяться с параметрами (или без параметров). Эти параметры заменяются на значения когда процедура вызывается. Параметры могут быть входными (IN) что обозначает что они используются для передачи данных в процедуру или выходными (OUT) что значит что эти параметры изменяются во время выполнения процедуры и затем новые значения возвращаются процедурой. Параметры также могут быть IN-OUT что значит что они используются и как входные и как выходные одновременно. Внутри процедуры можно определить переменные, которые в отличие от параметров видны только внутри процедуры. Для запуска процедуры можно вызвать её из PL/SQL кода или выполнить команду EXECUTE.
Функции подобны процедурам, но у них не бывает OUT параметов и их нельзя вызывать с помощью команды EXECUTE. Они возвращают одно значение с помощью команды RETURN.
Всё что может сделать функция – может сделать и процедура. Функции обычно используются для относительно простых операций: небольшой участок кода который будет использоваться много раз. Процедуры используются для разделения кода на модули и могут содержать длинную и сложную обработку.
Пакеты
Путём группировки связанных процедур и функций программисты могут создавать пакеты. Пакет состоит из двух объектов: спецификации и тела. Спецификация перечисляет процедуры и функции доступные для вызова вместе с их параметрами и их типами данных. Так же в спецификации можно определить переменные которые будут доступны для всех процедур и функций внутри пакета. Тело пакета содержит PL/SQL код: код который создаёт процедуры и функции. Для создания спецификации пакета используется команда CREATE PACKAGE. Например
Затем для создания тела пакета используется команда CREATE OR REPLACE PACKAGE BODY для создания функций и процедур пакета.
Доступно несколько сотен стандартных пакетов после установки Oracle. Для вызова процедуры из пакета вы должны указать имя процедуры и префикс – имя пакета. Например
exec numbers.odd_even(5);
Такая команда запустить процедуры ODD_EVEN из пакета NUMBERS. Пакет должен существовать в схеме текущего аккаунта или придётся указать явно имя схемы перед именем пакета. Также у пользователя должны быть права EXECUTE для пакета.
Триггеры (triggers)
Триггеры это определённый тип объектов PL/SQL которые нельзя вызвать вручную. Триггеры запускаются (срабатывают) автоматически когда выполняется определённое действие: событие триггеры (triggering event). Доступны разные типы событий и для многих из них можно указать время выполнения триггера: до или после события. Так же возможно определить триггеры для одного события с разным временем срабатывания. Триггеры DML, которые срабатывают когда строки добавляются, изменяются или удаляются можно настроить на запуск для каждой затронутой командой строки, или на запуск для всей команды.
У всех триггеров одно общее свойство: их выполнение никак не контрлируется пользователем кто выполнил событие. Пользователь может даже не знать что выполнялся какой-либо триггер.
В таблице ниже описаны часто используемые события триггеров
Для команды SELECT нет триггера но fine-grained auditing (лабораторная работа №6) может быть использовано для достижения схожего эффекта.
Триггеры часто используются для
Аудита действий пользователей – триггер может записывать все детали кто и что сделал и записывать данные в таблицу аудита
Выполнения сложных изменений – изменения одной строки могут требовать дополнительных действий. Триггер может сделать это автоматически
Безопасности – триггер может проверять например IP-адресс клиента или программу из которой выполняется запрос или любые другие факторы для проверки допустимых для конкретной сессии действий
Проверки сложных ограничений – действия над таблицей должны проверять данные в других таблицах
Exam tip
Невозможно запустить триггер никаким другим образом кроме как выполнив определённое действия приводящее к заданному событию