Oracle PL/SQL: Введение в хранимые процедуры и функции

• Основы языка PL/SQL
• Создание хранимых процедур
• Функции и возвращаемые значения
• Пакеты и архитектура повторного использования
• Вопросы и ответы

Основы языка PL/SQL

Что такое PL/SQL и как он работает

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

Главное преимущество PL/SQL — интеграция с СУБД Oracle. Это значит, что код выполняется на сервере базы данных, что снижает сетевые задержки и повышает производительность. Такая модель особенно полезна для систем, обрабатывающих большие объёмы данных.

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

Отличия от обычного SQL

SQL — это декларативный язык. Вы "говорите" системе, что хотите получить, а не как это сделать. Например, запрос SELECT * FROM employees WHERE department_id = 10; извлекает данные, но не может содержать условные операторы или циклы.

В PL/SQL же можно задавать алгоритмы. Это процедурный язык, и он "понимает" команды вроде “если”, “иначе”, “для каждого”, “повторять” и т.д.

Вот основные отличия PL/SQL от обычного SQL:

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

Такой подход позволяет реализовывать сложную бизнес-логику прямо в базе данных — без промежуточной обработки на уровне приложений.

Структура PL/SQL-блока

Базовая структура PL/SQL выглядит просто. Она состоит из трёх (опционально четырёх) секций:

Пример простого PL/SQL-блока:

DECLARE
  v_salary NUMBER := 1000;
BEGIN
  v_salary := v_salary * 1.15;
  DBMS_OUTPUT.PUT_LINE('Новая зарплата: ' || v_salary);
END;

Такой подход позволяет выполнять бизнес-логику без участия внешнего приложения, что сокращает количество ошибок и ускоряет разработку.

Среда исполнения: SQL Developer

SQL Developer — это официальная среда разработки от Oracle, которая поддерживает визуальное проектирование объектов базы данных, написание PL/SQL-кода, отладку и тестирование.

Она бесплатна и активно используется не только в учебных, но и в коммерческих проектах. Вот основные функции, которые делают SQL Developer удобным решением:

• Редактор PL/SQL с подсветкой синтаксиса и автодополнением
• Средства отладки: пошаговое выполнение, просмотр значений переменных
• Управление схемой БД: выполнение DDL-команд, создание объектов
• Работа с отчетами, построение графиков, анализ нагрузки

Благодаря высокой степени интеграции с платформой Oracle, SQL Developer часто используется в совместных проектах с ERP-системами, такими как SAP. Подробнее о принципах работы SAP можно узнать в связанной статье.

Если вы начинаете знакомство с Enterprise-разработкой или планируете систематизировать бизнес-логику внутри вашей СУБД — освоить PL/SQL будет правильным решением.

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

Объявление и вызов процедур

Хранимая процедура в Oracle PL/SQL — это именованный блок кода, который можно многократно вызывать из других частей приложения или напрямую через SQL*Plus. Такие процедуры облегчают поддержку кода, повышают читаемость и позволяют централизовать бизнес-логику базы данных.

Создание процедуры начинается с ключевого слова CREATE OR REPLACE PROCEDURE, за которым следует имя процедуры, список параметров (если они есть), и, собственно, тело:

CREATE OR REPLACE PROCEDURE update_employee_salary (
  emp_id IN NUMBER,
  percent_increase IN NUMBER
) AS
BEGIN
  UPDATE employees
  SET salary = salary + (salary * percent_increase / 100)
  WHERE employee_id = emp_id;
END;

Вызвать такую процедуру можно с помощью команды EXECUTE:

EXECUTE update_employee_salary(105, 10);

Или внутри другого блока PL/SQL:

BEGIN
  update_employee_salary(105, 10);
END;

Передача параметров

Один из ключевых моментов при работе с процедурами — корректная работа с параметрами. В PL/SQL реализована поддержка трех типов параметров:

• IN — входной параметр (по умолчанию), передаем данные в процедуру.
• OUT — выходной параметр, процедура возвращает данные в вызывающий код.
• IN OUT — параметр, который можно как передавать, так и изменять внутри процедуры.

Пример с параметром OUT:

CREATE OR REPLACE PROCEDURE get_employee_name (
  emp_id IN NUMBER,
  emp_name OUT VARCHAR2
) AS
BEGIN
  SELECT first_name || ' ' || last_name INTO emp_name
  FROM employees
  WHERE employee_id = emp_id;
END;

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

Примеры реальных процедур

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

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

CREATE OR REPLACE PROCEDURE process_return (
  order_id IN NUMBER
) AS
BEGIN
  UPDATE orders
  SET status = 'Returned', return_date = SYSDATE
  WHERE id = order_id;
  INSERT INTO audit_log (action_type, action_date, description)
  VALUES ('Return Processed', SYSDATE, 'Order ID: ' || order_id);
END;

Такой шаблон особенно полезен, если требуется сохранение истории изменений, что критично для аналитики и контроля бизнес-процессов.

Обработка ошибок

Обязательно включайте обработку ошибок внутри процедуры. Без неё даже небольшая ошибка может привести к непредсказуемому поведению всей бизнес-логики.

Пример обработки ошибок внутри процедуры:

BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = emp_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Сотрудник не найден.');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, 'Произошла неизвестная ошибка.');
END;

Грамотная обработка исключений облегчает отладку и снижает риски в продакшене.

Функции и возвращаемые значения

Разница между процедурами и функциями

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

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

К примеру:

CREATE OR REPLACE FUNCTION get_total_salary(emp_id NUMBER)
RETURN NUMBER IS
  total_salary NUMBER;
BEGIN
  SELECT salary + NVL(commission_pct, 0) INTO total_salary
  FROM employees
  WHERE employee_id = emp_id;
  RETURN total_salary;
END;

Такая функция может быть сразу использована в SELECT-запросе, в отличие от процедуры, предназначенной скорее для выполнения определённых действий, например, обновления данных.

Примеры функций с RETURN

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

• Функция форматирования имени — возвращает строку с форматированным ФИО сотрудника:

CREATE OR REPLACE FUNCTION format_name(last_name VARCHAR2, first_name VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
  RETURN INITCAP(last_name) || ', ' || INITCAP(first_name);
END;

• Функция расчета налога — вычисляет налог от переданной суммы:

CREATE OR REPLACE FUNCTION calc_tax(amount NUMBER)
RETURN NUMBER IS
  tax_rate CONSTANT NUMBER := 0.13;
BEGIN
  RETURN amount * tax_rate;
END;

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

Использование в SELECT-запросах

Одно из главных преимуществ функций — возможность вызывать их прямо в SQL, особенно в SELECT-запросах. Это делает их мощным инструментом при формировании отчётов, представлений и при реализации бизнес-логики на уровне базы данных.

Допустим, есть функция, рассчитывающая полную стоимость заказа, включая налог:

CREATE OR REPLACE FUNCTION get_order_total(order_id NUMBER)
RETURN NUMBER IS
  total NUMBER;
BEGIN
  SELECT SUM(unit_price * quantity) INTO total
  FROM order_items
  WHERE order_id = order_id;
  RETURN total * 1.13; -- включая 13% налог
END;

Теперь её можно использовать в запросе так:

SELECT order_id, get_order_total(order_id) AS total_price
FROM orders
WHERE order_date > TO_DATE('01-01-2025', 'DD-MM-YYYY');

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

Дополнительно, функции можно использовать в условиях фильтрации, например, как часть WHERE или HAVING:

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(get_order_total(order_id)) > 10000;

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

Если вы хотите протестировать подобные функции в безопасной среде, настройка виртуальной машины Oracle в VirtualBox — хороший выбор. Подробная инструкция доступна в статье Oracle VirtualBox: как установить и настроить виртуальную машину.

Таким образом, функции — это не просто способ вернуть значение: они позволяют строить лаконичные, гибкие и поддерживаемые решения на уровне БД, облегчая реализацию бизнес-логики и делая запросы выразительнее.

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

Создание и поддержка пакета

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

Создание пакета начинается с объявления его спецификации, где описывается интерфейс: публикуемые процедуры, функции, типы данных, константы и исключения. За спецификацией следует тело пакета, в котором уже реализуются методы и логика.

Простой пример спецификации и тела пакета для работы со складами:

CREATE PACKAGE warehouse_pkg AS
  PROCEDURE add_product(p_id NUMBER, p_name VARCHAR2);
  FUNCTION get_product_count RETURN NUMBER;
END warehouse_pkg;

CREATE PACKAGE BODY warehouse_pkg AS
  v_product_count NUMBER := 0;
  PROCEDURE add_product(p_id NUMBER, p_name VARCHAR2) IS
  BEGIN
    -- Добавление продукта (условная логика)
    v_product_count := v_product_count + 1;
  END;
  FUNCTION get_product_count RETURN NUMBER IS
  BEGIN
    RETURN v_product_count;
  END;
END warehouse_pkg;

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

Разделение на спецификацию и тело

Чёткое разграничение между интерфейсом (спецификацией) и реализацией (телом) пакета — это основа модульности в PL/SQL. Оно позволяет разработчикам определять, что именно должно быть доступно другим компонентам приложения, скрывая при этом внутренние детали реализации.

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

Разделение помогает и с точки зрения командной разработки: разные специалисты могут параллельно работать над спецификацией и телом. Это снижает конфликтность изменений и ускоряет внедрение новых функций.

Стратегии версионирования

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

Вот две основные стратегии:

• Ненарушающее (backward-compatible) расширение: добавление новых процедур и функций без изменения существующих интерфейсов. Такой подход минимизирует риски для потребителей пакета.
• Версионирование по имени: при необходимости изменить сигнатуру функций создаётся новый пакет, например invoice_pkg_v2. Это позволяет сохранить старую версию для совместимости и протестировать новую логику независимо.

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

Примеры комплексных решений на пакетах

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

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

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

Вопросы и ответы