Профессиональная разработка хранимых процедур на языке PL/SQL в СУБД Oracle

Архитектура PL/SQL и базовая структура анонимного блока

Когда стандартный SQL-запрос сталкивается с необходимостью реализовать сложную бизнес-логику — например, начислить проценты по кредиту только тем клиентам, чья задолженность превышает средний показатель по региону, и при этом отправить уведомление в смежную систему — возможности декларативного языка подходят к пределу. В этот момент в игру вступает PL/SQL (Procedural Language extensions to SQL). Это не просто «надстройка» над базой данных, а мощный процедурный язык, интегрированный в ядро Oracle, который позволяет объединить мощь манипуляции данными с гибкостью алгоритмического программирования.

Механика взаимодействия: SQL Engine vs PL/SQL Engine

Ключ к пониманию производительности и логики работы в Oracle лежит в архитектурном разделении двух движков (engines). SQL — это декларативный язык: вы описываете, что хотите получить, а оптимизатор решает, как это сделать. PL/SQL — это процедурный язык: вы описываете последовательность шагов, циклы и условия.

Внутри сервера Oracle существуют два исполнителя. Когда вы запускаете блок кода, управление берет на себя PL/SQL Engine. Он обрабатывает процедурные команды (присваивание переменных, IF-THEN-ELSE, циклы LOOP), но как только в коде встречается SQL-оператор (SELECT, INSERT, UPDATE, DELETE), управление и данные передаются SQL Engine. Это переключение контекста (context switching) является одной из самых дорогостоящих операций с точки зрения ресурсов процессора.

Представьте, что вы строите дом. PL/SQL Engine — это прораб с чертежами, который знает последовательность действий. SQL Engine — это бригада строителей, которая умеет только класть кирпич или копать траншею. Каждый раз, когда прораб дает команду бригаде, он тратит время на передачу инструкций. Если прораб будет просить класть по одному кирпичу за раз (строка за строкой в цикле), работа затянется. Профессиональная разработка на PL/SQL направлена на то, чтобы минимизировать эти переключения и передавать работу «пакетами».

Архитектура PL/SQL обладает свойством «тесной интеграции». Это означает, что типы данных SQL (например, NUMBER, VARCHAR2, DATE) бесшовно распознаются в процедурном коде, а компилятор проверяет зависимости объектов базы данных еще на этапе сборки кода, что исключает ошибки выполнения, связанные с опечатками в именах таблиц.

Анатомия анонимного блока: фундамент кода

В PL/SQL основной единицей кода является блок. Существует два типа блоков: анонимные и именованные (процедуры, функции, пакеты, триггеры). Мы начнем с анонимного блока, так как это простейшая форма исполнения кода, которая не сохраняется в базе данных как объект, но служит «контейнером» для выполнения скриптов миграции или тестирования логики.

Структура блока жестко регламентирована и состоит из четырех секций:

1. DECLARE (необязательная): Здесь описываются переменные, константы, курсоры и пользовательские типы данных.
2. BEGIN (обязательная): Тело блока. Здесь располагается исполняемый код, логика и SQL-запросы.
3. EXCEPTION (необязательная): Секция перехвата и обработки ошибок.
4. END; (обязательная): Маркер завершения блока.

Рассмотрим простейший пример, чтобы увидеть синтаксические границы:

DECLARE
    v_user_name VARCHAR2(100);
    v_login_date DATE := SYSDATE;
BEGIN
    SELECT first_name INTO v_user_name
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Пользователь: ' || v_user_name || ' вошел в ' || TO_CHAR(v_login_date, 'HH24:MI'));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Сотрудник не найден.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Произошла непредвиденная ошибка: ' || SQLERRM);
END;

В этом блоке мы видим ключевой оператор SELECT ... INTO. В обычном SQL результат запроса возвращается клиенту. В PL/SQL результат обязательно должен быть помещен в переменную. Если запрос вернет 0 строк или более 1 строки, выполнение прервется и управление перейдет в секцию EXCEPTION.

Секция объявлений (DECLARE): управление памятью

Секция DECLARE — это место, где вы резервируете ресурсы. Важно понимать разницу между типами данных и способами их инициализации. В PL/SQL переменные могут иметь значения по умолчанию, а также быть константами.

Особое внимание стоит уделить атрибуту %TYPE. Это "золотой стандарт" профессиональной разработки. Вместо того чтобы жестко прописывать v_salary NUMBER(10,2), лучше использовать:

v_salary employees.salary%TYPE;

Это создает динамическую привязку к типу данных столбца в таблице. Если завтра отдел сопровождения БД изменит точность зарплаты в таблице employees, ваш код PL/SQL не потребует перекомпиляции или правки — он автоматически подстроится под новую структуру.

Константы и обязательные значения

Если переменная не должна меняться в ходе выполнения, используйте ключевое слово CONSTANT. Это не только защищает от логических ошибок, но и позволяет компилятору оптимизировать использование памяти:

c_vat_rate CONSTANT NUMBER := 0.20;

Если же вы объявили переменную с ограничением NOT NULL, вы обязаны инициализировать ее сразу в секции DECLARE, иначе блок не скомпилируется.

Исполняемая секция (BEGIN ... END): сердце алгоритма

Все, что находится между BEGIN и EXCEPTION (или END), выполняется последовательно. Здесь действуют строгие правила типизации. В отличие от некоторых других языков, PL/SQL — это сильно типизированный язык. Вы не можете просто сложить строку и число без явного или неявного преобразования.

Важный нюанс работы внутри блока — область видимости (scope). Блоки могут быть вложенными. Переменная, объявленная во внешнем блоке, видна во внутреннем, но не наоборот.

DECLARE
    v_outer VARCHAR2(20) := 'Внешняя';
BEGIN
    DECLARE
        v_inner VARCHAR2(20) := 'Внутренняя';
    BEGIN
        DBMS_OUTPUT.PUT_LINE(v_outer); -- Доступно
        DBMS_OUTPUT.PUT_LINE(v_inner); -- Доступно
    END;
    -- DBMS_OUTPUT.PUT_LINE(v_inner); -- ОШИБКА: v_inner здесь не существует
END;

Использование вложенных блоков — это мощный инструмент локализации обработки ошибок. Если вы хотите, чтобы ошибка в конкретном маленьком расчете не прерывала работу всего большого скрипта, вы можете обернуть этот расчет в собственный BEGIN ... EXCEPTION ... END;.

Обработка исключений (EXCEPTION): искусство устойчивости

Профессиональный код отличается от любительского тем, как он реагирует на аномалии. В PL/SQL ошибка называется «исключением» (exception). Когда происходит ошибка (например, деление на ноль или нарушение уникального ключа), нормальный ход выполнения программы останавливается, и Oracle ищет ближайший обработчик в секции EXCEPTION.

Существует три категории исключений:

1. Предопределенные (Predefined Oracle Errors): Такие как NO_DATA_FOUND (запрос SELECT INTO ничего не вернул) или TOO_MANY_ROWS (запрос вернул больше одной строки).
2. Непредопределенные: Ошибки Oracle, у которых есть код (например, ORA-00942: таблица не существует), но нет стандартного имени в языке.
3. Пользовательские (User-defined): Ошибки, которые вы создаете сами для реализации бизнес-логики (например, insufficient_funds).

Рассмотрим пример с вложенной логикой обработки:

DECLARE
    v_total_sales NUMBER;
    v_bonus NUMBER;
BEGIN
    -- Основная логика
    SELECT SUM(amount) INTO v_total_sales FROM orders;

    BEGIN
        -- Вложенный блок для расчета бонуса
        v_bonus := v_total_sales / 0; -- Намеренная ошибка
    EXCEPTION
        WHEN ZERO_DIVIDE THEN
            v_bonus := 0; -- Гасим ошибку и продолжаем
            DBMS_OUTPUT.PUT_LINE('Предупреждение: деление на ноль при расчете бонуса.');
    END;

    DBMS_OUTPUT.PUT_LINE('Итоговый бонус: ' || v_bonus);
END;

Если бы мы не использовали вложенный блок, ошибка деления на ноль «выбросила» бы нас из всего анонимного блока, и финальная строка с выводом бонуса никогда бы не выполнилась.

Взаимодействие с сервером: пакет DBMS_OUTPUT

Для отладки и вывода информации в анонимных блоках используется пакет DBMS_OUTPUT. Важно понимать, что это не «печать на экран» в реальном времени. Когда вы вызываете PUT_LINE, сообщение записывается в специальный буфер в памяти сервера. Только после того, как выполнение всего блока завершится успешно (или с ошибкой), клиентское приложение (например, SQL Developer, DBeaver или SQL*Plus) запрашивает содержимое этого буфера и отображает его пользователю.

По умолчанию буфер может быть выключен. В SQL*Plus или SQL Developer его нужно активировать командой: SET SERVEROUTPUT ON;

Размер буфера ограничен. В старых версиях Oracle он составлял 2000 байт, в современных — по умолчанию 20 000 байт, но может быть установлен в UNLIMITED. Однако злоупотребление выводом в высоконагруженных процедурах может привести к избыточному потреблению памяти SGA (System Global Area).

Литералы и наборы символов

В PL/SQL строковые литералы всегда заключаются в одиночные кавычки: 'Текст'. Если внутри строки должна быть сама кавычка, она дублируется: 'It''s a string'.

Для работы с большими объемами текста или специальными символами Oracle поддерживает альтернативный синтаксис кавычек (Q-quoting mechanism): v_text := q'[Это строка, где можно использовать 'одиночные кавычки' без дублирования]'; Этот механизм значительно повышает читаемость кода, особенно при генерации динамического SQL внутри PL/SQL блоков.

Сравнение производительности: SQL vs PL/SQL

Частая ошибка начинающих разработчиков — перенос всей логики в PL/SQL. Важно помнить правило: «Если это можно сделать на чистом SQL — делайте это на SQL».

Рассмотрим задачу: обновить статус у 100 000 заказов.

• Подход SQL: Один оператор UPDATE orders SET status = 'PROCESSED' WHERE ...;
• Подход PL/SQL: Цикл FOR rec IN (SELECT id FROM orders ...) LOOP UPDATE orders SET ... WHERE id = rec.id; END LOOP;

Второй подход будет работать в десятки раз медленнее из-за того самого переключения контекста между PL/SQL Engine и SQL Engine на каждой итерации цикла. PL/SQL следует использовать для:

1. Сложных процедурных проверок, которые невозможно описать в CHECK constraints или WHERE.
2. Группировки нескольких SQL-операций в одну атомарную транзакцию.
3. Обработки исключений и логирования ошибок.
4. Автоматизации административных задач.

Транзакционность внутри блока

Анонимный блок сам по себе не является границей транзакции. Если внутри блока выполнено три оператора INSERT и один UPDATE, изменения не зафиксируются в базе данных автоматически (если не включен AUTOCOMMIT в клиенте, что не рекомендуется). Вы должны явно использовать COMMIT или ROLLBACK.

Однако стоит помнить о «побочных эффектах». Команды DDL (например, EXECUTE IMMEDIATE 'CREATE TABLE ...') вызывают неявный COMMIT. Если такой оператор встретится в середине вашего блока, все предыдущие DML-изменения будут зафиксированы в базе данных, и вы не сможете сделать ROLLBACK в случае последующей ошибки.

Особенности компиляции и выполнения

Когда вы отправляете анонимный блок на выполнение, происходит следующее:

1. Парсинг (Parsing): Проверка синтаксиса.
2. Связывание (Binding): Проверка имен таблиц, колонок и прав доступа пользователя.
3. Генерация P-кода: Компилятор переводит ваш код в байт-код, понятный виртуальной машине PL/SQL.
4. Выполнение: Исполнение байт-кода.

Поскольку анонимный блок не сохраняется в БД, эти этапы проходят при каждом запуске. Это одно из главных отличий от хранимых процедур, которые компилируются один раз и хранятся в виде готового P-кода, что экономит время при повторных вызовах.

Практические рекомендации по оформлению

Для того чтобы ваш код был профессиональным и поддерживаемым, придерживайтесь следующих правил:

• Именуйте переменные с префиксами. Например, v_ для локальных переменных (v_count), c_ для констант (c_tax_rate), p_ для входных параметров процедур (которые мы изучим позже). Это позволяет избежать конфликтов имен с именами колонок в SQL-запросах.
• Используйте отступы. Каждый уровень вложенности (внутри DECLARE, BEGIN, IF, LOOP) должен иметь отступ в 2 или 4 пробела.
• Комментируйте логику. Используйте -- для однострочных комментариев и /* ... */ для многострочных описаний сложных алгоритмов.
• Секция EXCEPTION не должна быть пустой. Худшая практика — это EXCEPTION WHEN OTHERS THEN NULL;. Это «проглатывание» ошибок, которое делает невозможной отладку системы, так как вы никогда не узнаете, что что-то пошло не так.

Работа с NULL в PL/SQL

Понимание логики NULL критично. В PL/SQL, как и в SQL, NULL означает отсутствие значения. Любая арифметическая операция с NULL дает NULL. Любое логическое сравнение с NULL (кроме IS NULL) дает результат UNKNOWN.

DECLARE
    v_a NUMBER := NULL;
    v_b NUMBER := 10;
BEGIN
    IF v_a > v_b THEN
        DBMS_OUTPUT.PUT_LINE('A больше B');
    ELSIF v_a <= v_b THEN
        DBMS_OUTPUT.PUT_LINE('A меньше или равно B');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Результат неопределен'); -- Сработает эта ветка
    END IF;
END;

Это поведение часто приводит к логическим ошибкам в условиях IF-THEN-ELSE. Всегда проверяйте переменные на NULL, если они приходят из столбцов таблиц, не имеющих ограничения NOT NULL.

Масштабируемость и анонимные блоки

Хотя анонимные блоки удобны, их чрезмерное использование в клиентском коде (например, внутри Java или Python приложения) считается плохим тоном. Основная причина — безопасность (риск SQL-инъекций) и сложность обновления логики. Если бизнес-логика изменится, вам придется обновлять код во всех приложениях. Если же логика инкапсулирована в хранимую процедуру на стороне сервера, вы меняете код в одном месте, и все клиенты мгновенно получают обновленную версию.

Анонимные блоки идеально подходят для:

• Разовых скриптов исправления данных.
• Запуска тестов.
• Инициализации окружения в скриптах деплоя.
• Оберток (wrappers) для вызова сложных пакетов из командной строки.

В следующей главе мы перейдем к созданию постоянных объектов базы данных — хранимых процедур, которые позволяют превратить эти блоки в полноценные элементы программного интерфейса вашей базы данных.

Создание хранимых процедур и синтаксис заголовка подпрограмм

Представьте, что вы написали идеальный анонимный блок, который рассчитывает годовые бонусы для сотрудников. Он работает безупречно, но как только вам нужно запустить его снова или передать коллеге, приходится копировать сотни строк кода в SQL-редактор. В высоконагруженных системах Oracle такой подход не только неудобен, но и неэффективен: анонимные блоки компилируются при каждом запуске, создавая лишнюю нагрузку на CPU. Решение этой проблемы — трансформация временного кода в объект базы данных. Хранимая процедура — это не просто «сохраненный скрипт», это скомпилированный программный модуль, обладающий собственным именем, правами доступа и строго определенным интерфейсом взаимодействия.

От анонимности к именованным объектам

Переход от анонимного блока к хранимой процедуре кардинально меняет жизненный цикл кода. Анонимный блок существует ровно столько, сколько длится его выполнение. Хранимая процедура же становится частью словаря данных (Data Dictionary). Когда вы выполняете команду CREATE PROCEDURE, Oracle выполняет несколько критически важных действий:

1. Синтаксический анализ: проверка корректности кода PL/SQL.
2. Проверка зависимостей: СУБД проверяет, существуют ли таблицы, представления и другие объекты, к которым обращается процедура.
3. Компиляция в P-code: код переводится в промежуточное представление, оптимизированное для исполнения движком PL/SQL.
4. Сохранение метаданных: исходный текст и скомпилированный код сохраняются в системных таблицах (например, USER_SOURCE и USER_OBJECTS).

Главное преимущество здесь заключается в разделении интерфейса и реализации. Вы можете изменить внутреннюю логику процедуры, не меняя способ, которым внешние приложения её вызывают. Это фундаментальный принцип инкапсуляции в проектировании баз данных.

Анатомия команды CREATE OR REPLACE

Базовый синтаксис создания процедуры кажется простым, но он содержит нюансы, определяющие поведение объекта в многопользовательской среде.

CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter_name [IN | OUT | IN OUT] data_type [, ...])]
{IS | AS}
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

Использование конструкции OR REPLACE является стандартом де-факто в разработке. Без неё попытка создать уже существующую процедуру приведет к ошибке ORA-00955: name is already used by an existing object. Однако стоит помнить, что REPLACE фактически удаляет старый объект и создает новый, при этом сохраняя выданные на него права (GRANTs). Если же вы используете DROP и затем CREATE, все привилегии доступа придется назначать заново.

Выбор между ключевыми словами IS и AS в Oracle PL/SQL не несет функциональной разницы для процедур (в отличие от спецификаций пакетов, где исторически сложились свои традиции). Вы можете использовать то, которое лучше соответствует вашему стилю кодирования или корпоративным стандартам.

Заголовок процедуры: проектирование контракта

Заголовок (сигнатура) процедуры — это контракт между разработчиком БД и потребителем (Java-сервисом, отчетом или другим PL/SQL блоком). Ошибки в проектировании заголовка обходятся дороже всего, так как их исправление требует изменения всех вызывающих модулей.

Именование и идентификаторы

Имя процедуры должно быть глаголом или содержать глагол, отражающий действие: calculate_tax, archive_logs, process_order. В Oracle идентификаторы по умолчанию нечувствительны к регистру и ограничены 128 символами (в версиях до 12.2 — 30 символами). Профессиональным тоном считается использование snake_case.

Параметризация: мост между окружениями

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

1. Режим IN (по умолчанию): параметр передается в процедуру как константа. Вы не можете изменить его значение внутри блока. Если вы попытаетесь присвоить новое значение переменной, помеченной как IN, компилятор выдаст ошибку.
2. Режим OUT: параметр используется для возврата значения вызывающей стороне. В начале выполнения процедуры значение OUT-параметра всегда равно NULL (если не используется опция NOCOPY, но об этом позже).
3. Режим IN OUT: гибридный режим, позволяющий передать значение, изменить его внутри и вернуть результат.

Важное правило: при объявлении типов данных в заголовке запрещено указывать точность или масштаб.

• Неправильно: p_amount NUMBER(10,2)
• Правильно: p_amount NUMBER

Это ограничение существует потому, что процедура должна быть готова принять любой NUMBER, а проверку на соответствие конкретной точности Oracle выполнит на этапе связывания значений. Если вам нужно жестко ограничить тип данных типом колонки таблицы, используйте %TYPE, который мы разбирали ранее. Это обеспечит «типовую безопасность» вашего контракта.

Формальные и фактические параметры

Для глубокого понимания работы процедур важно различать два состояния параметров:

• Формальные параметры: переменные, указанные в определении процедуры (в коде CREATE PROCEDURE).
• Фактические параметры: конкретные значения или переменные, которые передаются в процедуру в момент вызова.

Рассмотрим пример:

-- Определение (формальные параметры: p_emp_id, p_bonus)
CREATE OR REPLACE PROCEDURE apply_bonus(
    p_emp_id IN employees.employee_id%TYPE,
    p_bonus  IN NUMBER
) IS
BEGIN
    UPDATE employees
    SET salary = salary + p_bonus
    WHERE employee_id = p_emp_id;
END apply_bonus;

При вызове apply_bonus(101, 500), числа 101 и 500 становятся фактическими параметрами. Oracle сопоставляет их по позиции или по имени.

Способы передачи параметров

Существует три способа сопоставления фактических параметров формальным:

1. Позиционный: параметры передаются в том же порядке, в котором они объявлены. apply_bonus(101, 500); Плюс: краткость. Минус: при большом количестве параметров легко ошибиться, а читаемость кода падает.
2. Именованный: использование оператора =>. apply_bonus(p_bonus => 500, p_emp_id => 101); Плюс: самодокументированность кода. Порядок не важен. Это стандарт для промышленной разработки.
3. Смешанный: первые параметры передаются позиционно, остальные — по имени. Как только вы использовали именованный способ, все последующие параметры тоже должны быть именованными.

Значения по умолчанию и их влияние на гибкость

PL/SQL позволяет задавать значения по умолчанию для параметров IN с помощью ключевого слова DEFAULT или оператора :=.

CREATE OR REPLACE PROCEDURE create_log(
    p_message IN VARCHAR2,
    p_level   IN VARCHAR2 DEFAULT 'INFO',
    p_module  IN VARCHAR2 := 'GENERAL'
) IS ...

Это делает процедуру гибкой: её можно вызвать с одним, двумя или тремя аргументами. Однако будьте осторожны с перегрузкой и значениями по умолчанию в больших пакетах — иногда это может привести к неоднозначности, когда компилятор не понимает, какую именно версию подпрограммы вы хотите запустить.

Секция объявлений: жизнь между IS и BEGIN

В анонимном блоке мы использовали слово DECLARE. В хранимой процедуре оно запрещено. Роль секции объявлений выполняет пространство между ключевым словом IS (или AS) и BEGIN.

Здесь вы определяете локальные переменные, курсоры, типы и даже вложенные подпрограммы. Локальные переменные процедуры невидимы за её пределами и уничтожаются сразу после завершения выполнения.

Особое внимание стоит уделить инициализации переменных. В PL/SQL переменная, которой не присвоено значение, всегда содержит NULL. Если вы планируете использовать переменную в расчетах, например, как счетчик, обязательно инициализируйте её: l_counter PLS_INTEGER := 0;

Использование префиксов (например, p_ для параметров и l_ для локальных переменных) — это не просто прихоть, а способ избежать конфликтов имен с колонками таблиц в SQL-запросах. Если имя переменной совпадает с именем колонки, Oracle отдаст приоритет колонке, что может привести к логическим ошибкам в WHERE-клаузах, которые крайне трудно отловить.

Управление выполнением и чистота кода

Тело процедуры (между BEGIN и END) должно быть сфокусировано на одной бизнес-задаче. Если процедура занимает более 200-300 строк, это явный признак того, что её пора разбивать на несколько более мелких модулей.

Метка завершения

Хорошей практикой считается повторение имени процедуры после ключевого слова END: END apply_bonus; Это помогает визуально ориентироваться в коде, особенно если внутри процедуры есть вложенные блоки или сложные конструкции обработки исключений.

Компиляция и ошибки

Когда вы запускаете скрипт создания процедуры, Oracle может ответить: Procedure created with compilation errors. Это означает, что объект создан, но он имеет статус INVALID. Вы не сможете его запустить. Чтобы увидеть ошибки, используйте команду: SHOW ERRORS; Или обратитесь к представлению USER_ERRORS. Пока процедура находится в статусе INVALID, любые попытки вызвать её из других модулей будут вызывать ошибку ORA-06550.

Взаимодействие с транзакциями

По умолчанию хранимая процедура является частью той же транзакции, которая её вызвала. Если внутри процедуры выполняется UPDATE, а затем во внешнем коде происходит ROLLBACK, изменения, сделанные процедурой, также будут отменены.

Однако существует механизм автономных транзакций (PRAGMA AUTONOMOUS_TRANSACTION). Он позволяет процедуре выполнять действия в отдельной транзакции, которая может быть зафиксирована (COMMIT) независимо от основной. Это критически важно для процедур логирования: вы хотите сохранить запись об ошибке в таблице логов, даже если основная операция (например, перевод денег) завершилась неудачей и была откачена.

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_log (message, log_date) VALUES (p_msg, SYSDATE);
    COMMIT; -- Фиксирует только вставку в лог
END;

Производительность и передача параметров по ссылке

Когда мы передаем большие объемы данных (например, длинные строки или коллекции) через OUT или IN OUT параметры, Oracle по умолчанию использует механизм передачи по значению. Это означает, что создается полная копия данных. Если процедура завершается успешно, копия копируется обратно в исходную переменную. Если происходит необработанное исключение, исходная переменная остается нетронутой.

Для оптимизации памяти и скорости можно использовать хинт NOCOPY: p_data IN OUT NOCOPY CLOB

Это заставляет Oracle передавать параметр по ссылке.

• Плюс: мгновенная передача огромных объемов данных без копирования.
• Минус: если в середине работы процедуры произойдет ошибка, фактический параметр (переменная вызывающей стороны) может оказаться в «испорченном» состоянии, так как изменения вносились в него напрямую.

Права доступа: INVOKER vs DEFINER

Это один из самых глубоких аспектов безопасности в Oracle. При создании процедуры вы определяете, с чьими правами она будет выполняться.

1. AUTHID DEFINER (по умолчанию): процедура выполняется с правами того, кто её создал. Если системный администратор DBA_USER создал процедуру, которая удаляет данные из таблицы SALARIES, и дал право на выполнение этой процедуры обычному пользователю CLERK, то CLERK сможет удалять данные, даже если у него нет прямого доступа к таблице. Это позволяет реализовывать строго контролируемый доступ к данным.
2. AUTHID CURRENT_USER: процедура выполняется с правами того, кто её вызывает в данный момент. Это полезно для утилит, которые должны работать с таблицами в схеме текущего пользователя.

Выбор режима AUTHID указывается в заголовке перед ключевым словом IS.

Пример комплексной процедуры

Соберем изученные элементы в единый пример. Создадим процедуру для регистрации нового заказа, которая принимает данные, рассчитывает налог и возвращает ID созданной записи.

CREATE OR REPLACE PROCEDURE register_new_order (
    p_customer_id IN  customers.customer_id%TYPE,
    p_amount      IN  NUMBER,
    p_order_id    OUT orders.order_id%TYPE,
    p_status      OUT VARCHAR2
) IS
    -- Секция объявлений (без слова DECLARE)
    l_tax_rate    CONSTANT NUMBER := 0.15;
    l_final_total NUMBER;

    -- Внутренняя логика может быть вынесена в локальную подпрограмму
    FUNCTION calculate_total(amount NUMBER, tax NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN amount * (1 + tax);
    END calculate_total;

BEGIN
    -- Валидация входных данных
    IF p_amount <= 0 THEN
        p_status := 'REJECTED: Invalid amount';
        RETURN; -- Досрочный выход из процедуры
    END IF;

    l_final_total := calculate_total(p_amount, l_tax_rate);

    -- Выполнение DML
    INSERT INTO orders (customer_id, total_amount, order_date)
    VALUES (p_customer_id, l_final_total, SYSDATE)
    RETURNING order_id INTO p_order_id;

    p_status := 'SUCCESS';

EXCEPTION
    WHEN OTHERS THEN
        -- В реальном проекте здесь должен быть вызов процедуры логирования
        p_status := 'ERROR: ' || SQLERRM;
        RAISE; -- Пробрасываем ошибку выше
END register_new_order;

В этом примере мы видим:

• Использование %TYPE для синхронизации типов с БД.
• Комбинацию IN и OUT параметров.
• Константы и локальные функции для чистоты кода.
• Обработку граничных условий через RETURN.
• Использование RETURNING INTO для получения сгенерированного ключа.

Замыкание темы

Создание хранимых процедур — это переход от написания скриптов к проектированию систем. Заголовок процедуры определяет её внешнее лицо, а внутренняя структура обеспечивает надежность и производительность. Понимание того, как Oracle управляет параметрами, как работает область видимости и как права доступа влияют на выполнение, позволяет создавать не просто работающий, а защищенный и масштабируемый код. В следующих разделах мы подробнее разберем типы данных и управляющие конструкции, которые наполняют эти блоки жизнью.

Работа с переменными, константами и типами данных в PL/SQL

Знаете ли вы, что неверный выбор типа данных для переменной в PL/SQL может замедлить выполнение процедуры в десятки раз, даже если в коде нет ни одного SQL-запроса? В мире Oracle Database переменная — это не просто ячейка памяти, а сложный объект, имеющий свои правила выравнивания, семантику сравнения и механизмы преобразования. Когда мы переходим от написания простых SQL-запросов к разработке логики внутри хранимых процедур, понимание типизации становится фундаментом производительности и надежности системы.

Жизненный цикл и типизация данных

В PL/SQL типизация является строгой и статической. Это означает, что тип переменной определяется в момент компиляции и не может быть изменен во время выполнения. Однако за этой строгостью скрывается иерархия типов, которая делится на четыре основные категории: скалярные, составные (коллекции и записи), ссылочные (курсоры) и LOB-типы (Large Objects).

Работа с данными начинается в секции объявлений. В хранимых процедурах она располагается между заголовком IS | AS и ключевым словом BEGIN. Важно понимать, что при каждом вызове процедуры переменные инициализируются заново. Если вы не присвоили значение переменной при объявлении, она автоматически получает значение NULL (за исключением случаев, когда наложено ограничение NOT NULL).

Скалярные типы: за пределами стандартного SQL

Хотя PL/SQL тесно интегрирован с SQL, его набор типов данных шире. Например, тип BOOLEAN, который отсутствует в стандартных таблицах Oracle SQL, является полноценным гражданином в PL/SQL.

Числовые типы и точность вычислений

Для работы с числами чаще всего используется NUMBER. Его гибкость позволяет хранить как целые числа, так и числа с плавающей точкой.

$$\text{NUMBER}(p, s)$$

Здесь $p$ — прецизионность (общее количество цифр), а $s$ — масштаб (количество цифр после запятой). Однако для внутренних вычислений в процедурах, где не требуется высокая точность десятичных дробей (например, счетчики циклов), эффективнее использовать PLS_INTEGER.

В отличие от NUMBER, который является программно-реализованным типом (что обеспечивает идентичность вычислений на разных платформах, но требует больше ресурсов CPU), PLS_INTEGER использует аппаратную арифметику процессора. Это делает операции с ним значительно быстрее. Существует также тип BINARY_INTEGER, который в современных версиях Oracle (начиная с 10g) практически идентичен PLS_INTEGER, но исторически имел другие реализации.

Символьные типы и семантика длины

При работе с VARCHAR2 в PL/SQL важно помнить о лимите в 32767 байт. Это значительно больше, чем стандартный лимит в 4000 байт для столбцов таблиц (хотя в последних версиях Oracle лимит в SQL также может быть расширен до 32к при включении параметра MAX_STRING_SIZE = EXTENDED).

Особое внимание стоит уделить семантике длины: BYTE против CHAR.

v_name VARCHAR2(20 BYTE); -- лимит в байтах
v_desc VARCHAR2(20 CHAR); -- лимит в символах

Если ваша база данных использует кодировку UTF-8 (AL32UTF8), один символ может занимать до 4 байт. Объявление VARCHAR2(20) по умолчанию часто трактуется как BYTE, что приведет к ошибке ORA-06502: PL/SQL: numeric or value error: character string buffer too small, если вы попытаетесь записать туда 20 кириллических букв. В профессиональной разработке рекомендуется всегда явно указывать CHAR или настраивать параметр сессии NLS_LENGTH_SEMANTICS.

Константы и модификатор NOT NULL

Использование магических чисел и строк внутри кода — признак плохого тона и источник трудноуловимых багов. Константы позволяют зафиксировать значение и гарантировать, что оно не будет изменено в ходе выполнения алгоритма.

c_tax_rate CONSTANT NUMBER(3, 2) := 0.20;

Ключевое слово CONSTANT обязывает инициализировать переменную немедленно. Попытка присвоить ей новое значение в секции BEGIN...END вызовет ошибку компиляции. Это не только защищает логику, но и дает подсказку оптимизатору PL/SQL.

Модификатор NOT NULL работает похожим образом, но позволяет изменять значение переменной, требуя лишь, чтобы она никогда не становилась NULL.

v_user_id NUMBER NOT NULL := -1;

Если в ходе выполнения процедуры вы попытаетесь присвоить такой переменной результат запроса SELECT INTO, который вернет NULL, выполнение немедленно прервется с системным исключением. Это отличный способ самодокументирования кода и раннего обнаружения логических ошибок.

Продвинутая привязка типов: %TYPE и %ROWTYPE

В предыдущих статьях мы касались %TYPE, но давайте разберем его глубокое влияние на архитектуру. Использование прямой типизации (например, v_emp_name VARCHAR2(100)) создает «хрупкий» код. Если завтра бизнес-требования изменятся и размер поля в таблице увеличится до 200 символов, ваша процедура упадет.

Атрибут %TYPE создает зависимость на уровне метаданных. При изменении типа столбца в таблице, все зависящие от него процедуры помечаются как INVALID. При следующем вызове Oracle автоматически перекомпилирует их, подтягивая новую размерность типа.

Использование %ROWTYPE

Если процедура обрабатывает целую строку таблицы, объявление десятка переменных через %TYPE становится избыточным. Здесь на сцену выходит %ROWTYPE.

PROCEDURE process_employee(p_emp_id IN employees.employee_id%TYPE) IS
    r_emp employees%ROWTYPE;
BEGIN
    SELECT * INTO r_emp FROM employees WHERE employee_id = p_emp_id;

    -- Доступ к полям через точечную нотацию
    IF r_emp.salary > 10000 THEN
        dbms_output.put_line(r_emp.last_name || ' is a senior member.');
    END IF;
END;

%ROWTYPE создает запись (Record), структура которой в точности повторяет структуру таблицы. Это не только сокращает код, но и минимизирует количество правок при добавлении новых колонок в таблицу (если используется SELECT *). Однако стоит помнить о производительности: если вам нужны только два поля из пятидесяти, использование %ROWTYPE с SELECT * избыточно нагружает буферный кэш и сеть.

Подтипы (SUBTYPE) и чистота кода

Для повышения читаемости и повторного использования логики типизации в PL/SQL существует механизм SUBTYPE. Он позволяет создавать псевдонимы для существующих типов, иногда с наложением ограничений.

DECLARE
    SUBTYPE t_money IS NUMBER(15, 2);
    SUBTYPE t_short_code IS VARCHAR2(5 CHAR);

    v_balance t_money;
    v_currency t_short_code;
BEGIN
    v_balance := 1500.50;
    v_currency := 'USD';
END;

Это особенно полезно в пакетах. Вы можете объявить набор подтипов в спецификации пакета и использовать их во всем приложении. Если формат денежных сумм изменится (например, потребуется 4 знака после запятой), вам достаточно будет изменить определение SUBTYPE в одном месте.

Существуют также «ограниченные» подтипы (constrained subtypes). Например, встроенный подтип POSITIVE — это фактически BINARY_INTEGER с ограничением на значения больше нуля.

Неявное и явное преобразование типов

Oracle славится своей «либеральностью» в преобразовании типов, но для профессионального разработчика это скорее ловушка, чем удобство.

Опасности неявного преобразования

Рассмотрим ситуацию:

DECLARE
    v_val NUMBER;
    v_str VARCHAR2(100) := '123.45';
BEGIN
    v_val := v_str; -- Неявное преобразование
END;

Этот код сработает, если настройки NLS_NUMERIC_CHARACTERS в сессии совпадают с форматом строки. Если в сессии разделителем является запятая, а в строке пришла точка — код упадет.

Более коварная проблема — производительность. При сравнении переменных разных типов в условии WHERE или IF, Oracle вынужден применять функции преобразования к каждой строке или итерации. Это может привести к тому, что индекс по столбцу не будет использован.

Явное преобразование как стандарт

Всегда используйте функции TO_NUMBER, TO_DATE, TO_CHAR, CAST. При работе с датами обязательно указывайте маску формата:

v_date := TO_DATE('2023-10-25', 'YYYY-MM-DD');

Это делает код независимым от региональных настроек сервера или клиента. Помните, что тип DATE в Oracle всегда включает в себя время до секунд. Если вам нужна более высокая точность (миллисекунды и выше) или учет часовых поясов, используйте семейство типов TIMESTAMP.

Сложные типы: записи (RECORD)

Помимо %ROWTYPE, вы можете определять собственные структуры данных с помощью типа RECORD. Это необходимо, когда данные собираются из разных таблиц или являются результатом вычислений.

DECLARE
    TYPE t_emp_summary IS RECORD (
        full_name  VARCHAR2(200),
        dept_name  departments.department_name%TYPE,
        total_pay  NUMBER
    );

    v_summary t_emp_summary;
BEGIN
    -- Логика заполнения записи
    v_summary.full_name := 'Ivan Ivanov';
END;

Записи позволяют группировать логически связанные данные, передавать их как единое целое в другие процедуры и функции, что значительно упрощает сигнатуры подпрограмм. Вместо передачи 10 параметров, вы передаете одну переменную типа RECORD.

Граничные случаи и специфика NULL

В PL/SQL NULL ведет себя согласно логике трехзначной истинности (True, False, Unknown). Одной из самых частых ошибок является попытка сравнения с NULL через оператор =.

IF v_var = NULL THEN -- Это условие НИКОГДА не будет истинным

Для проверки на пустоту всегда используется оператор IS NULL. Также помните, что пустая строка '' в Oracle Database (в отличие от некоторых других СУБД) тождественна NULL. Это фундаментальная особенность, которую нужно учитывать при валидации входящих параметров в процедурах.

Области видимости и время жизни

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

1. Локальные переменные: создаются при входе в блок, уничтожаются при выходе. Память выделяется в PGA (Process Global Area).
2. Пакетные переменные: инициализируются при первом обращении к пакету и живут до конца сессии пользователя. Это мощный инструмент для кэширования настроек, но опасный с точки зрения потребления памяти при больших объемах данных.

Практические рекомендации по работе с данными

Для написания производительного и чистого кода придерживайтесь следующих правил:

1. Используйте PLS_INTEGER для всех внутренних счетчиков и целочисленных вычислений, не связанных с сохранением в таблицы.
2. Предпочитайте %TYPE и %ROWTYPE жестко прописанным типам. Это обеспечивает «автоматическую» поддержку кода при изменении схемы БД.
3. Минимизируйте использование LONG и LONG RAW. Эти типы являются устаревшими (deprecated). Для больших текстов и бинарных данных используйте CLOB и BLOB.
4. Всегда инициализируйте переменные. Если переменная не должна быть пустой, используйте NOT NULL.
5. Группируйте данные в RECORD. Если вы видите, что передаете в подпрограммы одни и те же наборы параметров, объедините их в структуру.

Работа с коллекциями (введение)

Хотя детально коллекции будут рассмотрены позже, важно понимать, что переменная может быть массивом. В PL/SQL есть три вида коллекций: ассоциативные массивы (Index-by tables), вложенные таблицы (Nested Tables) и вариативные массивы (Varrays). Выбор типа коллекции зависит от того, нужно ли вам хранить данные в БД или только в памяти процедуры, и требуется ли разреженность индексов.

Использование коллекций вместе с переменными типа RECORD позволяет создавать в памяти мощные структуры данных, имитирующие целые таблицы, что критически важно для высокопроизводительной обработки данных без лишних обращений к дисковой подсистеме.

Влияние на производительность: типизация и память

Каждая переменная занимает место в PGA сессии. При разработке процедур, которые будут запускаться сотнями параллельных пользователей, объем памяти, выделяемый под VARCHAR2(32767), может стать проблемой, если таких переменных много. Oracle выделяет память под VARCHAR2 динамически, но до определенного порога. Будьте аккуратны с объявлением огромных буферов «на всякий случай».

Также стоит учитывать затраты на приведение типов. Если процедура в цикле на миллион итераций сравнивает VARCHAR2 и NUMBER, суммарные потери времени на implicit conversion могут составить секунды, что в масштабах Enterprise-систем недопустимо.

Завершая разбор работы с данными, стоит отметить, что профессионализм разработчика PL/SQL проявляется в мелочах: в точно выбранном типе данных, в отсутствии лишних преобразований и в умении использовать метаданные схемы через атрибуты привязки. Это создает фундамент для масштабируемых и легко сопровождаемых систем автоматизации бизнес-процессов.

Управление параметрами процедур: режимы IN, OUT и IN OUT

Ошибка компиляции PLS-00363: expression cannot be used as an assignment target — классический барьер, с которым сталкивается разработчик, впервые пытающийся изменить значение входящего параметра внутри хранимой процедуры. В PL/SQL параметры не являются безликими переменными, которые можно произвольно читать и перезаписывать. Они подчиняются строгим контрактам передачи данных, определяемым режимами IN, OUT и IN OUT. Эти режимы контролируют не только направление потока информации между вызывающей средой и подпрограммой, но и то, как Oracle управляет памятью, обрабатывает исключения и гарантирует целостность данных при сбоях.

Архитектурный смысл режимов параметров

Взаимодействие с хранимой процедурой строится на передаче фактических параметров (конкретных значений или переменных из вызывающего кода) в формальные параметры (идентификаторы, описанные в заголовке процедуры). Режим параметра — это инструкция для PL/SQL Engine, указывающая, кто имеет право на чтение и запись в эту область памяти.

Режим	Направление данных	Доступ внутри процедуры	Обязательность фактического параметра
IN	Внутрь (К процедуре)	Только чтение	Может быть константой, литералом, выражением или переменной.
OUT	Наружу (От процедуры)	Чтение и запись	Строго переменная (для приема результата).
IN OUT	В обе стороны	Чтение и запись	Строго переменная (должна быть инициализирована).

Выбор режима напрямую влияет на производительность и предсказуемость кода, особенно в высоконагруженных транзакционных системах.

Режим IN: Неизменяемый источник данных

Режим IN используется по умолчанию. Если при объявлении параметра режим не указан явно, компилятор назначит ему IN. Главное правило этого режима: внутри тела процедуры формальный параметр ведет себя как константа.

Попытка присвоить значение IN-параметру приведет к ошибке на этапе компиляции. Это защитный механизм, гарантирующий, что процедура не вызовет побочных эффектов в вызывающей среде, случайно затерев исходные данные.

CREATE OR REPLACE PROCEDURE calculate_discount (
    p_base_price IN NUMBER,
    p_discount_pct IN NUMBER
) IS
    v_final_price NUMBER;
BEGIN
    -- ОШИБКА: p_base_price нельзя изменить
    -- p_base_price := p_base_price * 0.9;

    v_final_price := p_base_price - (p_base_price * (p_discount_pct / 100));
    DBMS_OUTPUT.PUT_LINE('Итоговая цена: ' || v_final_price);
END;

Поскольку IN-параметры гарантированно не изменяются, Oracle оптимизирует работу с ними, передавая их по ссылке (by reference). Вместо того чтобы копировать значение фактического параметра в новую область памяти для формального параметра, PL/SQL Engine просто передает указатель на исходную переменную. Это критически важно при передаче объемных данных, таких как длинные строки VARCHAR2 или коллекции, так как исключает накладные расходы на выделение памяти и копирование.

Значения по умолчанию (DEFAULT)

Только IN-параметры могут иметь значения по умолчанию. Это позволяет вызывающему коду опускать передачу некоторых аргументов.

CREATE OR REPLACE PROCEDURE log_event (
    p_message IN VARCHAR2,
    p_level   IN VARCHAR2 DEFAULT 'INFO',
    p_source  IN VARCHAR2 := 'SYSTEM' -- Альтернативный синтаксис DEFAULT
) IS
BEGIN
    INSERT INTO system_logs (log_msg, log_level, log_src)
    VALUES (p_message, p_level, p_source);
END;

Если при вызове log_event('Job started') передается только первый аргумент, Oracle автоматически подставит 'INFO' и 'SYSTEM' для остальных. Важный нюанс: если вызывающий код явно передает NULL в качестве фактического параметра, значение по умолчанию не применяется. В процедуру поступит именно NULL.

Режим OUT: Канал возврата данных

Режим OUT предназначен для возврата результатов работы процедуры в вызывающую среду. В отличие от функций, которые могут вернуть только одно значение через оператор RETURN, процедура с несколькими OUT-параметрами способна вернуть целый набор независимых данных.

Ключевая особенность OUT-параметра, о которой часто забывают: при входе в процедуру он инициализируется значением NULL, независимо от того, какое значение имела переменная в вызывающем коде до вызова.

Если передать переменную с уже существующими данными в OUT-параметр, процедура "забудет" эти данные в момент начала выполнения. OUT-параметр не предназначен для чтения входящей информации.

Рассмотрим пример извлечения данных о сотруднике:

CREATE OR REPLACE PROCEDURE get_employee_info (
    p_emp_id    IN  NUMBER,
    p_name      OUT VARCHAR2,
    p_salary    OUT NUMBER
) IS
BEGIN
    -- В этот момент p_name и p_salary равны NULL
    SELECT first_name || ' ' || last_name, salary
    INTO p_name, p_salary
    FROM employees
    WHERE employee_id = p_emp_id;
END;

Вызов такой процедуры требует обязательного наличия переменных на принимающей стороне:

DECLARE
    v_emp_name VARCHAR2(100) := 'Старое имя';
    v_emp_sal  NUMBER;
BEGIN
    -- v_emp_name потеряет значение 'Старое имя' при вызове
    get_employee_info(101, v_emp_name, v_emp_sal);
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' получает ' || v_emp_sal);
END;

Фактический параметр для режима OUT не может быть литералом (например, 100 или 'Текст') или выражением ($x + y$). Это должна быть именованная область памяти — переменная, способная сохранить результат.

Режим IN OUT: Модификатор состояния

Режим IN OUT объединяет свойства обоих предыдущих режимов. Он используется, когда процедуре необходимо получить начальное значение, произвести над ним манипуляции и вернуть обновленный результат в ту же самую переменную.

В отличие от OUT, параметр IN OUT сохраняет значение, переданное из вызывающей среды. Типичный сценарий использования — нормализация или очистка данных.

CREATE OR REPLACE PROCEDURE normalize_phone (
    p_phone_number IN OUT VARCHAR2
) IS
BEGIN
    -- Читаем входящее значение, удаляем все нечисловые символы
    p_phone_number := REGEXP_REPLACE(p_phone_number, '[^0-9]', '');

    -- Добавляем код страны, если номер состоит из 10 цифр
    IF LENGTH(p_phone_number) = 10 THEN
        p_phone_number := '7' || p_phone_number;
    END IF;
END;

При вызове normalize_phone переменная передается с грязными данными, а после завершения работы процедуры содержит очищенный результат. Как и в случае с OUT, фактический параметр для IN OUT обязан быть переменной.

Механика управления памятью: Copy-In / Copy-Out

Понимание того, как Oracle физически передает данные между вызывающим кодом и процедурой, критически важно для написания надежного кода. Разница между передачей по ссылке (by reference) и по значению (by value) определяет поведение системы при возникновении исключений.

Как упоминалось ранее, IN-параметры передаются по ссылке. Параметры OUT и IN OUT по умолчанию передаются по значению с использованием механизма, называемого Copy-In / Copy-Out.

1. Copy-In: При вызове процедуры Oracle выделяет новую локальную область памяти для формального параметра IN OUT и копирует туда значение фактического параметра. Для OUT-параметра выделяется память, но она инициализируется как NULL (копирования входящего значения не происходит).
2. Выполнение: Процедура работает исключительно с этой локальной копией. Исходная переменная в вызывающем коде остается нетронутой.
3. Copy-Out: Если процедура завершается успешно, Oracle копирует итоговые значения из локальных формальных параметров обратно в фактические переменные вызывающего кода.

Поведение при исключениях (Rollback параметров)

Механизм Copy-Out обеспечивает транзакционную чистоту на уровне переменных. Если внутри процедуры возникает необработанное исключение, выполнение прерывается до того, как наступит фаза Copy-Out.

Это означает, что фактические переменные в вызывающем коде сохранят те значения, которые у них были до вызова процедуры. Частичные изменения, сделанные внутри упавшей процедуры, не "протекут" наружу.

Рассмотрим финансовую транзакцию:

CREATE OR REPLACE PROCEDURE process_payment (
    p_amount IN NUMBER,
    p_balance IN OUT NUMBER,
    p_status OUT VARCHAR2
) IS
BEGIN
    p_status := 'PROCESSING'; -- Локальное изменение

    IF p_balance >= p_amount THEN
        p_balance := p_balance - p_amount; -- Локальное изменение
    ELSE
        RAISE_APPLICATION_ERROR(-20001, 'Недостаточно средств');
    END IF;

    p_status := 'SUCCESS';
END;

Если у клиента на балансе 100 долл., а он пытается списать 500 долл., сработает исключение. Несмотря на то, что первой строкой процедура присвоила p_status := 'PROCESSING', в вызывающем коде переменная статуса останется NULL (или сохранит свое старое значение), а баланс останется равным 100. Фаза Copy-Out не состоялась из-за ошибки.

Влияние NOCOPY на режимы OUT и IN OUT

Механизм Copy-In / Copy-Out безопасен, но потребляет ресурсы. Если через IN OUT передается коллекция из миллиона записей, копирование туда и обратно займет существенное время и память.

Использование хинта компилятора NOCOPY заставляет Oracle попытаться передать OUT или IN OUT параметр по ссылке, аналогично IN-параметрам.

CREATE OR REPLACE PROCEDURE process_large_data (
    p_data IN OUT NOCOPY t_large_collection
) IS ...

Однако NOCOPY меняет семантику обработки ошибок. При передаче по ссылке процедура работает напрямую с оригинальной переменной. Если возникает исключение на середине обработки, все изменения, уже примененные к p_data, сохранятся в вызывающем коде. Защитный механизм отката переменных перестает работать. Разработчик должен самостоятельно гарантировать консистентность данных при использовании NOCOPY.

Проблема псевдонимизации (Aliasing)

Псевдонимизация возникает, когда одна и та же переменная передается в качестве фактического параметра двум разным формальным параметрам, и хотя бы один из них имеет режим OUT или IN OUT.

Рассмотрим синтетический пример:

CREATE OR REPLACE PROCEDURE math_operation (
    p_val1 IN OUT NUMBER,
    p_val2 IN OUT NUMBER
) IS
BEGIN
    p_val1 := p_val1 + 10;
    p_val2 := p_val2 * 2;
END;

Если вызвать эту процедуру, передав разные переменные, результат предсказуем. Но что произойдет, если передать одну и ту же переменную?

DECLARE
    v_shared_var NUMBER := 5;
BEGIN
    math_operation(v_shared_var, v_shared_var);
    DBMS_OUTPUT.PUT_LINE(v_shared_var);
END;

Внутри процедуры p_val1 и p_val2 — это две независимые локальные копии (из-за механизма Copy-In).

1. p_val1 получает значение 5, становится 15.
2. p_val2 получает значение 5, становится 10.

При успешном завершении Oracle выполняет Copy-Out. Но в каком порядке он скопирует значения обратно в v_shared_var? Сначала 15, а поверх него 10? Или наоборот? Согласно документации Oracle, порядок возврата значений при псевдонимизации не определен (undefined behavior). Результат может меняться в зависимости от версии СУБД или уровня оптимизации компилятора. Передача одной переменной в несколько OUT/IN OUT параметров является серьезной архитектурной ошибкой.

Ограничения типов данных в параметрах

При объявлении формальных параметров (любого режима) запрещено указывать ограничения размера, точности или масштаба. Компилятор должен знать только базовый тип.

❌ Ошибка:

CREATE OR REPLACE PROCEDURE update_status (
    p_new_status IN VARCHAR2(20) -- Ошибка компиляции
)

✅ Правильно:

CREATE OR REPLACE PROCEDURE update_status (
    p_new_status IN VARCHAR2
)

Размер формального параметра динамически подстраивается под размер переданного фактического параметра при каждом вызове. Если необходимо жестко ограничить длину входящих данных на уровне заголовка процедуры (а не проверять внутри тела), следует использовать привязку %TYPE к столбцу таблицы с нужной размерностью или создать пользовательский подтип (SUBTYPE), который инкапсулирует ограничение.

Взаимодействие режимов с методами вызова

Наличие значений по умолчанию (DEFAULT) у IN-параметров делает смешанную нотацию вызова особенно полезной. Если процедура имеет множество параметров, часть из которых обязательные IN, часть OUT, а часть IN с дефолтными значениями, позиционная передача может стать нечитаемой.

CREATE OR REPLACE PROCEDURE create_user (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2,
    p_role     IN VARCHAR2 DEFAULT 'GUEST',
    p_quota    IN NUMBER DEFAULT 100,
    p_user_id  OUT NUMBER
) IS ...

Если требуется создать пользователя, оставив роль по умолчанию, но изменив квоту, позиционная нотация заставит нас явно прописывать дефолтное значение для роли, чтобы добраться до квоты. Именованная нотация решает эту проблему элегантно:

DECLARE
    v_new_id NUMBER;
BEGIN
    create_user(
        p_username => 'jdoe',
        p_password => 'secure123',
        p_quota    => 500,
        p_user_id  => v_new_id
    );
END;

Параметр p_role был пропущен, и PL/SQL Engine безопасно применил к нему значение 'GUEST'. Важно помнить, что OUT и IN OUT параметры не могут иметь секцию DEFAULT, поэтому при вызове они должны быть указаны всегда, независимо от выбранной нотации.

Контроль над режимами параметров — это инструмент проектирования интерфейсов подпрограмм. IN обеспечивает изоляцию и безопасность входящих данных, OUT формирует четкий канал возврата результатов, защищенный от частичных сбоев механизмом Copy-Out, а IN OUT предоставляет контролируемую среду для модификации состояния. Понимание разницы между ссылочной передачей и копированием значений позволяет проектировать процедуры, которые не только эффективны с точки зрения потребления памяти, но и устойчивы к непредвиденным ошибкам времени выполнения.

SQL — это декларативный язык: мы описываем, какой результат хотим получить, а оптимизатор базы данных сам строит маршрут. PL/SQL возвращает нас в мир императивного программирования, где разработчик берет управление на себя и детально описывает каждый шаг: куда повернуть, где остановиться, сколько раз повторить действие. Без механизмов ветвления и циклов любой блок кода превращается в прямолинейный скрипт, не способный реагировать на данные.

Сложность управления потоком в PL/SQL заключается не в синтаксисе IF или LOOP, а в том, что этот код работает внутри базы данных. Здесь переменные могут принимать значение NULL, что ломает привычную бинарную логику, а неправильно спроектированный цикл способен парализовать сервер из-за миллионов переключений контекста.

Ветвления и коварство трехзначной логики

Базовая конструкция IF-THEN-ELSIF-ELSE в PL/SQL синтаксически похожа на аналоги из других языков. Она последовательно вычисляет условия и выполняет первый блок, где условие истинно.

DECLARE
    v_credit_score NUMBER := 750;
    v_status VARCHAR2(20);
BEGIN
    IF v_credit_score >= 800 THEN
        v_status := 'Premium';
    ELSIF v_credit_score >= 600 THEN
        v_status := 'Standard';
    ELSE
        v_status := 'High Risk';
    END IF;

    DBMS_OUTPUT.PUT_LINE('Статус: ' || v_status);
END;

Главная ловушка кроется в механизме оценки условий. В PL/SQL логические выражения вычисляются в парадигме трехзначной логики (Three-valued logic). Результатом сравнения может быть TRUE, FALSE или NULL (неизвестно).

Конструкция IF передает управление внутрь блока THEN только в том случае, если условие строго равно TRUE. Если условие равно FALSE или NULL, управление передается в блок ELSE (или к следующему ELSIF).

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

DECLARE
    v_purchase_amount NUMBER := 5000;
    v_credit_limit NUMBER := NULL; -- Лимит не установлен (например, новый клиент)
BEGIN
    IF v_purchase_amount > v_credit_limit THEN
        DBMS_OUTPUT.PUT_LINE('Отказ: превышен лимит.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Одобрено: транзакция разрешена.');
    END IF;
END;

С точки зрения бизнес-логики, если лимит не установлен, транзакцию на 5000 одобрять нельзя. Но выражение 5000 > NULL возвращает NULL. PL/SQL интерпретирует это как «не TRUE» и переходит в ветку ELSE. Транзакция ошибочно одобряется.

Чтобы избежать таких ситуаций, необходимо явно обрабатывать пустые значения с помощью функции NVL или оператора IS NULL:

IF v_credit_limit IS NULL OR v_purchase_amount > v_credit_limit THEN
    DBMS_OUTPUT.PUT_LINE('Отказ: лимит не установлен или превышен.');
ELSE
    DBMS_OUTPUT.PUT_LINE('Одобрено.');
END IF;

В данном случае используется короткое замыкание (short-circuit evaluation): если v_credit_limit IS NULL равно TRUE, вторая часть условия после OR даже не вычисляется, и код безопасно переходит в ветку отказа.

CASE: Оператор против Выражения

Когда количество условий ELSIF разрастается, код становится трудночитаемым. Для множественного ветвления используется CASE. В PL/SQL существует два принципиально разных понятия: CASE-оператор (statement) и CASE-выражение (expression). Их часто путают, что приводит к синтаксическим ошибкам.

CASE-оператор (Statement)

Это управляющая конструкция, которая заменяет IF-THEN-ELSIF. Она управляет потоком выполнения и выполняет действия. Завершается конструкцией END CASE;.

Существует в двух вариантах:

1. Простой CASE — сравнивает одно выражение со списком значений.
2. Поисковый (Searched) CASE — вычисляет независимые логические условия.

Пример поискового CASE-оператора:

DECLARE
    v_order_total NUMBER := 1500;
    v_customer_tier VARCHAR2(10) := 'GOLD';
BEGIN
    CASE
        WHEN v_customer_tier = 'VIP' THEN
            DBMS_OUTPUT.PUT_LINE('Скидка 20%');
            -- Здесь может быть вызов другой процедуры
        WHEN v_order_total > 1000 THEN
            DBMS_OUTPUT.PUT_LINE('Скидка 10% за объем');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Базовая цена');
    END CASE;
END;

Если ни одно условие не выполнилось и ветка ELSE отсутствует, PL/SQL сгенерирует предопределенное исключение CASE_NOT_FOUND. В отличие от IF, где отсутствие ELSE просто приводит к выходу из конструкции, CASE-оператор требует строгого покрытия всех вариантов.

CASE-выражение (Expression)

Выражение не управляет потоком выполнения блоков кода, оно возвращает единичное значение. Его можно использовать внутри SQL-запросов, при присвоении значений переменным или как параметр функции. Завершается словом END (без CASE;).

DECLARE
    v_status_code NUMBER := 2;
    v_status_desc VARCHAR2(50);
BEGIN
    -- Присвоение результата CASE-выражения переменной
    v_status_desc := CASE v_status_code
                        WHEN 1 THEN 'Создан'
                        WHEN 2 THEN 'В обработке'
                        WHEN 3 THEN 'Завершен'
                        ELSE 'Неизвестный статус'
                     END;

    DBMS_OUTPUT.PUT_LINE(v_status_desc);
END;

Если в CASE-выражении нет ветки ELSE и совпадений не найдено, оно безопасно вернет NULL, исключение выброшено не будет.

Механизмы циклов: от простых к управляемым

Циклы в PL/SQL делятся на три категории: базовый LOOP, WHILE и FOR. Выбор конкретного типа зависит от того, известно ли заранее количество итераций и где должна находиться точка проверки условия выхода.

Базовый LOOP (Бесконечный цикл)

Конструкция LOOP ... END LOOP; создает цикл, который будет выполняться бесконечно, пока не встретит команду принудительного выхода. Это аналог конструкции do-while из других языков: тело цикла гарантированно выполнится хотя бы один раз.

Для выхода используется оператор EXIT (безусловный выход) или EXIT WHEN (выход по условию).

DECLARE
    v_attempt_count NUMBER := 0;
    v_max_attempts CONSTANT NUMBER := 3;
    v_success BOOLEAN := FALSE;
BEGIN
    LOOP
        v_attempt_count := v_attempt_count + 1;
        DBMS_OUTPUT.PUT_LINE('Попытка подключения № ' || v_attempt_count);

        -- Имитация успешного подключения на 2-й попытке
        IF v_attempt_count = 2 THEN
            v_success := TRUE;
        END IF;

        -- Выход, если подключились ИЛИ исчерпали попытки
        EXIT WHEN v_success = TRUE OR v_attempt_count >= v_max_attempts;

        -- Имитация паузы (экспоненциальная задержка)
        DBMS_SESSION.SLEEP(v_attempt_count);
    END LOOP;

    IF v_success THEN
        DBMS_OUTPUT.PUT_LINE('Подключение установлено.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Ошибка: превышен лимит попыток.');
    END IF;
END;

Базовый цикл идеален для сценариев поллинга (опроса состояния), повторных попыток (retry mechanisms) или чтения данных, когда условие завершения формируется только внутри самого цикла.

Цикл WHILE

Цикл WHILE condition LOOP ... END LOOP; проверяет условие до входа в тело цикла. Если при первой проверке условие равно FALSE или NULL, код внутри цикла не выполнится ни разу.

DECLARE
    v_balance NUMBER := 5000;
    v_withdrawal NUMBER := 1500;
BEGIN
    WHILE v_balance >= v_withdrawal LOOP
        v_balance := v_balance - v_withdrawal;
        DBMS_OUTPUT.PUT_LINE('Снято: ' || v_withdrawal || '. Остаток: ' || v_balance);
    END LOOP;
END;

Важная деталь: переменные, участвующие в условии WHILE, должны быть инициализированы до начала цикла. Если v_balance изначально равен NULL, цикл будет проигнорирован.

Числовой цикл FOR

Если количество итераций известно заранее, используется цикл FOR. Его синтаксис скрывает в себе несколько жестких внутренних правил, о которых разработчики часто забывают.

BEGIN
    FOR i IN 1 .. 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Итерация: ' || i);
    END LOOP;
END;

Особенности числового FOR:

1. Неявное объявление индекса: Переменную i не нужно объявлять в секции DECLARE. PL/SQL автоматически создает ее как переменную типа PLS_INTEGER исключительно для области видимости этого цикла. Как только цикл завершается, переменная i уничтожается.
2. Индекс доступен только для чтения: Внутри тела цикла нельзя написать i := i + 1;. Индекс защищен от модификации компилятором. Это гарантирует, что цикл выполнится ровно заданное количество раз без побочных эффектов.
3. Вычисление границ: Границы цикла (в примере 1 и 5) вычисляются ровно один раз перед стартом. Если вместо чисел используются переменные, и их значения меняются внутри цикла, это никак не повлияет на количество итераций.
4. Порядок границ: Нижняя граница всегда должна быть слева, верхняя — справа. Если написать FOR i IN 5 .. 1, цикл не выдаст ошибку, но не выполнится ни разу, так как стартовое значение сразу больше конечного.

Для обратного отсчета используется ключевое слово REVERSE. При этом границы всё равно записываются по возрастанию:

BEGIN
    -- Индекс будет принимать значения 5, 4, 3, 2, 1
    FOR i IN REVERSE 1 .. 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Обратный отсчет: ' || i);
    END LOOP;
END;

Управление потоком в сложных структурах

В реальных бизнес-процессах циклы часто вкладываются друг в друга. Например, внешний цикл перебирает отделы компании, а внутренний — сотрудников внутри отдела. Управление выходом из таких матрешек требует специальных инструментов.

Метки (Labels)

Метка в PL/SQL — это идентификатор, заключенный в двойные угловые скобки <<имя_метки>>. Метки можно ставить перед циклами или блоками кода, чтобы явно указывать, к какому уровню относится команда выхода.

DECLARE
    v_matrix_found BOOLEAN := FALSE;
BEGIN
    <<outer_loop>>
    FOR i IN 1..3 LOOP
        <<inner_loop>>
        FOR j IN 1..3 LOOP
            DBMS_OUTPUT.PUT_LINE('Проверка ячейки [' || i || ',' || j || ']');

            IF i = 2 AND j = 2 THEN
                DBMS_OUTPUT.PUT_LINE('Целевое значение найдено!');
                -- Выход сразу из внешнего цикла
                EXIT outer_loop;
            END IF;
        END LOOP inner_loop;
    END LOOP outer_loop;
END;

Если бы в этом примере использовался просто EXIT; без указания метки, прервался бы только inner_loop. Внешний цикл продолжил бы работу, перейдя к i = 3, что привело бы к лишним вычислениям. Указание имени метки после END LOOP (например, END LOOP inner_loop;) не обязательно, но считается хорошим тоном для повышения читаемости длинного кода.

Оператор CONTINUE

До версии Oracle 11g в PL/SQL не было оператора для пропуска текущей итерации и перехода к следующей. Разработчикам приходилось оборачивать всё тело цикла в массивный IF или использовать нерекомендуемый оператор безусловного перехода GOTO.

Сейчас доступен оператор CONTINUE и его условная форма CONTINUE WHEN.

BEGIN
    FOR i IN 1..10 LOOP
        -- Пропускаем четные числа
        CONTINUE WHEN MOD(i, 2) = 0;

        DBMS_OUTPUT.PUT_LINE('Обработка нечетного числа: ' || i);
        -- Здесь может быть сложная логика обработки
    END LOOP;
END;

Как и EXIT, оператор CONTINUE может работать с метками внешних циклов (CONTINUE outer_loop WHEN ...), что позволяет гибко управлять сложными алгоритмами парсинга или обработки многомерных массивов.

Архитектурные границы циклов

Изучив механику работы циклов, важно понимать их место в архитектуре базы данных. Главный антипаттерн PL/SQL-разработки, который Том Кайт (известный эксперт Oracle) назвал «Row-by-Row is Slow-by-Slow» (строка за строкой — это медленно), заключается в использовании циклов для построчной модификации данных.

Если внутри цикла FOR на 10 000 итераций поместить оператор INSERT или UPDATE, произойдет 10 000 переключений контекста (context switches) между PL/SQL Engine (который крутит цикл) и SQL Engine (который выполняет вставку). Это катастрофически снижает производительность.

Циклы в PL/SQL предназначены для:

• Сложной алгоритмической логики, которую невозможно выразить средствами SQL (например, генерация криптографических хешей, сложный парсинг текста).
• Управления внешними вызовами (отправка email, вызов REST API с паузами).
• Управления порционной обработкой данных (когда данные забираются и сохраняются крупными чанками, а не по одной строке).

Для массовой работы с таблицами обычные циклы уступают место специализированным механизмам массовой привязки, которые позволяют передавать массивы данных между движками за одно переключение контекста. Понимание базовой логики циклов — это фундамент, без которого невозможно освоить эти продвинутые техники оптимизации.

Интеграция SQL: работа с DML-операциями и явными курсорами

Каждый раз, когда в коде PL/SQL выполняется простейший запрос SELECT или операция UPDATE, ядро базы данных незаметно для разработчика создает курсор. В Oracle не существует «бескурсорного» выполнения SQL-команд. Разница заключается лишь в том, кто управляет выделенной областью памяти: сама система в автоматическом режиме или разработчик, явно контролирующий каждый шаг обработки данных. Понимание механизмов взаимодействия процедурного кода с SQL-движком определяет, насколько предсказуемо и безопасно будет работать бизнес-логика при обработке как единичных записей, так и массивов данных.

Неявные курсоры и атрибуты DML-операций

Когда в блоке PL/SQL выполняется любая DML-операция (INSERT, UPDATE, DELETE, MERGE) или запрос, возвращающий строго одну строку (SELECT INTO), Oracle использует неявный курсор (implicit cursor). Разработчик не объявляет его, не открывает и не закрывает — эти фазы жизненного цикла ядро СУБД берет на себя.

Однако процедурному коду часто требуется обратная связь от SQL-движка. Например, необходимо узнать, сколько строк было обновлено, или проверить, удалилась ли целевая запись. Для этого используется глобальная структура SQL, предоставляющая доступ к атрибутам последнего выполненного неявного курсора.

Ключевые атрибуты неявного курсора:

• SQL%ROWCOUNT — возвращает количество строк, затронутых последней DML-операцией (целое число $N \geq 0$).
• SQL%FOUND — возвращает логическое значение TRUE, если операция затронула хотя бы одну строку ($N > 0$).
• SQL%NOTFOUND — возвращает TRUE, если ни одна строка не была изменена или найдена ($N = 0$).
• SQL%ISOPEN — для неявных курсоров всегда возвращает FALSE, так как Oracle закрывает их мгновенно после выполнения.

Атрибуты привязаны к сессии и перезаписываются при выполнении следующей SQL-команды. Поэтому считывать их нужно немедленно.

DECLARE
    v_target_department NUMBER := 50;
    v_bonus_amount      NUMBER := 500;
BEGIN
    UPDATE employees
    SET salary = salary + v_bonus_amount
    WHERE department_id = v_target_department;

    -- Проверка результата немедленно после DML
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Сотрудники в отделе не найдены. Обновление не выполнено.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Начислен бонус. Обновлено сотрудников: ' || SQL%ROWCOUNT);
    END IF;
END;

Особое внимание требует конструкция SELECT INTO. В отличие от DML-операций, которые могут спокойно обработать ноль строк (просто ничего не обновив), SELECT INTO ожидает строго одну строку. Если запрос не находит данных, генерируется исключение NO_DATA_FOUND. Если запрос возвращает более одной строки, возникает исключение TOO_MANY_ROWS. В обоих случаях нормальный поток выполнения прерывается, и управление передается в секцию EXCEPTION. Использовать атрибут SQL%NOTFOUND после SELECT INTO бессмысленно — код до этой проверки просто не дойдет.

Анатомия и жизненный цикл явного курсора

Когда бизнес-логика требует построчной обработки набора данных (активного множества), состоящего из нескольких строк, неявного курсора недостаточно. Разработчик должен взять управление памятью на себя, используя явный курсор (explicit cursor).

Явный курсор — это именованный указатель на частную область памяти SQL (Private SQL Area), в которой хранится результат выполнения запроса. Работа с ним состоит из четырех обязательных этапов.

1. DECLARE (Объявление). Курсор определяется в секции объявлений. На этом этапе парсится SQL-запрос, но данные еще не извлекаются.
2. OPEN (Открытие). Выполняется в секции BEGIN. Oracle связывает переменные (bind variables), выполняет запрос, определяет активное множество строк и устанавливает внутренний указатель перед первой строкой.
3. FETCH (Извлечение). Считывание текущей строки в переменные PL/SQL и сдвиг указателя на следующую позицию. Обычно выполняется в цикле.
4. CLOSE (Закрытие). Освобождение ресурсов памяти. Если курсор не закрыть, он останется в памяти до завершения сессии, что в высоконагруженных системах приводит к утечкам памяти (ошибка ORA-01000: maximum open cursors exceeded).

Классическая реализация обработки данных через явный курсор выглядит следующим образом:

DECLARE
    -- 1. Объявление курсора
    CURSOR c_high_earners IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE salary > 10000;

    -- Переменная для хранения строки (используем %ROWTYPE курсора)
    v_emp_record c_high_earners%ROWTYPE;
BEGIN
    -- 2. Открытие курсора
    OPEN c_high_earners;

    LOOP
        -- 3. Извлечение данных
        FETCH c_high_earners INTO v_emp_record;

        -- Проверка достижения конца выборки
        EXIT WHEN c_high_earners%NOTFOUND;

        -- Обработка извлеченной строки
        DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name || ' получает ' || v_emp_record.salary);
    END LOOP;

    -- 4. Закрытие курсора
    CLOSE c_high_earners;
END;

Ловушка позиционирования EXIT WHEN

В приведенном примере проверка EXIT WHEN c_high_earners%NOTFOUND; стоит сразу после команды FETCH. Это критически важное правило.

Если поместить проверку в конец цикла (после обработки данных), возникнет классическая логическая ошибка: последняя строка активного множества будет обработана дважды. Когда FETCH пытается прочитать данные после последней строки, он не очищает переменные v_emp_record — в них остаются данные от предыдущего успешного чтения. Атрибут %NOTFOUND становится TRUE, но если обработка стоит до проверки, бизнес-логика отработает со старыми данными еще раз.

Атрибуты явного курсора

Явные курсоры имеют те же атрибуты, что и неявные, но применяются к имени конкретного курсора и имеют иную семантику:

• cursor_name%ISOPEN — позволяет проверить, открыт ли курсор, прежде чем пытаться его открыть (попытка открыть открытый курсор вызовет ошибку).
• cursor_name%ROWCOUNT — возвращает количество строк, уже извлеченных командой FETCH на данный момент времени. Сразу после OPEN он равен нулю. Он не показывает общее количество строк в запросе, пока курсор не будет вычитан до конца.

Курсорный цикл FOR (Cursor FOR Loop)

Ручное управление курсором (объявление переменных, открытие, цикл, проверка, закрытие) требует написания большого объема шаблонного кода. В PL/SQL существует элегантная конструкция — курсорный цикл FOR, которая берет на себя всю рутину управления состоянием.

Курсорный цикл неявно выполняет следующие действия:

1. Создает запись (record) с типом %ROWTYPE на основе структуры курсора.
2. Открывает курсор.
3. Выполняет FETCH на каждой итерации.
4. Автоматически прерывает цикл, когда данные заканчиваются.
5. Гарантированно закрывает курсор (даже если внутри цикла произошло исключение или был вызван досрочный выход через EXIT или метку).

Сравним подходы при решении одной и той же задачи:

Ручное управление	Курсорный цикл FOR
Требуется объявление переменной v_rec cursor_name%ROWTYPE;	Переменная-индекс создается неявно, доступна только внутри цикла.
Явные команды OPEN, FETCH, CLOSE.	Отсутствуют. Движок управляет ими сам.
Риск оставить курсор открытым при генерации исключения.	Абсолютная безопасность: курсор закроется при любом выходе из области видимости.
Требуется ручная проверка %NOTFOUND.	Выход происходит автоматически.

Код с использованием курсорного цикла становится лаконичным:

DECLARE
    CURSOR c_departments IS
        SELECT department_id, department_name
        FROM departments
        WHERE location_id = 1700;
BEGIN
    -- Итератор v_dept объявляется неявно
    FOR v_dept IN c_departments LOOP
        DBMS_OUTPUT.PUT_LINE('Отдел: ' || v_dept.department_name);
    END LOOP;
END;

Более того, PL/SQL позволяет использовать курсорный цикл вообще без явного объявления курсора в секции DECLARE, встраивая SQL-запрос прямо в заголовок цикла (инлайн-курсор):

BEGIN
    FOR v_emp IN (SELECT first_name, last_name FROM employees WHERE manager_id = 100) LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name);
    END LOOP;
END;

Этот подход идеален для коротких, одноразовых выборок, где запрос не планируется переиспользовать в других частях процедуры.

Параметризованные курсоры

В разработке часто возникает потребность использовать один и тот же курсор для разных входных данных. Написание отдельных курсоров для каждого случая нарушает принцип DRY (Don't Repeat Yourself). PL/SQL поддерживает передачу параметров в явные курсоры.

Параметры курсора объявляются в скобках после его имени, аналогично параметрам процедур. При этом можно указывать только тип данных, без размерности (например, VARCHAR2, но не VARCHAR2(50)). Режим передачи всегда IN.

Параметризация не только сокращает код, но и способствует повторному использованию планов выполнения в SQL-движке, так как параметры курсора автоматически транслируются в переменные связывания (bind variables) при передаче запроса.

DECLARE
    -- Курсор принимает идентификатор должности как параметр
    CURSOR c_employees_by_job (p_job_id VARCHAR2) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE job_id = p_job_id;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Программисты ---');
    FOR v_emp IN c_employees_by_job('IT_PROG') LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('--- Менеджеры ---');
    FOR v_emp IN c_employees_by_job('SA_MAN') LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary);
    END LOOP;
END;

Блокировка строк: FOR UPDATE и WHERE CURRENT OF

В многопользовательской среде возникает проблема конкурентного доступа. Если процедура считывает строку через курсор, анализирует ее сложной бизнес-логикой в течение нескольких секунд, а затем пытается обновить, существует риск, что другая транзакция уже изменила эту строку. Это приводит к потере обновлений (lost update) или логическим несоответствиям.

Для обеспечения целостности данных применяется пессимистичная блокировка на уровне строк. В запрос курсора добавляется конструкция FOR UPDATE. При выполнении OPEN Oracle немедленно блокирует все строки, попавшие в активное множество. Другие сессии смогут прочитать эти данные, но любая попытка их изменить (через UPDATE или другой FOR UPDATE) будет приостановлена до тех пор, пока текущая транзакция не завершится (через COMMIT или ROLLBACK).

DECLARE
    CURSOR c_pending_orders IS
        SELECT order_id, status, total_amount
        FROM orders
        WHERE status = 'PENDING'
        FOR UPDATE; -- Блокировка строк при открытии курсора

Если строки уже заблокированы другой транзакцией, выполнение OPEN зависнет в ожидании снятия блокировки. Чтобы избежать бесконечного зависания процедуры, используют опции NOWAIT (мгновенная генерация ошибки ORA-00054, если ресурс занят) или WAIT n (ожидание $n$ секунд перед генерацией ошибки).

Когда строки надежно заблокированы, процедура может безопасно их обновлять. Чтобы не писать громоздкие условия WHERE order_id = v_order.order_id и не заставлять SQL-движок заново искать нужную строку по индексу, используется конструкция WHERE CURRENT OF. Она указывает ядру обновить именно ту физическую строку (по ее ROWID), на которую в данный момент указывает курсор после последнего FETCH.

DECLARE
    CURSOR c_inventory IS
        SELECT product_id, stock_qty, reserved_qty
        FROM inventory
        WHERE status = 'ACTIVE'
        FOR UPDATE OF stock_qty NOWAIT;

    v_rec c_inventory%ROWTYPE;
BEGIN
    OPEN c_inventory;
    LOOP
        FETCH c_inventory INTO v_rec;
        EXIT WHEN c_inventory%NOTFOUND;

        -- Сложная бизнес-логика пересчета остатков
        IF v_rec.stock_qty < v_rec.reserved_qty THEN
            -- Обновление текущей строки, на которую указывает курсор
            UPDATE inventory
            SET stock_qty = reserved_qty,
                last_audit_date = SYSDATE
            WHERE CURRENT OF c_inventory;
        END IF;
    END LOOP;
    CLOSE c_inventory;

    COMMIT; -- Снятие всех блокировок
END;

Использование связки FOR UPDATE и WHERE CURRENT OF гарантирует транзакционную безопасность при построечной обработке и исключает аномалии конкурентного доступа. Однако важно помнить, что блокировки удерживаются до конца транзакции, а не до закрытия курсора.

Уверенное владение явными курсорами дает разработчику точечный контроль над потоком данных между SQL и PL/SQL. Переход от декларативного мышления (где SQL оперирует множествами целиком) к процедурному (где логика применяется к каждой строке индивидуально) открывает возможности для реализации алгоритмов любой сложности. При этом всегда следует оценивать архитектурный компромисс: построчная обработка неизбежно увеличивает количество переключений контекста. Если логику модификации можно выразить одним сложным SQL-запросом, это всегда будет быстрее. Курсоры вступают в игру там, где выразительности чистого SQL становится недостаточно для реализации многошаговых, ветвящихся бизнес-процессов.

Обработка исключений: системные ошибки и механизм PRAGMA EXCEPTION_INIT

Представьте ситуацию: ваша процедура перевода денежных средств между счетами успешно списала сумму с одного баланса, но при попытке зачисления на другой возникла ошибка нарушения целостности (например, счет получателя был заблокирован или удален в параллельной сессии). Если этот сбой не будет корректно перехвачен и обработан, транзакция может остаться в неопределенном состоянии, а данные — потерять консистентность. В PL/SQL исключение — это не просто «ошибка», это механизм передачи управления от места возникновения проблемы к блоку логики, который знает, как на эту проблему реагировать.

Природа исключений и иерархия обработки

Исключение в Oracle возникает всякий раз, когда нормальный ход выполнения программы становится невозможным. Это может быть вызвано как внутренними ошибками СУБД (нехватка памяти, нарушение ограничений таблицы), так и явным сигналом из кода разработчика.

Когда в блоке BEGIN ... END возникает ошибка, выполнение текущей инструкции немедленно прекращается. Система ищет секцию EXCEPTION. Если она найдена и в ней есть соответствующий обработчик (блок WHEN), управление передается туда. Если обработчика нет или секция EXCEPTION отсутствует вовсе, исключение «всплывает» (propagates) выше — в вызывающий блок или клиентское приложение.

Важно понимать, что при возникновении исключения все переменные PL/SQL сохраняют свои текущие значения (если только не использовался хинт NOCOPY для параметров), но SQL-операции внутри блока могут быть либо автоматически откачены до неявной точки сохранения, либо требовать явного ROLLBACK в секции обработки.

Системные исключения: предопределенные и неименованные

Oracle делит все системные ошибки на две категории. Понимание разницы между ними критично для написания чистого кода.

Предопределенные исключения (Predefined Exceptions)

Для самых частых ошибок (около 20–25 ситуаций) Oracle зарезервировал специальные имена в пакете STANDARD. Вам не нужно их объявлять, они доступны «из коробки».

Наиболее распространенные из них:

• NO_DATA_FOUND: возникает, когда SELECT INTO не возвращает ни одной строки.
• TOO_MANY_ROWS: возникает, когда SELECT INTO возвращает более одной строки.
• ZERO_DIVIDE: попытка деления на ноль.
• VALUE_ERROR: ошибка арифметики, преобразования типов или превышение длины строки.
• INVALID_NUMBER: неудачная попытка преобразования строки в число в SQL-запросе.
• DUP_VAL_ON_INDEX: попытка вставить дублирующее значение в колонку с уникальным индексом.

Использование этих имен делает код читаемым. Вместо проверки кода ошибки $-1$ мы пишем WHEN DUP_VAL_ON_INDEX THEN.

Неименованные системные ошибки

В СУБД Oracle существуют тысячи кодов ошибок (ORA-XXXXX), но для большинства из них нет встроенных имен в PL/SQL. Например, ошибка ORA-02292 (нарушение ограничения целостности — обнаружен дочерний запрос) не имеет красивого имени «Integrity_Constraint_Violated».

Если вы попытаетесь перехватить такую ошибку, у вас есть два пути:

1. Использовать универсальный обработчик WHEN OTHERS. Это считается «плохим тоном», если вы не логируете ошибку и не пробрасываете её дальше, так как OTHERS «проглатывает» абсолютно всё, включая критические системные сбои.
2. Использовать механизм PRAGMA EXCEPTION_INIT для связывания кода ошибки с вашим собственным именем.

Механизм PRAGMA EXCEPTION_INIT

Директива компилятора (прагма) EXCEPTION_INIT позволяет назначить имя любому коду ошибки Oracle. Это превращает «магическое число» в осмысленную переменную, которую можно использовать в секции EXCEPTION.

Синтаксис связывания выглядит так:

1. Объявление имени исключения в секции DECLARE (тип данных EXCEPTION).
2. Привязка имени к коду через PRAGMA EXCEPTION_INIT(имя, код_ошибки).

Рассмотрим пример, когда нам нужно обработать попытку удаления записи, на которую ссылаются другие таблицы (ошибка ORA-02292).

CREATE OR REPLACE PROCEDURE delete_department(p_dept_id NUMBER) IS
    -- 1. Объявляем имя для исключения
    e_child_records_found EXCEPTION;

    -- 2. Связываем имя с кодом ORA-02292
    -- Код указывается как отрицательное число
    PRAGMA EXCEPTION_INIT(e_child_records_found, -2292);
BEGIN
    DELETE FROM departments WHERE department_id = p_dept_id;
    COMMIT;
EXCEPTION
    WHEN e_child_records_found THEN
        -- Логика обработки: например, логирование и уведомление
        DBMS_OUTPUT.PUT_LINE('Ошибка: Нельзя удалить отдел ' || p_dept_id ||
                             ', так как в нем числятся сотрудники.');
        ROLLBACK;
    WHEN OTHERS THEN
        -- Все остальные непредвиденные ошибки
        ROLLBACK;
        RAISE; -- Пробрасываем ошибку дальше
END;

Нюансы использования PRAGMA

• Диапазон кодов: Вы можете связывать любые коды ошибок Oracle, обычно в диапазоне от $-1$ до $-20000$ (кроме $-1403$, который соответствует NO_DATA_FOUND).
• Местоположение: Прагма должна следовать сразу за объявлением переменной типа EXCEPTION в той же секции объявлений.
• Область видимости: Как и переменные, именованные таким образом исключения подчиняются правилам области видимости. Если вы объявили e_lock_timeout внутри процедуры, вы не сможете перехватить его в вызывающем блоке по имени, только по коду.

Функции SQLCODE и SQLERRM

Внутри обработчика WHEN OTHERS (или любого другого) нам часто нужно знать, что именно произошло. Для этого служат две встроенные функции:

1. $SQLCODE$: возвращает числовой код последней ошибки.

   • Для предопределенных исключений это обычно отрицательное число (например, $-1$ для DUP_VAL_ON_INDEX).
   • Исключение: для NO_DATA_FOUND $SQLCODE$ возвращает $+100$ (стандарт ANSI), хотя код ошибки в Oracle — ORA-01403.
   • Если ошибки не было, возвращает $0$.

2. $SQLERRM$: возвращает текстовое сообщение об ошибке.

   • Можно передать конкретный код в качестве аргумента: SQLERRM(-2292) вернет текст ошибки нарушения внешнего ключа без возникновения самой ошибки.

Важное ограничение: функции SQLCODE и SQLERRM нельзя использовать напрямую в SQL-запросах. Их значения нужно предварительно присвоить локальным переменным.

EXCEPTION
    WHEN OTHERS THEN
        DECLARE
            v_err_code NUMBER := SQLCODE;
            v_err_msg  VARCHAR2(1000) := SQLERRM;
        BEGIN
            -- Правильно: используем переменные
            INSERT INTO error_log (code, message, log_time)
            VALUES (v_err_code, v_err_msg, SYSDATE);

            -- Ошибка: INSERT INTO ... VALUES (SQLCODE, SQLERRM, ...) вызовет сбой компиляции
        END;
        RAISE;

Распространенные системные ошибки и стратегии их обработки

Разберем несколько критических сценариев, с которыми сталкивается разработчик при автоматизации бизнес-логики.

1. Ошибки блокировок (ORA-00054)

Когда вы используете SELECT ... FOR UPDATE NOWAIT, и строка уже заблокирована другим пользователем, Oracle выбрасывает ошибку ORA-00054: resource busy. Вместо того чтобы аварийно завершать работу, вы можете перехватить её и, например, подождать несколько секунд перед повторной попыткой.

DECLARE
    e_resource_busy EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
    v_retry_count   NUMBER := 0;
BEGIN
    <<attempt_lock>>
    BEGIN
        SELECT salary INTO ... FROM employees WHERE ... FOR UPDATE NOWAIT;
    EXCEPTION
        WHEN e_resource_busy THEN
            IF v_retry_count < 3 THEN
                v_retry_count := v_retry_count + 1;
                DBMS_LOCK.SLEEP(2); -- Пауза (требует прав на DBMS_LOCK)
                GOTO attempt_lock;
            ELSE
                RAISE_APPLICATION_ERROR(-20001, 'Лимит попыток исчерпан. Объект заблокирован.');
            END IF;
    END;
END;

2. Ошибки мутирующих таблиц (ORA-04091)

Эта ошибка часто возникает при работе триггеров, но может проявиться и в сложных процедурах, когда код пытается прочитать таблицу, которая в данный момент изменяется тем же SQL-предложением. Хотя PRAGMA EXCEPTION_INIT позволяет её перехватить, лучшая стратегия здесь — пересмотр архитектуры (например, использование временных таблиц или коллекций), так как состояние данных в момент этой ошибки неопределенно.

3. Ошибки нехватки места (ORA-01652, ORA-01536)

Если во время массовой вставки данных заканчивается место в табличном пространстве, процедура упадет. Обработка таких ошибок полезна в фоновых заданиях (Job), чтобы отправить критическое уведомление администратору БД через UTL_MAIL или записать статус в таблицу мониторинга, прежде чем процесс окончательно остановится.

Распространение исключений (Propagation)

Когда исключение возникает в блоке, выполнение кода в секции BEGIN прекращается. Дальнейшая судьба программы зависит от того, где именно оно возникло.

Исключение в секции DECLARE

Если ошибка происходит на этапе инициализации переменных (например, при попытке присвоить результат SELECT INTO переменной с атрибутом NOT NULL, а запрос вернул NULL), обработчик текущего блока не сможет его перехватить. Исключение немедленно уходит в вызывающий блок.

Исключение в секции EXCEPTION

Если внутри самого обработчика возникает новая ошибка, она также немедленно покидает текущий блок. Это часто случается при попытке логирования в таблицу, у которой закончилось место.

Исключение в подпрограммах

Если процедура $A$ вызывает процедуру $B$, и в $B$ происходит ошибка:

1. Если в $B$ есть EXCEPTION, управление переходит туда. После выполнения обработчика $B$ завершается успешно (с точки зрения $A$), и выполнение в $A$ продолжается со следующей строки.
2. Если в $B$ нет обработчика или он делает RAISE, ошибка передается в $A$. Теперь $A$ должна её обработать.

Использование RAISE_APPLICATION_ERROR

Для системных ошибок Oracle предоставляет коды, но иногда нам нужно прервать выполнение со своим собственным кодом и текстом, который будет понятен фронтенд-приложению. Для этого используется процедура RAISE_APPLICATION_ERROR.

Синтаксис: RAISE_APPLICATION_ERROR(номер_ошибки, сообщение[, сохранить_стек]);

• Номер ошибки: число в диапазоне от $-20000$ до $-20999$. Этот диапазон зарезервирован специально для разработчиков.
• Сообщение: строка до 2048 байт.
• Сохранить стек: булево значение (по умолчанию FALSE). Если TRUE, ваша ошибка добавится к стеку уже возникших ошибок.

Это мощный инструмент для превращения технической ошибки (например, ORA-02291: integrity constraint violated - parent key not found) в бизнес-ошибку (-20005: Указанный идентификатор клиента не существует в системе).

Транзакционный контроль при исключениях

Один из самых тонких моментов — состояние данных после возникновения системной ошибки.

Если исключение возникло внутри SQL-инструкции (например, INSERT в 1000 строк нарушил уникальность на 500-й строке), Oracle автоматически откатывает только эту конкретную инструкцию. Все изменения, сделанные предыдущими командами INSERT/UPDATE в этом же блоке, остаются в буфере (Pending Changes) и ждут COMMIT или ROLLBACK.

Если же вы не обработали исключение, и оно «убило» сессию или вызвало аварийное завершение анонимного блока в SQL*Plus, Oracle может откатить всю транзакцию целиком.

Золотое правило: всегда явно управляйте транзакциями в секции EXCEPTION.

• Если ошибка критична — делайте ROLLBACK.
• Если вы используете PRAGMA AUTONOMOUS_TRANSACTION (разбиралось в предыдущих лекциях), помните, что вы обязаны завершить транзакцию (COMMIT или ROLLBACK) внутри блока, иначе получите ORA-06519: active autonomous transaction detected and rolled back.

Профессиональные рекомендации по структуре обработки

При проектировании хранимых процедур стоит придерживаться иерархического подхода к обработке системных ошибок.

1. Локализация: если какая-то часть кода «склонна» к ошибкам, которые не должны прерывать всю процедуру (например, получение необязательного параметра), оберните её в отдельный вложенный блок BEGIN ... END.
2. Именование: забудьте про магические числа. Если вы знаете, что процедура может столкнуться с ORA-01013 (пользователь отменил операцию) или ORA-00060 (deadlock), объявите их через PRAGMA EXCEPTION_INIT.
3. Минимизация WHEN OTHERS: используйте этот обработчик только на самом верхнем уровне для финального логирования. Никогда не пишите WHEN OTHERS THEN NULL; — это «черная дыра», которая скроет даже критические ошибки компиляции или нехватки ресурсов, превращая отладку в кошмар.
4. Использование стека ошибок: начиная с Oracle 12c, рекомендуется использовать пакет DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, чтобы точно знать строку кода, где возникла системная ошибка, а не просто получать текст сообщения.

Пример комплексного подхода

Рассмотрим процедуру обработки заказа, которая должна учитывать возможные системные сбои.

CREATE OR REPLACE PROCEDURE process_order_v2(p_order_id NUMBER) IS
    e_deadlock EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_deadlock, -60);

    e_fk_violation EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_fk_violation, -2291);

    v_step VARCHAR2(100);
BEGIN
    v_step := 'Проверка наличия товара';
    -- Локальный блок для специфической логики
    BEGIN
        SELECT ... FOR UPDATE WAIT 5;
    EXCEPTION
        WHEN e_deadlock THEN
            -- Логика при взаимной блокировке
            RAISE_APPLICATION_ERROR(-20010, 'Система перегружена, попробуйте позже.');
    END;

    v_step := 'Обновление остатков';
    UPDATE inventory SET quantity = quantity - 1 WHERE ...;

    v_step := 'Создание записи в логе';
    INSERT INTO order_history (order_id, status) VALUES (p_order_id, 'PROCESSED');

EXCEPTION
    WHEN e_fk_violation THEN
        ROLLBACK;
        -- Мы знаем, что это ошибка внешнего ключа (например, нет такого статуса)
        log_error(p_order_id, 'Нарушение ссылочной целостности на шаге: ' || v_step);
        RAISE;

    WHEN OTHERS THEN
        ROLLBACK;
        -- Общий обработчик
        log_error(p_order_id, 'Непредвиденная ошибка (' || SQLCODE || ') на шаге: ' || v_step ||
                              '. Стек: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;
END;

В этом примере использование переменной v_step в сочетании с SQLCODE и FORMAT_ERROR_BACKTRACE дает исчерпывающую информацию для отладки, даже если произойдет редкая системная ошибка, которую мы не предусмотрели явно.

Обработка исключений — это не только защита от сбоев, но и способ управления потоком выполнения. Правильное использование системных кодов и механизма PRAGMA EXCEPTION_INIT превращает процедурный код из хрупкой последовательности команд в надежный промышленный инструмент автоматизации.

Пользовательские исключения и стратегии логирования выполнения

Представьте систему банковских переводов: технически запрос корректен, баланса хватает, счета существуют, но сумма перевода превышает дневной лимит, установленный для конкретного типа аккаунта. С точки зрения СУБД Oracle здесь нет ошибки — ни деления на ноль, ни нарушения целостности данных. Однако с точки зрения бизнеса — это критическая исключительная ситуация. Как элегантно вплести такие проверки в логику хранимых процедур, не превращая код в бесконечные вложенные IF-THEN-ELSE, и как гарантировать, что информация о сбое не исчезнет бесследно?

Природа пользовательских исключений

В отличие от системных ошибок, которые возбуждаются ядром базы данных автоматически, пользовательские исключения (User-defined exceptions) — это инструмент программиста для обработки нарушений бизнес-логики. Они позволяют отделить логику проверки условий от логики обработки последствий.

Работа с пользовательскими исключениями строится по трехэтапной схеме:

1. Объявление в секции DECLARE (или в спецификации пакета).
2. Возбуждение (вызов) с помощью оператора RAISE в секции BEGIN.
3. Обработка в секции EXCEPTION.

Основное преимущество такого подхода — «чистота» основного алгоритма. Вместо того чтобы загромождать бизнес-логику кодами возврата и проверками на каждом шагу, вы просто декларируете: «Если условие нарушено — бросаем исключение». Управление немедленно передается в конец блока, что предотвращает выполнение последующих (теперь уже некорректных) действий.

Объявление и возбуждение: синтаксический протокол

Пользовательское исключение объявляется как переменная особого типа EXCEPTION. В отличие от обычных переменных, исключения не имеют значений и не могут быть переданы в качестве параметров.

CREATE OR REPLACE PROCEDURE process_withdrawal (
    p_account_id IN NUMBER,
    p_amount     IN NUMBER
) IS
    -- 1. Объявление
    e_insufficient_funds EXCEPTION;
    e_limit_exceeded     EXCEPTION;

    v_balance NUMBER;
BEGIN
    SELECT balance INTO v_balance FROM accounts WHERE id = p_account_id;

    -- Логика проверки бизнес-правил
    IF v_balance < p_amount THEN
        -- 2. Возбуждение
        RAISE e_insufficient_funds;
    END IF;

    IF p_amount > 100000 THEN
        RAISE e_limit_exceeded;
    END IF;

    -- Основное действие (выполнится только если проверки пройдены)
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_account_id;

EXCEPTION
    -- 3. Обработка
    WHEN e_insufficient_funds THEN
        -- Здесь может быть логика уведомления пользователя
        dbms_output.put_line('Ошибка: Недостаточно средств на счете.');
    WHEN e_limit_exceeded THEN
        dbms_output.put_line('Ошибка: Превышен разовый лимит перевода.');
END;

Важный нюанс: если пользовательское исключение объявлено внутри процедуры, оно является локальным. Если оно «всплывет» (propagate) за пределы этой процедуры необработанным, вызывающая среда получит стандартный код ORA-06510: PL/SQL: unhandled user-defined exception. Чтобы сделать исключение узнаваемым для внешних систем, необходимо либо использовать RAISE_APPLICATION_ERROR, либо объявлять исключения в спецификациях пакетов.

Глобальные исключения в пакетах

Для крупных систем хорошей практикой считается создание специального пакета «библиотеки исключений». Это позволяет централизованно управлять кодами ошибок и использовать одни и те же исключения в десятках различных процедур.

CREATE OR REPLACE PACKAGE app_exceptions IS
    e_invalid_status EXCEPTION;
    e_access_denied  EXCEPTION;
    -- Мы можем сразу связать их с кодами для внешних приложений
    PRAGMA EXCEPTION_INIT(e_invalid_status, -20001);
    PRAGMA EXCEPTION_INIT(e_access_denied, -20002);
END app_exceptions;

Теперь в любой процедуре вы можете написать RAISE app_exceptions.e_access_denied;. Это избавляет от дублирования кода и делает архитектуру предсказуемой.

Использование RAISE_APPLICATION_ERROR

Этот механизм — мост между вашим кодом и вызывающим приложением (например, на Java или Python). Он позволяет не просто прервать выполнение, но и передать осмысленное сообщение и уникальный числовой код.

Синтаксис: RAISE_APPLICATION_ERROR(error_number, message[, keep_errors]);

• error_number: отрицательное целое число в диапазоне от -20000 до -20999.
• message: строка до 2048 байт.
• keep_errors: булево значение. Если TRUE, новая ошибка добавляется в стек уже имеющихся; если FALSE (по умолчанию) — заменяет стек.

Использование этого метода предпочтительно, когда процедура вызывается из внешнего интерфейса, так как клиентское приложение сможет перехватить конкретный номер ошибки и вывести локализованное сообщение пользователю.

Стратегии логирования: зачем и как?

В промышленной разработке просто «выбросить» ошибку недостаточно. Когда процедура падает в три часа ночи в рамках пакетного задания, разработчику на утро нужны ответы на вопросы:

1. Какая именно ошибка произошла?
2. С какими входными параметрами была вызвана процедура?
3. В какой строке кода возник сбой?
4. Какова была последовательность вызовов (Call Stack)?

Проблема транзакционности логов

Наивный подход к логированию выглядит так:

EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (msg, dt) VALUES (SQLERRM, SYSDATE);
        ROLLBACK; -- Ой! Лог тоже откатился.

Если в блоке происходит ROLLBACK, все записи в таблицу логов, сделанные в рамках этой транзакции, будут стерты. Чтобы этого избежать, логирование всегда должно выноситься в автономную транзакцию.

Реализация автономного логера

Использование PRAGMA AUTONOMOUS_TRANSACTION позволяет процедуре логирования создать собственную транзакцию, которая фиксируется (COMMIT) независимо от того, что происходит в основном коде.

CREATE OR REPLACE PROCEDURE log_error (
    p_proc_name IN VARCHAR2,
    p_params    IN VARCHAR2,
    p_err_code  IN NUMBER,
    p_err_msg   IN VARCHAR2
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO app_error_logs (
        id,
        log_time,
        proc_name,
        params,
        error_code,
        error_message,
        call_stack,
        db_user
    ) VALUES (
        seq_log_id.NEXTVAL,
        SYSTIMESTAMP,
        p_proc_name,
        p_params,
        p_err_code,
        p_err_msg,
        DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, -- Важнейшая функция для отладки
        USER
    );
    COMMIT; -- Фиксируем только запись в лог
END;

Сбор контекста выполнения

Для эффективной отладки недостаточно знать текст ошибки. Профессиональный лог должен содержать «снимок» состояния.

1. Параметры: Всегда логируйте значения входящих аргументов. Это позволит воспроизвести ошибку на тестовом стенде.
2. Backtrace: Функция DBMS_UTILITY.FORMAT_ERROR_BACKTRACE показывает цепочку вызовов с номерами строк до самого места возникновения исключения. Это критически важно, так как обычный SQLERRM показывает только саму ошибку, но не место её рождения.
3. Call Stack: DBMS_UTILITY.FORMAT_CALL_STACK поможет понять, как управление попало в текущую точку (цепочка вызовов процедур).

Профессиональный паттерн обработки и логирования

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

CREATE OR REPLACE PROCEDURE process_order (
    p_order_id IN NUMBER
) IS
    v_params VARCHAR2(4000) := 'p_order_id=' || p_order_id;
    e_order_already_processed EXCEPTION;
    v_status orders.status%TYPE;
BEGIN
    -- Проверка состояния
    SELECT status INTO v_status FROM orders WHERE id = p_order_id;

    IF v_status = 'PROCESSED' THEN
        RAISE e_order_already_processed;
    END IF;

    -- Имитация сложной логики
    UPDATE orders SET status = 'PROCESSED' WHERE id = p_order_id;

    -- Имитация возможной системной ошибки (например, ORA-01476: divisor is equal to zero)
    -- v_status := 1/0;

EXCEPTION
    WHEN e_order_already_processed THEN
        -- Бизнес-исключение: логируем как предупреждение или игнорируем
        log_error('process_order', v_params, -20001, 'Заказ уже обработан');
        RAISE_APPLICATION_ERROR(-20001, 'Повторная обработка заказа невозможна');

    WHEN OTHERS THEN
        -- Системное исключение: логируем всё мясо
        log_error(
            p_proc_name => 'process_order',
            p_params    => v_params,
            p_err_code  => SQLCODE,
            p_err_msg   => SQLERRM
        );
        -- Пробрасываем ошибку дальше, чтобы вызывающая среда знала о сбое
        RAISE;
END;

Иерархия логирования и уровни (Logging Levels)

В сложных системах не стоит логировать всё подряд с одинаковым приоритетом. Обычно выделяют уровни:

• ERROR: Критические сбои, требующие вмешательства.
• WARN: Нарушения бизнес-логики (например, «недостаточно средств»), которые не являются поломкой системы.
• INFO: Ключевые этапы процесса (начало загрузки, завершение этапа).
• DEBUG: Подробные данные для разработчиков, отключаемые в продакшене.

Для реализации такой системы в таблицу логов добавляется поле log_level, а в процедуру логирования — логика фильтрации (например, через глобальную переменную пакета или параметр в таблице настроек).

Типичные антипаттерны

1. "Глотание" исключений (Exception Swallowing)

EXCEPTION
    WHEN OTHERS THEN
        NULL; -- Худшее, что можно сделать

Код продолжает работу в неопределенном состоянии, данные могут быть испорчены, а разработчик никогда не узнает, что что-то пошло не так. Если вы используете WHEN OTHERS, там обязательно должен быть либо RAISE, либо запись в лог и корректное завершение.

2. Избыточное логирование Если у вас вложена процедура А в процедуру Б, и обе имеют блок WHEN OTHERS с вызовом log_error, вы получите дублирование записей. Правило: логируйте либо в месте возникновения (самый глубокий уровень), либо на самом верхнем уровне (интерфейсном). Чаще всего логирование на верхнем уровне удобнее, так как FORMAT_ERROR_BACKTRACE всё равно покажет всю глубину проблемы.

3. Использование RAISE_APPLICATION_ERROR для всего подряд Не используйте этот механизм для внутренней логики, если процедура вызывается другой процедурой PL/SQL. Это дорогостоящая операция. Для внутренних нужд используйте обычные EXCEPTION и RAISE.

Резюме по стратегии логирования

Эффективная система логирования в Oracle — это не просто таблица и INSERT. Это инфраструктура, которая:

1. Использует автономные транзакции, чтобы не зависеть от ROLLBACK основного кода.
2. Собирает максимум контекста через DBMS_UTILITY.
3. Различает технические сбои и бизнес-исключения.
4. Позволяет управлять детализацией логов без перекомпиляции кода (через настройки).

Правильно настроенное управление исключениями превращает «черный ящик» хранимых процедур в прозрачную систему, где каждая ошибка становится не головной болью, а ценным источником данных для улучшения продукта.

Методы отладки, модульного тестирования и способы вызова процедур

Представьте ситуацию: вы разработали сложную процедуру расчета налоговых вычетов, которая успешно прошла компиляцию. Однако при запуске на реальных данных она либо возвращает пустой результат, либо «тихо» завершается, не обновив ни одной строки. В мире PL/SQL компиляция — это лишь подтверждение синтаксической корректности. Настоящая работа начинается на этапе отладки и верификации логики. Без системного подхода к тестированию и понимания того, как именно вызывается ваш код из различных сред, разработка превращается в гадание на кофейной гуще.

Способы вызова хранимых процедур

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

Вызов из анонимного блока

Это самый распространенный способ при разработке и ручном тестировании. Анонимный блок позволяет объявить тестовые переменные, передать их в процедуру и затем вывести результат с помощью DBMS_OUTPUT.

DECLARE
    v_input_val  NUMBER := 100;
    v_output_val NUMBER;
BEGIN
    -- Вызов процедуры с использованием именованной нотации
    calculate_tax(
        p_amount => v_input_val,
        p_result => v_output_val
    );

    DBMS_OUTPUT.PUT_LINE('Результат расчета: ' || v_output_val);
END;

Команда EXECUTE (SQL*Plus и SQL Developer)

В интерактивных средах (SQL*Plus, SQLcl, Oracle SQL Developer) существует сокращенная команда EXEC или EXECUTE. Важно понимать, что это не команда SQL, а обертка, которая неявно превращает ваш вызов в анонимный блок BEGIN процедура; END;.

Если процедура имеет OUT-параметры, вам потребуется использовать переменные окружения (bind variables):

VARIABLE g_res NUMBER;
EXEC calculate_tax(500, :g_res);
PRINT g_res;

Вызов из внешних приложений (JDBC/ODBC)

При интеграции с Java, Python или C# вызов происходит через подготовленные выражения (Prepared Statements). Здесь критически важно соответствие типов данных. Например, в JDBC используется синтаксис:

{call calculate_tax(?, ?)}

Здесь первый знак вопроса привязывается как IN, а второй регистрируется как OUT параметр определенного типа (например, Types.NUMERIC). Ошибка на этом этапе часто приводит к исключениям вроде ORA-06553: PLS-306: wrong number or types of arguments.

Методы отладки: от «пехотного» до профессионального

Отладка — это процесс сокращения дистанции между тем, что код должен делать, и тем, что он делает на самом деле.

Инструментальная отладка (DBMS_OUTPUT)

Несмотря на простоту, пакет DBMS_OUTPUT остается основным инструментом быстрой проверки. Однако у него есть существенные ограничения:

1. Буферизация: данные не выводятся на экран до тех пор, пока весь блок PL/SQL не завершит выполнение. Если процедура зависла в бесконечном цикле, вы не увидите ни одной отладочной строки.
2. Лимиты: старые версии Oracle имели жесткий лимит на размер буфера (20 000 байт), современные версии позволяют устанавливать UNLIMITED, но это все равно потребляет память сессии (PGA).
3. Зависимость от среды: клиент (например, SQL Developer) должен явно выполнить SET SERVEROUTPUT ON.

Трассировка с помощью DBMS_APPLICATION_INFO

Для отладки процедур, которые выполняются долго (например, ночные пакетные задания), DBMS_OUTPUT бесполезен. Профессионалы используют пакет DBMS_APPLICATION_INFO, который позволяет записывать текущий статус выполнения прямо в системные представления V$SESSION.

PROCEDURE long_running_proc IS
BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE('BATCH_PROCESSOR', 'STARTING');

    -- Некий цикл обработки
    FOR i IN 1..1000 LOOP
        DBMS_APPLICATION_INFO.SET_ACTION('PROCESSING ROW ' || i);
        -- Логика...
    END LOOP;

    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;

Администратор БД может в реальном времени увидеть прогресс выполнения через запрос: SELECT action FROM v$session WHERE module = 'BATCH_PROCESSOR';

Использование интерактивного отладчика (JDWP)

Современные IDE (Oracle SQL Developer, PL/SQL Developer, TOAD) поддерживают протокол JDWP (Java Debug Wire Protocol). Это позволяет:

• Устанавливать точки остановки (breakpoints).
• Просматривать значения переменных «на лету».
• Выполнять код по шагам (Step Into, Step Over).

Для работы отладчика пользователю необходима привилегия DEBUG CONNECT SESSION и DEBUG ANY PROCEDURE (или доступ к конкретному объекту). Важно помнить, что отладка в промышленной среде опасна: сессия «замораживает» выполнение, и если процедура удерживает блокировки на таблицы, это может парализовать работу других пользователей.

Модульное тестирование: переход к автоматизации

Модульное тестирование (Unit Testing) — это проверка минимально возможных фрагментов кода (процедур, функций) в изоляции от остальной системы. В PL/SQL это особенно важно из-за тесной связи кода с данными.

Проблема зависимостей и «чистоты» тестов

Главная сложность тестирования хранимых процедур — зависимость от состояния таблиц. Если процедура update_salary меняет данные, то повторный запуск теста на тех же данных может дать другой результат. Золотое правило: каждый тест должен сам готовить себе данные и очищать их после себя.

Использование фреймворка utPLSQL

На сегодняшний день стандартом де-факто для Oracle является open-source фреймворк utPLSQL (версия 3+). Он построен по принципам JUnit и позволяет писать тесты на самом PL/SQL.

Структура теста в utPLSQL обычно оформляется в виде пакета:

CREATE OR REPLACE PACKAGE test_bet_logic IS
    -- %suite(Тестирование логики ставок)

    -- %test(Проверка корректного начисления выигрыша)
    PROCEDURE test_calculate_winning;
END;
/

CREATE OR REPLACE PACKAGE BODY test_bet_logic IS
    PROCEDURE test_calculate_winning IS
        v_actual   NUMBER;
        v_expected NUMBER := 200;
    BEGIN
        -- 1. Arrange (Подготовка данных)
        -- Здесь можно вставить тестовые строки в таблицы

        -- 2. Act (Действие)
        v_actual := betting_engine.get_payout(p_bet => 100, p_coeff => 2.0);

        -- 3. Assert (Проверка)
        ut.expect(v_actual).to_equal(v_expected);
    END;
END;
/

Преимущества такого подхода:

• Аннотации: специальные комментарии (-- %test) позволяют фреймворку автоматически находить и запускать тесты.
• Изоляция: utPLSQL поддерживает автоматический откат транзакций после выполнения теста, что сохраняет БД в чистоте.
• Интеграция: результаты можно выгружать в формате JUnit XML для систем CI/CD (Jenkins, GitLab CI).

Профессиональные подходы к верификации кода

Тестирование граничных условий

При отладке процедур новички часто проверяют только «счастливый путь» (Happy Path). Профессиональный разработчик обязан проверить:

1. Пустые входные данные: что будет, если передать NULL в обязательный параметр?
2. Экстремальные значения: как поведет себя процедура при обработке суммы в $10^{15}$ или при отрицательном количестве товара?
3. Нарушение уникальности: если процедура делает INSERT, корректно ли она обрабатывает DUP_VAL_ON_INDEX?

Рефакторинг для тестируемости

Если процедуру невозможно протестировать без заполнения 50 таблиц — это признак плохого дизайна.

• Разделяйте логику и данные: выносите сложные расчеты в функции, которые не делают SELECT из таблиц, а принимают все данные как параметры. Такие «чистые» функции тестируются мгновенно.
• Используйте интерфейсы (пакеты): это позволяет подменять (mock) реализации в будущем.

Замер производительности при отладке

Иногда процедура работает правильно, но неприемлемо медленно. Для поиска «узких мест» используйте профайлер: DBMS_PROFILER или более современный DBMS_HPROF. Они позволяют увидеть, на какой именно строке кода программа провела больше всего времени.

Пример запуска профайлера:

BEGIN
    DBMS_PROFILER.START_PROFILER('Test run ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    my_complex_procedure();
    DBMS_PROFILER.STOP_PROFILER;
END;

После выполнения данные о времени работы каждой строки будут доступны в системных таблицах PLSQL_PROFILER_DATA и других.

Тонкости работы с исключениями при тестировании

Важной частью тестирования является проверка того, что процедура выбрасывает нужное исключение в ответ на некорректные действия. В анонимном блоке это проверяется так:

DECLARE
    e_invalid_param EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_invalid_param, -20001);
BEGIN
    -- Ожидаем, что вызов с -1 вызовет ошибку
    process_data(p_id => -1);

    -- Если дошли сюда, значит тест провален
    RAISE_APPLICATION_ERROR(-20999, 'Тест провален: ошибка не возникла');
EXCEPTION
    WHEN e_invalid_param THEN
        DBMS_OUTPUT.PUT_LINE('Тест пройден: получена ожидаемая ошибка');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Тест провален: получена неверная ошибка ' || SQLERRM);
END;

Замыкание мысли

Отладка и тестирование — это не досадная задержка перед релизом, а неотъемлемая часть процесса разработки. Использование профессиональных инструментов, таких как DBMS_APPLICATION_INFO для мониторинга, JDWP для пошагового анализа и фреймворков вроде utPLSQL для автоматизации проверок, превращает написание кода из искусства в инженерную дисциплину. Помните: код, который нельзя протестировать автоматически, со временем превращается в «наследие» (legacy), которое все боятся трогать. Начинайте писать тесты одновременно с кодом, и ваши хранимые процедуры станут фундаментом надежной автоматизации бизнес-процессов.

Оптимизация производительности и обеспечение безопасности хранимого кода

Написание работающей хранимой процедуры — это лишь половина пути профессионального разработчика. В высоконагруженных системах разница между «просто кодом» и «оптимизированным кодом» измеряется не в эстетике, а в часах сэкономленного времени выполнения и терабайтах непереданного по сети трафика. В этой финальной части нашего погружения мы разберем, как заставить PL/SQL работать на пределе возможностей СУБД и как защитить логику от внешних угроз.

Проблема «построчного» мышления и Bulk Operations

Основной барьер производительности в Oracle — это переключение контекста между движками PL/SQL и SQL. Каждый раз, когда внутри цикла LOOP выполняется инструкция INSERT, UPDATE или SELECT INTO, процессор тратит ресурсы на передачу данных и команд из процедурной среды в SQL-ядро. Если в цикле 100 000 итераций, мы получаем 100 000 переключений.

Решением является «массовая» (bulk) обработка данных. Вместо того чтобы передавать строки по одной, мы передаем их пачками (коллекциями), сводя количество переключений к минимуму.

Массовая выборка: BULK COLLECT

Инструкция BULK COLLECT позволяет извлечь весь результат запроса (или его часть) в коллекцию за один проход.

CREATE OR REPLACE PROCEDURE process_large_data AS
    -- Объявляем тип коллекции для хранения записей
    TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
    v_emps t_emp_tab;

    -- Лимит для управления потреблением памяти (PGA)
    c_limit CONSTANT PLS_INTEGER := 1000;

    CURSOR c_data IS SELECT * FROM employees;
BEGIN
    OPEN c_data;
    LOOP
        FETCH c_data BULK COLLECT INTO v_emps LIMIT c_limit;
        EXIT WHEN v_emps.COUNT = 0;

        -- Обработка коллекции в памяти
        FOR i IN 1 .. v_emps.COUNT LOOP
            NULL; -- Логика обработки
        END LOOP;
    END LOOP;
    CLOSE c_data;
END;

Использование LIMIT критически важно. Без него BULK COLLECT попытается загрузить все миллионы строк в оперативную память (PGA), что приведет к ошибке ORA-04030 (нехватка памяти процесса). Оптимальный размер лимита обычно варьируется от 100 до 1000.

Массовые изменения: FORALL

Если BULK COLLECT ускоряет чтение, то FORALL ускоряет запись. Это не цикл, а декларативная команда движку SQL: «Возьми эту коллекцию и примени к ней следующую DML-операцию».

FORALL i IN v_ids.FIRST .. v_ids.LAST
    UPDATE accounts
    SET balance = balance + v_amounts(i)
    WHERE id = v_ids(i);

Важно понимать: внутри FORALL может быть только одна DML-команда. Если вам нужно выполнить сложную логику перед обновлением, вы сначала готовите данные в коллекциях в PL/SQL, а затем одним «выстрелом» FORALL отправляете их в базу.

Профилирование и поиск «бутылочного горлышка»

Оптимизация без измерений — это гадание. Профессионалы используют DBMS_PROFILER или DBMS_HPROF (Hierarchical Profiler). Эти инструменты позволяют увидеть, сколько микросекунд было потрачено на каждую конкретную строку кода.

Если вы обнаружили, что время тратится на SQL-запросы внутри процедуры, следующим шагом станет анализ планов выполнения. В PL/SQL часто забывают о хинтах (hints). Например, если вы знаете, что процедура всегда обрабатывает небольшой объем данных, хинт /*+ FIRST_ROWS(10) */ может заставить оптимизатор выбрать индекс вместо сканирования таблицы.

Кэширование результатов: Result Cache

Начиная с версии 11g, Oracle предлагает механизм RESULT_CACHE. Если функция вызывается с одними и теми же параметрами и данные в базовых таблицах не менялись, Oracle вернет результат из кэша, вообще не выполняя код.

FUNCTION get_tax_rate(p_region_id IN NUMBER)
RETURN NUMBER RESULT_CACHE RELIES_ON (regions) IS
BEGIN
    -- Сложные расчеты
END;

Секция RELIES_ON указывает, при изменении какой таблицы кэш должен стать невалидным. Это мощнейший инструмент для справочных данных.

Безопасность кода: SQL-инъекции в PL/SQL

Многие полагают, что использование хранимых процедур автоматически защищает от SQL-инъекций. Это опасное заблуждение, особенно при использовании динамического SQL (EXECUTE IMMEDIATE).

Уязвимый код

-- ОПАСНО: Конкатенация строк
PROCEDURE find_user(p_name IN VARCHAR2) IS
    v_sql VARCHAR2(1000);
    v_count NUMBER;
BEGIN
    v_sql := 'SELECT count(*) FROM users WHERE username = ''' || p_name || '''';
    EXECUTE IMMEDIATE v_sql INTO v_count;
END;

Если передать в p_name значение ' OR '1'='1, злоумышленник обойдет проверку.

Безопасный код: Связываемые переменные (Bind Variables)

Единственный верный способ работы с динамическим SQL — использование USING. Это не только защищает от инъекций, но и позволяет Oracle повторно использовать план выполнения запроса в Shared Pool, что колоссально повышает производительность.

-- БЕЗОПАСНО: Использование плейсхолдеров
PROCEDURE find_user(p_name IN VARCHAR2) IS
    v_sql VARCHAR2(1000);
    v_count NUMBER;
BEGIN
    v_sql := 'SELECT count(*) FROM users WHERE username = :name';
    EXECUTE IMMEDIATE v_sql INTO v_count USING p_name;
END;

Управление правами: Definer's vs Invoker's Rights

Мы уже касались темы AUTHID, но в контексте безопасности это фундаментальный выбор.

1. AUTHID DEFINER (по умолчанию): Процедура выполняется с правами создателя. Это позволяет реализовать принцип минимальных привилегий: пользователю не нужен доступ к таблице SALARIES, ему дается доступ только к процедуре CALCULATE_BONUS, которая сама «видит» таблицу.
2. AUTHID CURRENT_USER: Процедура выполняется с правами того, кто её вызвал. Это необходимо для утилитных процедур, работающих с данными текущего пользователя (например, процедура очистки временных таблиц).

Опасность AUTHID DEFINER заключается в возможности непреднамеренного расширения прав. Если администратор (DBA) создаст процедуру с этим флагом, любой, кому дано право на выполнение, сможет косвенно выполнить действия от имени администратора.

Сокрытие логики: Wrap Utility

Иногда бизнес-логика является интеллектуальной собственностью, которую нельзя показывать даже заказчику, имеющему доступ к базе. Oracle предоставляет утилиту wrap, которая обфусцирует (шифрует) исходный код PL/SQL.

После обработки код в системном представлении USER_SOURCE будет выглядеть как нечитаемый набор символов. Однако помните: wrap — это не абсолютная защита, существуют деобфускаторы. Это защита от «любопытных глаз», а не от профессионального взлома.

Использование пакетов для оптимизации памяти

Пакеты (Packages) — это не просто способ группировки процедур. С точки зрения производительности у них есть важное свойство: при первом обращении к любому элементу пакета весь пакет загружается в память целиком.

Это снижает количество обращений к дисковой подсистеме (I/O) при вызове смежных функций. Кроме того, переменные, объявленные в спецификации или теле пакета (но вне процедур), сохраняют свое состояние на протяжении всей сессии пользователя. Это позволяет реализовать «сессионный кэш»:

PACKAGE BODY sales_pkg IS
    v_last_rate NUMBER; -- Глобальная переменная сессии

    FUNCTION get_rate RETURN NUMBER IS
    BEGIN
        IF v_last_rate IS NULL THEN
            SELECT rate INTO v_last_rate FROM exchange_rates WHERE TRUNC(dt) = TRUNC(SYSDATE);
        END IF;
        RETURN v_last_rate;
    END;
END sales_pkg;

В этом примере запрос к таблице выполнится только один раз за всю сессию пользователя, сколько бы раз он ни вызывал функцию.

Детерминированные функции

Если функция всегда возвращает один и тот же результат при одинаковых входных данных (например, функция перевода валюты по фиксированному курсу), её следует помечать ключевым словом DETERMINISTIC.

FUNCTION calculate_area(p_radius NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
    RETURN 3.14159 * p_radius * p_radius;
END;

Это позволяет оптимизатору Oracle использовать значение функции в функциональных индексах и пропускать повторные вычисления в рамках одного SQL-запроса.

Финальные рекомендации по производительности

Эффективность PL/SQL кода часто упирается в то, насколько хорошо разработчик понимает границу между процедурным кодом и SQL.

• SQL First: Если задачу можно решить одним сложным SQL-запросом (используя аналитические функции, MERGE или WITH), делайте это на SQL. Движок SQL всегда быстрее обрабатывает наборы данных, чем циклы PL/SQL.
• Bulk Operations: Если SQL недостаточно и нужна сложная логика — используйте BULK COLLECT и FORALL.
• Data Types: Используйте PLS_INTEGER для счетчиков циклов и арифметики. Он быстрее, чем NUMBER, так как работает на уровне машинных инструкций, а не программной эмуляции.
• NoCopy: Для больших коллекций, передаваемых в параметры OUT или IN OUT, используйте хинт NOCOPY, чтобы избежать дорогостоящего копирования данных в памяти.

Профессиональная разработка на PL/SQL — это баланс между чистотой архитектуры, безопасностью доступа и скоростью обработки данных. Понимание механизмов кэширования, массовых операций и защиты от инъекций превращает разработчика из «пишущего скрипты» в архитектора баз данных, способного создавать системы, выдерживающие колоссальные нагрузки.