Для меня PL/SQL есть логичное продолжение изучения SQL, но по нему много и статей, и курсов, причем даже на русском языке. С другой стороны, PL/SQL раскрывается достаточно редко, особенно среди бесплатных ресурсов, так что… Начинаем:)
Блоки процедур
В языке программирования PL/SQL существует несколько блоков, а именно: DECLARE, BEGIN и EXCEPTION. В этой статье мы разберем, как правильно объявлять и использовать переменные в каждом из этих блоков.
Блок DECLARE
В блоке DECLARE происходит объявление переменных, которые будут использоваться в теле процедуры. Пример объявления переменных:
DECLARE
var_1 NUMERIC;
var_2 VARCHAR2(64);
var_3 DATE;Кроме того, в PL/SQL можно использовать якорные типы данных. Вместо того чтобы вручную задавать тип данных переменной, можно привязать её к типу данных конкретного столбца в таблице. Это позволит избежать ошибок при изменении типа данных столбца в будущем.
DECLARE
var_1 table_name.column_name%TYPE;
var_2 table_name2.column_name2%TYPE;Это полезно, если изменится тип данных столбца в таблице. Или, что чаще, удобнее прописать TYPE, чем смотреть в модель данных и искать, какое же указан тип данных для необходимых полей.
%ROWTYPE
Атрибут %ROWTYPE автоматически наследует структуру всех столбцов таблицы или курсора (не будет рассматриваться в этой статье), обеспечивая удобство работы с несколькими значениями одновременно. Кстати, сие называется “Record” (запись).
При обращении к полям записи используется тот же принцип, что и при обращении к таблицам внутри схем (scheme.table_name): RECORD.field_n.
______________________________________________________________________________
Рассмотрим на примере:
О пакете DBMS_OUTPUT
Хотя сначала стоит сказать, что же такое пакет.
Пакет – объединение нескольких процедур/функций в одно целое;
Обычно пакеты состоят из двух компонентов: спецификации (интерфейс) и тела (реализации интерфейса).
Спецификация пакета – список доступных процедур и функций (вместе со всеми переменными, определениями типов и курсорами).
Спецификация пакета содержит информацию о пакете, кроме того, в ней перечисляются все имеющиеся в пакете процедуры и функции (о разнице поговорим в другой раз). Обычно спецификация не содержит кода, код помещается в теле пакета. Процедуры и функции, перечисленные в спецификации доступны для просмотра, а реализация скрыта.
Итак, DBMS_OUTPUT...
Начнем с названия:
DBMS – Database Management System (оно же СУБД);
OUTPUT – в переводе на русский "вывод, результат".
Пакет чаще всего используется для отладки кода (дебаггинга) или для отображения некоторых сообщений и отчетов.
Наиболее часто используемые процедуры: PUT и PUT_LINE.
DBMS_OUTPUT.put('string') -- добавляет строку в буфер (без перевода строки)
DBMS_OUTPUT.put_line('string') -- добавляет строку в буфер (с переводом строки)Для показа данных сообщений иногда требуется прописать строки:
-- Перед кодом
SET serveroutput ON
-- Внутри блока BEGIN
DBMS_OUTPUT.enable() -- обратная команда: DBMS_OUTPUT.disable()В некоторых IDE по умолчанию не выводятся сообщения из DBMS_OUTPUT (SQL Developer, например. А вот PL/SQL Developer уже выводит и без просьб:), потому нужна строка с serveroutput.
С другой стороны, буфер с выводом данных может быть в целом неактивен, тогда пишем DBMS_OUTPUT.enable(n), где n есть число байт, которое будет хранить буфер.
Изложена лишь малая часть, которая пригодится в рамках текущей статьи. Если заинтересовались и хотите погрузиться глубже – добро пожаловать в документацию, которая, к слову, хорошо написана!)
DECLARE
employee_record employees%ROWTYPE;
BEGIN
SELECT *
INTO employee_record
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('ID: ' || employee_record.employee_id ||
', Name: ' || employee_record.first_name ||
' ' || employee_record.last_name);
END;Следует учитывать, что либо записывается все (*) в Record, либо записываются конкретные столбцы в указанные явно Fields:
– – – – – – – – – – – – – – ERROR – – – – – – – – – – – – – – –
DECLARE
employee_record employees%ROWTYPE;
BEGIN
SELECT employee_id
INTO employee_record
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('ID: ' || employee_record.employee_id);
END;
– – – – – – – – – – – – – – ERROR – – – – – – – – – – – – – – –– – – – – – – – – – – – – – RIGHT – – – – – – – – – – – – – – –
DECLARE
employee_record employees%ROWTYPE;
BEGIN
SELECT employee_id
INTO employee_record.employee_id
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('ID: ' || employee_record.employee_id);
END;
– – – – – – – – – – – – – – RIGHT – – – – – – – – – – – – – – –Удобства:
Динамичность (при изменении типа данных атрибутов %ROWTYPE автоматически это перенимает);
Лаконичность (не 10 переменных для 10 атрибутов, а 1).
Ограничения:
Предполагается использовать малую часть атрибутов таблицы – %ROWTYPE будет использовать больше памяти, чем необходимо;
Таблица будет часто обновляться новыми столбцами – %ROWTYPE их включит, а использоваться они, вероятно, не будут.
Как и с отдельной переменной, можно задавать поля в записи вручную, тем самым указывая их количество, тип данных. В таком случае синтаксис следующий:
TYPE type_rec_name IS RECORD (field_1 datatype,
field_2 datatype,
...
field_n datatype);
var_rec type_rec_name;Пример:
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id NUMBER(4) NOT NULL := 10,
dept_name VARCHAR2(30) NOT NULL := 'Administration',
mgr_id NUMBER(6) := 200,
loc_id NUMBER(4) := 1700
);
dept_rec DeptRecTyp;
BEGIN
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.dept_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.mgr_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.loc_id);
END;Пример взят отсюда. Там же можно найти иные примеры, а также некоторую дополнительную информацию.
______________________________________________________________________________
Блок основного кода (BEGIN)
В блоке BEGIN размещается основной код, который будет выполняться. Это может быть один или несколько SQL-запросов:
BEGIN
-- Здесь размещается основной код
END;Рассматриваться подробнее будет в примерах ниже и в других статьях. Кратко: здесь могут применяться все те же SQL запросы, за исключением некоторых оговорок. Например, необходимо куда-то вкладывать выдаваемое запросом значение.
______________________________________________________________________________
Блок обработки ошибок (EXCEPTION)
В случае возникновения ошибок в блоке BEGIN, управление передается в блок EXCEPTION, где можно обработать исключения.
BEGIN
-- Основной код
EXCEPTION
WHEN others THEN
-- Обработка ошибок
END;Когда возникает ошибка, выполнение основного блока прерывается, и управление передаётся в блок EXCEPTION. Это позволяет предотвратить некорректное завершение программы и обработать ситуацию более осмысленно.
Стандартные исключения
PL/SQL имеет встроенные обработчики для стандартных ошибок:
NO_DATA_FOUND: Возникает, если SQL-запрос не возвращает строк, а результат ожидается через SELECT INTO.
TOO_MANY_ROWS: Выбрасывается, если SELECT INTO возвращает больше одной строки.
ZERO_DIVIDE: Ошибка деления на ноль.
INVALID_CURSOR: Использование неинициализированного или закрытого курсора.
VALUE_ERROR: Проблема с диапазоном или типом данных.
INVALID_NUMBER: Преобразование строки в число завершилось ошибкой.
______________________________________________________________________________
Пример обработки ошибки:
DECLARE
tmp_var NUMBER;
BEGIN
SELECT 100 / 0
INTO tmp_var
FROM dual;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE = ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM = ' || SQLERRM);
END;
SQLCODE возвращает числовой код ошибки.
SQLERRM возвращает текстовое описание ошибки.
______________________________________________________________________________
Пользовательские исключения
Можно определять свои исключения и выбрасывать их с помощью RAISE:
DECLARE
e_custom_exception EXCEPTION;
num NUMBER := 5;
BEGIN
IF num < 10 THEN
RAISE e_custom_exception;
END IF;
EXCEPTION
WHEN e_custom_exception THEN
DBMS_OUTPUT.PUT_LINE('Произошла ошибка: значение меньше 10!');
END;______________________________________________________________________________
Универсальный обработчик WHEN OTHERS
Обработчик WHEN OTHERS перехватывает все исключения, которые не были обработаны целенаправленно (примеры выше):
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Произошла ошибка: ' || SQLERRM);
END;Рекомендация: использовать WHEN OTHERS только с логированием ошибок для анализа и избегать скрытия критических проблем.
______________________________________________________________________________
Переменные в PL/SQL
Переменные в PL/SQL создаются и функционируют внутри определенного блока. Для того чтобы присвоить значение переменной, можно использовать два способа:
Присвоение значения "в лоб":
DECLARE
some_var VARCHAR2(32);
BEGIN
some_var := 'some_text';
END;Получение значения через SQL-запрос:
DECLARE
some_var1 VARCHAR2(32);
some_var2 VARCHAR2(32);
BEGIN
SELECT some_col1, some_col2
INTO some_var1, some_var2
FROM some_tab
WHERE some_col3 = some_filter;
END;В этом примере значения из столбцов some_col1 и some_col2 будут присвоены переменным some_var1 и some_var2 соответственно.
Константы в PL/SQL
Для создания константы, значение которой не будет изменяться в процессе выполнения программы, в блоке DECLARE следует использовать ключевое слово CONSTANT:
DECLARE
var_name CONSTANT VARCHAR2(32) := 'Some constant value';
BEGIN
DBMS_OUTPUT.PUT_LINE(var_name);
END;Общий синтаксис для объявления переменных в PL/SQL следующий:
variable_name [CONSTANT] data_type [NOT NULL] [:= | DEFAULT initial_value]CONSTANT гарантирует, что значение переменной не изменится.
NOT NULL позволяет устанавливать ограничение на переменные, что важно, если при присвоении значения переменной может быть получен NULL.
DEFAULT используется для задания начального значения переменной.
То есть константа есть частный случай переменной.
______________________________________________________________________________
Подстановочные переменные (bind/host переменные)
Подстановочные переменные (или bind-переменные, VAR) инициализируются (получают значение) в блоке PL/SQL (либо ручной ввод с клавиатуры), но могут быть созданы и вне PL/SQL. Они могут быть использованы для передачи значений в SQL-запросы.
DECLARE
var_name VARCHAR2(32);
var_name2 VARCHAR2(32);
tmp NUMBER;
BEGIN
:var_name := 'Some value';
SELECT id
INTO tmp
FROM tablename
WHERE col_name = :var_name; -- Попробуйте и :var_name2, наглядно увидите разницу
END;Для вывода значений этих переменных можно использовать три метода:
DBMS_OUTPUT.PUT_LINE();
PRINT var_name;
SET autoprint ON; — автоматический вывод значения переменной при вызове.
А также:
VAR[IABLE] без создания переменной покажет список всех bind-переменных с их типами данных;
PRINT без имени переменной покажет список наименований переменных вместе с их значениями.
Скрин из IDE Присваивать значение переменной не обязательно – Oracle, если не найдет что подставить вместо :var_name, выведет окошко с предложением ввести подстановочный текст.
Также важно помнить, что подстановочные переменные не могут быть использованы для имен объектов базы данных (таких как таблицы, поля и представления), так как они подставляются на этапе подготовки (parse) запроса.
_____________________________________________________________________________
Замещающие переменные
Замещающие переменные — это переменные, которые объявляются и инициализируются символьным значением. Все переменные с префиксом & будут замещены значением на этапе подготовки SQL-запроса в клиенте.
Пример использования замещающей переменной:
DEFINE var_name = 'value';
SELECT * FROM table_
WHERE column_ = '&var_name';
Особенности замещающих переменных:
DEFINE — выводит значение и тип данных переменной.
Можно объявлять и использовать вне PL/SQL;
Присваивать значение переменной не обязательно, если оно не найдено, Oracle предложит ввести значение вручную.
Из IDE Можно создавать неограниченное количество таких переменных.
DEFINE var_name = 'value';
DEFINE var_name2 = 'value2';
DEFINE var_name3 = 'value3';
...______________________________________________________________________________
Сравнение типов переменных
Вот основные различия между переменными в PL/SQL:
Тип переменной | Область видимости | Типы данных | Использование |
DECLARE | Локальная для блока PL/SQL | Любые типы данных | Внутри блоков PL/SQL для временных вычислений |
DEFINE | Глобальная для сессии | Только строки | Для подстановки значений в запросы |
BIND | Глобальная для сессии | Сложные типы данных | Для работы с результатами PL/SQL и сложными данными (например, курсоры) |
Вывод данных с помощью DBMS_OUTPUT
Для вывода данных из переменных в PL/SQL можно использовать пакет DBMS_OUTPUT. Пример:
DECLARE
some_var VARCHAR2(32);
BEGIN
SELECT some_col1
INTO some_var
FROM some_tab
WHERE some_col2 = some_filter;
DBMS_OUTPUT.PUT_LINE('Our value is ' || some_var);
END;Этот код выполнит запрос и выведет результат в окне вывода, используя процедуру PUT_LINE.
______________________________________________________________________________
Общие рекомендации
Использование якорных типов: рекомендуется использовать %TYPE для переменных, чтобы избежать ошибок при изменении структуры базы данных.
Поддержка констант: константы помогают избежать случайных изменений данных, особенно если скрипт используется в разных частях программы.
Понимание областей видимости переменных: правильное использование переменных в разных областях (локальные в PL/SQL, глобальные в сессии) поможет оптимизировать выполнение программы.
DBMS_OUTPUT: пакет DBMS_OUTPUT полезен для отладки и вывода значений переменных в процессе выполнения программ.
Примечание. В SQL Developer для вывода put_line в окошко необходимо предварительно установить
SET serveroutput ON______________________________________________________________________________
Резюме
В статье не были рассмотрены сложные типы данных, не рассказано о курсорах, триггерах. Нет достаточного многих важных аспектов PL/SQL.
Почему? Статья родилась из моих заметок, которые составлял в процессе самостоятельного изучения темы, а именно из первого блока, аля “новичкового”.
Планирую структуризировать дальнейшие заметки и составить еще несколько статей в том же обзорном ключе.
