Хранимые функции, процедуры и триггеры

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

Хранимые функции

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

Пример создания функции

CREATE OR REPLACE FUNCTION get_employee_salary (
  p_employee_id IN NUMBER
) RETURN NUMBER
IS
  v_salary NUMBER;
BEGIN
  SELECT salary
  INTO v_salary
  FROM employees
  WHERE employee_id = p_employee_id;

  RETURN v_salary;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

Примечания:

• RETURN указывает тип возвращаемого значения.

• Исключения обрабатываются для предотвращения ошибок, например, если данные не найдены.

Хранимые процедуры

Хранимые процедуры — это блоки кода, которые выполняют действия, но не возвращают значения. Они могут принимать параметры и выполнять операции, такие как INSERT, UPDATE, и DELETE.

Пример создания процедуры

CREATE OR REPLACE PROCEDURE update_employee_salary (
  p_employee_id IN NUMBER,
  p_new_salary IN NUMBER
) IS
BEGIN
  UPDATE employees
  SET salary = p_new_salary
  WHERE employee_id = p_employee_id;
END;

Примечания:

• Процедуры используются для выполнения действий, которые не требуют возвращения значения.

• Параметры могут быть входными (IN), выходными (OUT) или входными и выходными (IN OUT).

Триггеры

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

Пример создания триггера

CREATE OR REPLACE TRIGGER trg_before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 3000 THEN
    :NEW.salary := 3000;  -- Устанавливаем минимальную зарплату
  END IF;
END;

Примечания:

• Триггеры могут быть BEFORE (до выполнения операции) или AFTER (после выполнения операции).

• FOR EACH ROW означает, что триггер выполняется для каждой затронутой строки.

• Используйте :NEW для доступа к новым значениям строки и :OLD для старых значений.

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

1. Функции:

   • Используйте в SQL-запросах, например: SELECT get_employee_salary(101) FROM dual;

   • Можно использовать для вычисления значений или проверки условий.

2. Процедуры:

   • Вызываются из других PL/SQL блоков или приложений: BEGIN update_employee_salary(101, 5000); END;

   • Используются для выполнения операций над данными.

3. Триггеры:

   • Автоматически обрабатывают события: например, обеспечение целостности данных или автоматическое изменение данных.

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

Рекомендации по использованию

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

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

• Управление зависимостями: Будьте внимательны к зависимостям между объектами базы данных (например, функции могут использоваться в процедурах и триггерах).

• Документирование кода: Документируйте функции, процедуры и триггеры для упрощения поддержки и понимания кода другими разработчиками.

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