Основы языка PL/SQL: особенности, архитектура и предназначение

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

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

Содержание

• Особенности PL/SQL

• Переменные
• Управляющие структуры
• Процедуры и функции
• Обработка ошибок
• Оптимизация производительности
• Блочная структура

• Архитектура PL/SQL
• Что дальше

Особенности PL/SQL

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

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

Переменные

В PL/SQL вы можете объявить переменную, присвоить ей значение и использовать это значение в различных частях программы. Переменные позволяют легко изменять расчёты или поведение программы, не переписывая весь код. Достаточно обновить значение переменной.

Также важно знать о константах. Константы в PL/SQL похожи на переменные, но их значение задаётся только один раз при объявлении и не может быть изменено в дальнейшем. То есть значение переменных мы можем изменять, а константы всегда остаются неизменными.

Создадим таблицу employees, добавим в неё информацию о сотруднике и его зарплате, затем рассчитаем бонус в размере 10% от зарплаты, сохраним промежуточные значения и запишем результаты расчёта в таблицу:

-- Создаём таблицу employees. Добавляем поля для ID сотрудника, его имени, фамилии, зарплаты и бонуса
CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   salary NUMBER(10, 2),
   bonus NUMBER(10, 2)
);

-- Вставляем тестовые данные в таблицу
-- Добавляем запись для одного сотрудника с уникальным ID, именем, фамилией и зарплатой
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (101, 'John', 'Doe', 5000);

-- Сохраняем внесённые изменения в базе данных
COMMIT;

-- PL/SQL-блок для расчёта бонуса
DECLARE
   v_salary    NUMBER(10,2);  -- Создаём переменную для хранения зарплаты сотрудника
   v_bonus     NUMBER(10,2);  -- Создаём переменную для хранения рассчитанного бонуса
   c_bonus_rate CONSTANT NUMBER := 0.10; -- Создаём константу для ставки бонуса (10%)
BEGIN
   -- Извлекаем зарплату сотрудника из ID 101 и сохраняем её в переменной v_salary
   SELECT salary INTO v_salary
   FROM employees
   WHERE employee_id = 101;

   -- Рассчитываем бонус как 10% от зарплаты, используя константу c_bonus_rate, и сохраняем его в переменной v_bonus
   v_bonus := v_salary * c_bonus_rate;

   -- Обновляем запись сотрудника в таблице с новым значением бонуса
   UPDATE employees
   SET bonus = v_bonus
   WHERE employee_id = 101;

   -- Выводим на экран зарплату и бонус для проверки результатов
   DBMS_OUTPUT.PUT_LINE('Зарплата: ' || v_salary || ', Бонус: ' || v_bonus);
END;
/

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

-- Этап 1: Создаём таблицу
Table created.

-- Этап 2: Вставляем данные
1 row(s) inserted.

-- Этап 3: Сохраняем изменения
Statement processed.

-- Этап 4: Выполняем PL/SQL-блок и рассчитываем бонус
Statement processed.

-- Этап 5: Выводим результат
Зарплата: 5000, Бонус: 500

Читайте также:

Переменные в Python: что это такое и какие они бывают

Управляющие структуры

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

• Циклы (LOOP, WHILE, FOR) предназначены для выполнения повторяющихся операций. Например, с помощью цикла можно автоматически начислять проценты по каждому банковскому счёту в конце месяца.
• Условные операторы (IF, CASE) применяются для выполнения различных действий в зависимости от условий. Например, в системе управления складскими запасами через условные операторы можно проверять наличие товара перед оформлением заказа.

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

-- Создаём таблицу employees с полями для ID сотрудника, имени, фамилии, зарплаты и бонуса
CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   salary NUMBER(10, 2),
   bonus NUMBER(10, 2)
);

-- Вставляем тестовые данные в таблицу
-- Добавляем запись для первого сотрудника с уникальным ID, именем, фамилией и зарплатой
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (101, 'John', 'Doe', 5000);

-- Добавляем запись для второго сотрудника
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (102, 'Jane', 'Smith', 6000);

-- Добавляем запись для третьего сотрудника
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (103, 'Alice', 'Johnson', 5500);

-- Сохраняем внесённые изменения в базе данных
COMMIT;

-- PL/SQL-блок для расчёта бонусов для всех сотрудников
DECLARE
   v_salary  NUMBER(10,2);  -- Создаём переменную для хранения зарплаты текущего сотрудника
   v_bonus   NUMBER(10,2);  -- Создаём переменную для хранения рассчитанного бонуса
BEGIN
   -- Используем цикл FOR для прохождения по всем сотрудникам и расчёта бонусов
   FOR emp IN (SELECT employee_id, salary FROM employees) LOOP
       -- Сохраняем зарплату текущего сотрудника в переменной v_salary
       v_salary := emp.salary;

       -- Рассчитываем бонус как 10% от зарплаты и сохраняем его в переменной v_bonus
       v_bonus := v_salary * 0.10;

       -- Обновляем запись текущего сотрудника в таблице с новым значением бонуса
       UPDATE employees
       SET bonus = v_bonus
       WHERE employee_id = emp.employee_id;
   END LOOP;

   -- Используем цикл FOR для вывода зарплаты и бонусов для всех сотрудников
   FOR emp IN (SELECT employee_id, salary, bonus FROM employees) LOOP
       -- Печатаем ID, зарплату и бонус для каждого сотрудника
       DBMS_OUTPUT.PUT_LINE('ID сотрудника: ' || emp.employee_id || ', Зарплата: ' || emp.salary || ', Бонус: ' || emp.bonus);
   END LOOP;
END;
/

В результате выполнения кода в консоли должна появиться такая запись:

-- Этап 1. Создаём таблицу
Table created.

-- Этап 2. Вставляем данные для первого сотрудника
1 row(s) inserted.

-- Этап 3. Вставляем данные для второго сотрудника
1 row(s) inserted.

-- Этап 4. Вставляем данные для третьего сотрудника
1 row(s) inserted.

-- Этап 5. Сохраняем изменения в базе данных
Statement processed.

-- Этап 6. Выполняем PL/SQL-блок и рассчитываем бонусы
Statement processed.

-- Этап 7. Выводим результаты расчёта бонусов
ID сотрудника: 101, Зарплата: 5000, Бонус: 500
ID сотрудника: 102, Зарплата: 6000, Бонус: 600
ID сотрудника: 103, Зарплата: 5500, Бонус: 550

Читайте также:

Циклы for, while и do-while в С++ для новичков

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

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

Изменим нашу таблицу и добавим в неё явный курсор, который позволит последовательно обрабатывать и обновлять множество записей:

-- Создаём таблицу employees с полями для ID сотрудника, имени, фамилии, зарплаты и бонуса
CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   salary NUMBER(10, 2),
   bonus NUMBER(10, 2)
);

-- Вставляем тестовые данные в таблицу
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (101, 'John', 'Doe', 5000);
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (102, 'Jane', 'Smith', 6000);
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (103, 'Alice', 'Johnson', 5500);
COMMIT;

-- PL/SQL-блок с использованием явного курсора
DECLARE
   -- Определяем явный курсор для выборки всех сотрудников с их ID и зарплатами
   CURSOR emp_cursor IS
       SELECT employee_id, salary
       FROM employees;

   -- Переменные для хранения данных из курсора
   v_employee_id employees.employee_id%TYPE;  -- Переменная для хранения ID сотрудника
   v_salary employees.salary%TYPE;            -- Переменная для хранения зарплаты сотрудника
   v_bonus NUMBER(10, 2);                      -- Переменная для хранения бонуса сотрудника

BEGIN
   -- Открываем курсор, чтобы начать извлечение данных
   OPEN emp_cursor;

   -- Цикл для обработки каждой строки данных из курсора
   LOOP
       -- Извлекаем данные из курсора в переменные
       FETCH emp_cursor INTO v_employee_id, v_salary;

       -- Выходим из цикла, если больше нет строк для обработки
       EXIT WHEN emp_cursor%NOTFOUND;

       -- Рассчитываем бонус как 10% от зарплаты
       v_bonus := v_salary * 0.10;

       -- Обновляем запись сотрудника в таблице с новым значением бонуса
       UPDATE employees
       SET bonus = v_bonus
       WHERE employee_id = v_employee_id;

       -- Выводим на экран ID сотрудника, зарплату и бонус
       DBMS_OUTPUT.PUT_LINE('ID сотрудника: ' || v_employee_id || ', Зарплата: ' || v_salary || ', Бонус: ' || v_bonus);
   END LOOP;

   -- Закрываем курсор после завершения обработки всех записей
   CLOSE emp_cursor;

END;
/

Результаты вывода:

-- Создаём таблицу
Table created.

-- Вставляем первую запись в таблицу для сотрудника с ID 101
1 row(s) inserted.

-- Вставляем вторую запись в таблицу для сотрудника с ID 102
1 row(s) inserted.

-- Вставляем третью запись в таблицу для сотрудника с ID 103
1 row(s) inserted.

-- Сохраняем изменения в базе данных
Statement processed.

-- Завершаем выполнение PL/SQL-блока
Statement processed.

-- Результаты расчёта бонусов:
-- Для сотрудника с ID 101: Зарплата 5000, Бонус 500
ID сотрудника: 101, Зарплата: 5000, Бонус: 500

-- Для сотрудника с ID 102: Зарплата 6000, Бонус 600
ID сотрудника: 102, Зарплата: 6000, Бонус: 600

-- Для сотрудника с ID 103: Зарплата 5500, Бонус 550
ID сотрудника: 103, Зарплата: 5500, Бонус: 550

Читайте также:

Условные операторы в Python: от простых «если» до вложенных конструкций

Процедуры и функции

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

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

CREATE OR REPLACE PROCEDURE update_bonuses IS
BEGIN
   -- Начало блока процедуры

   -- Проходим по всем сотрудникам, чтобы обновить их бонусы
   FOR emp IN (SELECT employee_id, salary FROM employees) LOOP
       -- Обновляем запись каждого сотрудника, устанавливая новый бонус
       UPDATE employees
       SET bonus = salary * 0.10  -- Рассчитываем бонус как 10% от зарплаты
       WHERE employee_id = emp.employee_id;  -- Обновляем бонус только для текущего сотрудника
   END LOOP;  -- Завершаем цикл для обновления бонусов
  
   -- Подтверждаем все изменения в базе данных
   COMMIT;  -- Сохраняем изменения, чтобы они стали постоянными
END;
/

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

-- Вызов процедуры для обновления бонусов
BEGIN
   update_bonuses;  -- Вызываем процедуру update_bonuses для автоматического пересчёта бонусов
END;
/

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

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

CREATE OR REPLACE FUNCTION calculate_worked_days(p_employee_id NUMBER) RETURN NUMBER IS
   v_start_date DATE;
   v_current_date DATE := SYSDATE;
   v_worked_days NUMBER;
BEGIN
   -- Получаем дату трудоустройства сотрудника
   SELECT hire_date
   INTO v_start_date
   FROM employees
   WHERE employee_id = p_employee_id;

   -- Рассчитываем количество отработанных дней
   v_worked_days := v_current_date - v_start_date;

   -- Возвращаем количество отработанных дней
   RETURN v_worked_days;
END;
/

Пример вызова функции:

-- Вызываем функцию для вычисления количества отработанных дней для сотрудника с ID 101
SELECT employee_id, calculate_worked_days(employee_id) AS worked_days
FROM employees
WHERE employee_id = 101;

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

Читайте также:

Функции в Python: исчерпывающий гайд

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

CREATE OR REPLACE TRIGGER update_last_modified
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   -- Устанавливаем текущую дату и время в поле last_modified
   :NEW.last_modified := SYSDATE;
END;
/

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

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

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

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

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

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

CREATE OR REPLACE PROCEDURE update_employee_salary(p_employee_id NUMBER, p_new_salary NUMBER) IS
BEGIN
   -- Обновляем зарплату сотрудника
   UPDATE employees
   SET salary = p_new_salary
   WHERE employee_id = p_employee_id;

   IF SQL%ROWCOUNT = 0 THEN
    -- Если не обновлено ни одной строки, значит, ID не существует
       DBMS_OUTPUT.PUT_LINE('Сотрудник с указанным ID не найден.');
   ELSE
       DBMS_OUTPUT.PUT_LINE('Зарплата обновлена успешно.');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
   -- Обрабатываем любые другие исключения
       DBMS_OUTPUT.PUT_LINE('Произошла ошибка: ' || SQLERRM);
END;
/

Пример вызова процедуры:

BEGIN
   -- Вызываем процедуру для обновления зарплаты сотрудника с ID 101
   update_employee_salary(101, 50000);
END;
/

После вызова процедуры вы можете получить одно из трёх сообщений:

• «Зарплата обновлена успешно»;
• «Сотрудник с указанным ID не найден»;
• «Произошла ошибка: ORA-00001: уникальное ограничение (EMPLOYEE_ID_UK) нарушено» — пример одной из возможных проблем, возникающих при обновлении базы данных. В данном случае произошла попытка вставить дублирующее значение в поле с правилом, на которое распростирается уникальное ограничение.

Читайте также:

Исключения в Java: что это такое, зачем они нужны и как с ними работать

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

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

Вот пример с использованием функции FORALL:

DECLARE
   -- Определяем тип записи для хранения данных сотрудника
   TYPE emp_rec IS RECORD (
       employee_id NUMBER,
       new_salary NUMBER
   );

   -- Определяем тип таблицы для хранения списка сотрудников
   TYPE emp_tab IS TABLE OF emp_rec;

   -- Инициализируем таблицу сотрудников для обновления
   employees_to_update emp_tab := emp_tab(
       emp_rec(101, 50000),  -- Данные сотрудника с ID 101 и новой зарплатой 50000
       emp_rec(102, 55000),  -- Данные сотрудника с ID 102 и новой зарплатой 55000
       emp_rec(103, 60000)   -- Данные сотрудника с ID 103 и новой зарплатой 60000
   );
BEGIN
   -- Используем функцию FORALL для массового обновления записей
   FORALL i IN INDICES OF employees_to_update
       UPDATE employees
       SET salary = employees_to_update(i).new_salary
       WHERE employee_id = employees_to_update(i).employee_id;

   -- Выводим сообщение об успешном обновлении
   DBMS_OUTPUT.PUT_LINE('Зарплата обновлена для всех сотрудников.');
EXCEPTION
   WHEN OTHERS THEN
       -- Обрабатываем любые ошибки и выводим сообщение
       DBMS_OUTPUT.PUT_LINE('Произошла ошибка: ' || SQLERRM);
END;
/

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

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

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

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

Блочная структура

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

• DECLARE. В этом разделе объявляются переменные, константы, курсоры и другие элементы, которые будут использоваться в блоке. Если в блоке новые элементы не нужны, раздел можно пропустить.
• BEGIN. Здесь размещаются основные инструкции, такие как SQL-запросы, вызовы процедур и функций. Это обязательная часть любого блока.
• EXCEPTION. В этом разделе описывается логика обработки ошибок, которые могут возникнуть в разделе исполнения кода. Это необязательный раздел, он используется по мере необходимости.
• END. Эта часть завершает блок PL/SQL.

Блоки PL/SQL можно разделить на два типа: анонимные и именованные.

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

DECLARE
 v_salary NUMBER;
BEGIN
 SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;
 DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;

В этом блоке три части:

• DECLARE объявляет переменную v_salary;
• BEGIN содержит SQL-запрос для получения зарплаты сотрудника и вывод результата;
• END завершает блок.

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

CREATE OR REPLACE PROCEDURE update_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;
 COMMIT; -- Сохраняет изменения в базе данных
EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error updating salary: ' || SQLERRM);
END;

В этом примере:

• CREATE OR REPLACE PROCEDURE определяет именованный блок, в данном случае — процедуру update_salary;
• IS начинает определение блока;
• BEGIN содержит основной код, который выполняет SQL-запрос для обновления зарплаты сотрудника и сохраняет изменения в базе данных;
• EXCEPTION обрабатывает возможные ошибки и выводит сообщение в случае возникновения проблем;
• END завершает определение процедуры.

Мы создали именованный блок с процедурой update_salary, которая обновляет зарплату сотрудника с указанным employee_id. Если нам потребуется повторно обновить зарплату, мы можем вызвать эту процедуру в любом месте программы следующим образом:

BEGIN
 -- Вызываем процедуру update_salary, передавая ей идентификатор сотрудника и новую зарплату
 update_salary(101, 5000);
END;

Архитектура PL/SQL

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

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

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

DECLARE
    -- Переменная для хранения количества обновлённых записей
    v_employee_count NUMBER;
BEGIN
    -- Вызываем процедуру для обновления зарплат сотрудников
    update_employee_salaries(10, 50000);
    
    -- Вызываем функцию для получения количества обновлённых записей
    v_employee_count := get_updated_employee_count();
    
    -- Выводим количество обновлённых записей
    DBMS_OUTPUT.PUT_LINE('Обновлено записей: ' || v_employee_count);
END;
/

В этом примере анонимный блок выполняет следующие действия:

• вызывает процедуру update_employee_salaries для обновления зарплат сотрудников;
• использует функцию get_updated_employee_count для подсчёта количества обновлённых записей;
• выводит результат на экран.

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

CREATE OR REPLACE PROCEDURE update_employee_salaries(
    p_department_id NUMBER, 
    p_new_salary NUMBER
) IS
BEGIN
    -- Обновляем зарплаты сотрудников в указанном отделе
    UPDATE employees
    SET salary = p_new_salary
    WHERE department_id = p_department_id;
END;
/

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

CREATE OR REPLACE FUNCTION get_updated_employee_count RETURN NUMBER IS
   v_count NUMBER;
BEGIN
   -- Подсчитываем количество сотрудников с новой зарплатой
   SELECT COUNT(*)
   INTO v_count
   FROM employees
   WHERE salary = 50000; -- Условие для подсчёта
  
   RETURN v_count;
END;
/

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

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

CREATE OR REPLACE TRIGGER update_audit_log
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   -- Записываем изменения в журнал аудита
   INSERT INTO audit_log (employee_id, action, action_time)
   VALUES (:OLD.employee_id, 'Updated Salary', SYSDATE);
END;
/

Наш триггер будет срабатывать каждый раз после обновления записи в таблице employees. Он будет добавлять запись в таблицу audit_log, фиксировать действие и время его выполнения.

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

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

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

Читайте также:

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

Что дальше

В этом разделе мы собрали подборку ресурсов, по которым вы можете самостоятельно и бесплатно освоить PL/SQL.

Для начала мы рекомендуем учебник PL/SQL Tutorial от Oracle или PL/SQL Tutorial от TutorialsPoint. Это хорошо структурированные руководства, дающие полное представление о синтаксисе PL/SQL.

Ещё рекомендуем PL/SQL Tutorial от портала GeeksforGeeks. Помимо справочных материалов, в этом учебнике собраны популярные вопросы и ответы с собеседований по PL/SQL, что может помочь систематизировать знания.

Параллельно с изучением учебников посетите Oracle Dev Gym — бесплатную платформу с занятиями, тестами, видеоматериалами, статьями и еженедельными турнирами по языку PL/SQL и базам данных.

Если у вас технический бэкграунд и вы предпочитаете получать информацию из первоисточников сжато и по делу, рекомендуем официальную документацию Database PL/SQL Language Reference.