Введение в PL/SQL29.11.2024 15:30

В начале изучения нового мы такие:)

В начале изучения нового мы такие:)

Для меня PL/SQL есть логичное продолжение изучения SQL, но по нему много и статей, и курсов, причем даже на русском языке. С другой стороны, PL/SQL раскрывается достаточно редко, особенно среди бесплатных ресурсов, так что… Начинаем:)

Блоки процедур

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

Блок DECLARE

В блоке DECLARE происходит объявление переменных, которые будут использоваться в теле процедуры. Пример объявления переменных: 

DECLARE
    var_1 NUMERIC;
    var_2 VARCHAR2(64);
    var_3 DATE;

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

DECLARE
    var_1 table_name.column_name%TYPE;
    var_2 table_name2.column_name2%TYPE;

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

%ROWTYPE

Атрибут %ROWTYPE автоматически наследует структуру всех столбцов таблицы или курсора (не будет рассматриваться в этой статье), обеспечивая удобство работы с несколькими значениями одновременно.

Пример: 

DECLARE
    employee_record employees%ROWTYPE;
BEGIN
    SELECT *
    INTO employee_record
    FROM employees
    WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE('ID: ' || employee_record.employee_id ||
                         ', Name: ' || employee_record.first_name ||
                         ' ' || employee_record.last_name);
END;

Удобства:

  • Динамичность (при изменении типа данных атрибутов %ROWTYPE автоматически это перенимает);

  • Лаконичность (не 10 переменных для 10 атрибутов, а 1).

Ограничения:

  • Предполагается использовать малую часть атрибутов таблицы — %ROWTYPE будет использовать больше памяти, чем необходимо;

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

Блок основного кода (BEGIN)

В блоке BEGIN размещается основной код, который будет выполняться. Это может быть один или несколько SQL-запросов: 

BEGIN
    -- Здесь размещается основной код
END;

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

Блок обработки ошибок (EXCEPTION)

В случае возникновения ошибок в блоке BEGIN, управление передается в блок EXCEPTION, где можно обработать исключения.

BEGIN
    -- Основной код
EXCEPTION
    WHEN others THEN
        -- Обработка ошибок
END;

Когда возникает ошибка, выполнение основного блока прерывается, и управление передаётся в блок EXCEPTION. Это позволяет предотвратить некорректное завершение программы и обработать ситуацию более осмысленно.

Стандартные исключения

PL/SQL имеет встроенные обработчики для стандартных ошибок:

  • NO_DATA_FOUND: Возникает, если SQL-запрос не возвращает строк, а результат ожидается через SELECT INTO.

  • TOO_MANY_ROWS: Выбрасывается, если SELECT INTO возвращает больше одной строки.

  • ZERO_DIVIDE: Ошибка деления на ноль.

  • INVALID_CURSOR: Использование неинициализированного или закрытого курсора.

  • VALUE_ERROR: Проблема с диапазоном или типом данных.

  • INVALID_NUMBER: Преобразование строки в число завершилось ошибкой.

Пример обработки ошибки: 

DECLARE
    tmp_var NUMBER;
BEGIN
    SELECT 100 / 0
    INTO tmp_var 
    FROM dual;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE = ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM = ' || SQLERRM);
END;

Из IDE

Из IDE

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

Можно определять свои исключения и выбрасывать их с помощью RAISE: 

DECLARE
    e_custom_exception EXCEPTION;
    num NUMBER := 5;
BEGIN
    IF num < 10 THEN
        RAISE e_custom_exception;
    END IF;
EXCEPTION
    WHEN e_custom_exception THEN
        DBMS_OUTPUT.PUT_LINE('Произошла ошибка: значение меньше 10!');
END;

Универсальный обработчик WHEN OTHERS

Обработчик WHEN OTHERS перехватывает все ошибки, не указанные явно: 

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Произошла ошибка: ' || SQLERRM);
END;

Рекомендация: использовать WHEN OTHERS только с логированием ошибок для анализа и избегать скрытия критических проблем.

Переменные в PL/SQL

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

  1. Присвоение значения «в лоб»: 

DECLARE
    some_var VARCHAR2(32);
BEGIN
    some_var := 'some_text';
END;

  1. Получение значения через SQL-запрос: 

DECLARE
    some_var1 VARCHAR2(32);
    some_var2 VARCHAR2(32);
BEGIN
    SELECT some_col1, some_col2
    INTO some_var1, some_var2
    FROM some_tab
    WHERE some_col3 = some_filter;
END;

В этом примере значения из столбцов some_col1 и some_col2 будут присвоены переменным some_var1 и some_var2 соответственно.

Константы в PL/SQL

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

DECLARE
    var_name CONSTANT VARCHAR2(32) := 'Some constant value';
BEGIN
    DBMS_OUTPUT.PUT_LINE(var_name);
END;

Общий синтаксис для объявления переменных в PL/SQL следующий: 

variable_name [CONSTANT] data_type [NOT NULL] [:= | DEFAULT initial_value]

  • CONSTANT гарантирует, что значение переменной не изменится.

  • NOT NULL позволяет устанавливать ограничение на переменные, что важно, если при присвоении значения переменной может быть получен NULL.

  • DEFAULT используется для задания начального значения переменной.

То есть константа есть частный случай переменной.

Подстановочные переменные (bind/host переменные)

Подстановочные переменные (или bind-переменные) инициализируются в блоке PL/SQL, но могут быть созданы вне его. Они могут быть использованы для передачи значений в SQL-запросы.

DECLARE
    var_name VARCHAR2(32);
BEGIN
    :var_name := 'Some value';
END;

Для вывода значений этих переменных можно использовать три метода:

А также:

  • VARIABLE без создания переменной покажет список всех bind-переменных с их типами данных;

  • PRINT без имени переменной покажет список наименований переменных вместе с их значениями.

    Скрин из IDE

    Скрин из IDE

    Присваивать значение переменной не обязательно — Oracle, если не найдет что подставить вместо : var_name, выведет окошко с предложением ввести подстановочный текст.

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

Замещающие переменные

Замещающие переменные — это переменные, которые объявляются и инициализируются символьным значением. Все переменные с префиксом & будут замещены значением на этапе подготовки SQL-запроса в клиенте.

Пример использования замещающей переменной: 

DEFINE var_name = 'value';
SELECT * FROM table_
WHERE column_ = '&var_name';

Вывод в IDE

Вывод в IDE

Особенности замещающих переменных:

  1. DEFINE — выводит значение и тип данных переменной.

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

    Из IDE

    Из IDE

  3. Можно создавать неограниченное количество таких переменных.

DEFINE var_name = 'value';
DEFINE var_name2 = 'value2';
DEFINE var_name3 = 'value3';
...

Сравнение типов переменных

Вот основные различия между переменными в PL/SQL:

Тип переменной

Область видимости

Типы данных

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

PL/SQL

Локальная для блока PL/SQL

Любые типы данных

Внутри блоков PL/SQL для временных вычислений

DEFINE

Глобальная для сессии

Только строки

Для подстановки значений в запросы

VAR

Глобальная для сессии

Сложные типы данных

Для работы с результатами PL/SQL и сложными данными (например, курсоры)

Хорошая табличка со сравнением видов переменных здесь, а подробное описание тут.

Хорошая табличка со сравнением видов переменных здесь, а подробное описание тут.

Вывод данных с помощью DBMS_OUTPUT

Для вывода данных из переменных в PL/SQL можно использовать пакет DBMS_OUTPUT. Пример: 

DECLARE
    some_var VARCHAR2(32);
BEGIN
    SELECT some_col1
    INTO some_var
    FROM some_tab
    WHERE some_col2 = some_filter;
    DBMS_OUTPUT.PUT_LINE('Our value is ' || some_var);
END;

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

Общие рекомендации

  1. Использование якорных типов: рекомендуется использовать %TYPE для переменных, чтобы избежать ошибок при изменении структуры базы данных.

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

  3. Понимание областей видимости переменных: правильное использование переменных в разных областях (локальные в PL/SQL, глобальные в сессии) поможет оптимизировать выполнение программы.

  4. DBMS_OUTPUT: пакет DBMS_OUTPUT полезен для отладки и вывода значений переменных в процессе выполнения программ.
    Примечание. В SQL Developer для вывода put_line в окошко необходимо предварительно установить

SET serveroutput ON

Резюме

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

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

Планирую структуризировать дальнейшие заметки и составить еще несколько статей в том же обзорном ключе.

© Habrahabr.ru