Основы PL SQL: структура, функции, триггеры, переменные, записи
PL/SQL (Programming Language for SQL) — язык программирования от компании Oracle, предоставляющий средства для сложной обработки данных. В этой статье мы рассмотрим основы PL/SQL.
Начнём с программных единиц.
Программные единицы PL/SQL
К программным единицам в PL/SQL относят: 1. Процедуру — подпрограмму, выполняющую специфическое действие (CREATE PROCEDURE); 2. Функцию — подпрограмму, вычисляющую значение (CREATE FUNCTION). 3. PL/SQL-пакеты — объект БД, группирующий логически связанные типы, подпрограммы и программные объекты PL/SQL. Как правило, пакеты состоят из 2 частей — тела и спецификации. Спецификация представляет собой интерфейс с вашими приложениями (объявляет типы, константы, переменные, исключения, подпрограммы, курсоры — всё, что доступно для использования в пакете). Вторая часть — это тело пакета, полностью определяющая подпрограммы и курсоры, реализуя тем самым спецификацию пакета. 4. Динамический SQL: a. Native Dynamic SQL (NDS); b. DBMS_SQL. 5. Опции компилятора. 6. Триггеры — хранимые процедуры особого типа, которые юзер не вызывает непосредственно, т. к. их исполнение обусловлено действиями по модификации данных. Триггеры бывают разные: BEFORE INSERT, BEFORE UPDATE, AFTER INSERT и пр. 7. Управление зависимостями. 8. Хинты либо подсказки (Oracle Hints) — средства, которые позволяют оказывать явное влияние на план запроса. Подсказки определяют общие подходы и цели, оптимизирующие план исполнения запроса, в том числе методы и правила доступа к данным (например, указание метода и порядка соединения таблиц, индекса для доступа к таблице).
Блочная структура PL/SQL
В блоке PL/SQL может содержаться до 4-х разделов, но лишь один считается обязательным. 1. Заголовок. Применяется лишь в именованных блоках, служит для определения способа вызова программы либо именованного блока. 2. Раздел объявлений. Включает описания переменных, вложенных блоков и курсоров. 3. Раздел исключений. Служит для обработки исключений (предупреждений и ошибок). 4. Исполняемый раздел. Речь идёт о командах, которые выполняются ядром PL/SQL в процессе работы приложения. Обратите внимание, что это ОБЯЗАТЕЛЬНЫЙ раздел.
Давайте посмотрим на структуру PL/SQL-блока для процедуры:
Следующее изображение — это пример процедуры, содержащей все 4 раздела:
Хранимая процедура
Под хранимой процедурой понимают некоторый перечень инструкций, которые написаны на PL/SQL. Вызов такой процедуры обеспечивает выполнение содержащихся в этой процедуре инструкций. Сама она хранится в БД, поэтому и считается хранимой. Состоит из тела и спецификации.
Спецификация содержит имя процедуры, а также описание её выходных и входных данных, которые являются формальными параметрами либо формальными аргументами. Когда при вызове процедуры указывают параметры командной строки либо иные входные данные, указанные значения называют фактическими параметрами/фактическими аргументами.
Рассмотрим пример спецификации:
increase_salary_find_tax ( increase_percent IN NUMBER := 7, sal IN OUT NUMBER, tax OUT NUMBER )
В примере выше перед нами процедура с 3-мя формальными параметрами. IN после имени параметра значит, что в процессе вызова процедура может из данного параметра считать входное значение. OUT значит, что процедура может применять этот параметр в целях возврата значения в ту программу, из которой она вызывалась. Комбинация IN OUT значит, что параметр может использоваться в качестве передачи значения процедуре, а также в целях возврата значения.
Что касается тела процедуры, то это блок кода PL/SQL.
Хранимые функции
Функция PL/SQL напоминает процедуру PL/SQL: у неё тоже есть тело и спецификация. Основное различие — функция нужна для возврата значения, которое можно применять в более крупном операторе SQL.
Триггеры
Триггером называют процедуру PL/SQL, выполняемую автоматически, когда происходит некоторое событие, которое называется триггерным событием.
К примеру, мы можем писать триггеры, которые срабатывают в процессе выполнения над таблицей операций UPDATE, INSERT либо DELETE; во время выдачи команд DDL; во время входа пользователя в систему либо его выхода; во время запуска либо остановки БД; во время возникновения ошибок.
Существуют 3 различия между процедурами PL/SQL и триггерами: 1. Триггеры невозможно вызвать из кода программы. Они вызываются автоматически как ответ на некоторое событие. 2. У триггеров нет списка параметров. 3. Спецификации триггера и процедуры немного отличаются.
Переменные в PL/SQL
Переменными называют именованные контейнеры. Они способны содержать информацию разных видов. С учётом помещаемой информации данных они имеют разные типы данных, а чтобы отличать эти данные друг от друга, присваиваются имена. К примеру, числа в PL/SQL хранятся в переменных типа NUMBER, текст — в переменных CHAR либо VARCHAR2. Что касается синтаксиса объявления переменной, то в PL/SQL он имеет любую из нижеперечисленных форм записи:
имя_переменной тип_данных [[NOTNULL] := выражение_по_умолчанию]; имя_переменной тип_данных [[NOT NULL] DEFAULT выражение_по_умолчанию];
Здесь имя_переменной — любой правильный PL/SQL идентификатор. Такой идентификатор должен: 1. Иметь в длину не больше 30 символов, причём не иметь в записи пробелов и знаков табуляции. 2. Начинаться с буквы. 3. Состоять лишь из букв, цифр 0-9, символа подчеркивания, знака доллара и знака фунта. 4. Не совпадать с зарезервированными словами SQL и PL/SQL, имеющими спецзначение. Пример неправильной записи для переменной — слово BEGIN, обозначающее начало выполняемой секции основного PL/SQL-блока.
Следующее, это тип_данных — любой допустимый тип данных в SQL либо PL/SQL. Здесь обратите внимание, что модификатору NOT NULL требуется, чтобы переменная имела значение. И если модификатор указан, то переменной должно присвоиться значение по умолчанию.
Объявляем константы PL/SQL
Запись объявления константы выглядит следующим образом:
имя_переменной тип_данных CONSTANT := выражение;
В отличие, скажем, от тех же переменных, константам обязательно присваивается значение, причём это значение нельзя будет поменять в течение всего срока жизни константы. При этом константы весьма полезны при поддержании безопасности и дисциплины во время разработки приложений повышенной сложности. К примеру, если вы желаете гарантировать, что PL/SQL-процедура не будет модифицировать данные, передаваемые ей, то вы можете объявить их константами. А если процедура всё-таки попытается модифицировать эти данные, PL/SQL возбудит исключение.
Записи в PL/SQL
Говоря о записи в PL/SQL, мы говорим о наборе данных базовых типов. К записи мы можем обращаться, как к единому целому. При этом для доступа к отдельным полям записи используют нотацию
Record PL/SQL
Давайте посмотрим, как применять спецификатор записи Record в PL/SQL с синтаксисом.
Record (запись) — это группа связанных элементов данных, которые хранятся в полях, причём каждая имеет своё имя и тип данных. При этом мы можем применять Record как переменную, способную содержать строку таблицы либо некоторые столбцы из строки таблицы.
Определяем и объявляем Record в PL/SQL
Объявить запись поможет атрибут %ROWTYPE, представляющий строку в таблице БД без непосредственного перечисления всех столбцов. При этом ваш код будет продолжать функционировать даже после добавления в таблицу столбцов. Но если вы желаете представить подмножество столбцов в таблице либо столбцы из различных таблиц, можно определить VIEW либо объявить CURSOR в целях выбора правильных столбцов и любых нужных объединений, а потом задействовать атрибут %ROWTYPE к CURSOR либо VIEW.
Посмотрим на синтаксис объявления переменной в PL/SQL и определения типа Record:
TYPE type_rec_name IS Record (field_1 datatype, field_2 datatype, … field_n datatype); var_rec type_rec_name;
Мы видим следующие параметры: 1) type_rec_name – имя конкретного типа Record; 2) var_rec – имя переменной Record-типа; 3) field_1, field_2,… field_n – поля Record-типа; 4) datatype – тип данных для полей Record-типа. Может быть любой из списка:
При подготовке статьи использовались следующие материалы: 1. «PL SQL — функции, процедуры, переменные, курсоры и циклы plsql». 2. «Record Oracle PL/SQL».
