Введение в PL/SQL

Для меня 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;

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

%ROWTYPE

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

При обращении к полям записи используется тот же принцип, что и при обращении к таблицам внутри схем (scheme.table_name): RECORD.field_n.

______________________________________________________________________________

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

DBMS_OUTPUT.put('string') -- добавляет строку в буфер (без перевода строки) DBMS_OUTPUT.put_line('string') -- добавляет строку в буфер (с переводом строки)

-- Перед кодом SET serveroutput ON  -- Внутри блока BEGIN DBMS_OUTPUT.enable() -- обратная команда: DBMS_OUTPUT.disable()

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;

Следует учитывать, что либо записывается все (*) в Record, либо записываются конкретные столбцы в указанные явно Fields:

– – – – – – – – – – – – – – ERROR – – – – – – – – – – – – – – – DECLARE       employee_record employees%ROWTYPE; BEGIN       SELECT employee_id       INTO employee_record      FROM employees       WHERE employee_id = 100;       DBMS_OUTPUT.PUT_LINE('ID: ' || employee_record.employee_id); END; – – – – – – – – – – – – – – ERROR – – – – – – – – – – – – – – –

– – – – – – – – – – – – – – RIGHT – – – – – – – – – – – – – – – DECLARE       employee_record employees%ROWTYPE; BEGIN       SELECT employee_id       INTO employee_record.employee_id       FROM employees       WHERE employee_id = 100;       DBMS_OUTPUT.PUT_LINE('ID: ' || employee_record.employee_id); END; – – – – – – – – – – – – – – RIGHT – – – – – – – – – – – – – – –

Удобства:

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

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

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

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

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

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

TYPE type_rec_name IS RECORD  (field_1 datatype,                                                               field_2 datatype,                                                                ...                                 field_n datatype); var_rec type_rec_name;

Пример:

DECLARE   TYPE DeptRecTyp IS RECORD (     dept_id    NUMBER(4) NOT NULL := 10,         dept_name  VARCHAR2(30) NOT NULL := 'Administration',         mgr_id    NUMBER(6) := 200,         loc_id    NUMBER(4) := 1700     );   dept_rec DeptRecTyp; BEGIN   DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.dept_id);   DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);   DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.mgr_id);   DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.loc_id); END;

Пример взят отсюда. Там же можно найти иные примеры, а также некоторую дополнительную информацию.

______________________________________________________________________________

Блок основного кода (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;

• SQLCODE возвращает числовой код ошибки.

• SQLERRM возвращает текстовое описание ошибки.

______________________________________________________________________________

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

Можно определять свои исключения и выбрасывать их с помощью 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;

2. Получение значения через 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-переменные, VAR) инициализируются (получают значение) в блоке PL/SQL (либо ручной ввод с клавиатуры), но могут быть созданы и вне PL/SQL. Они могут быть использованы для передачи значений в SQL-запросы.

DECLARE     var_name VARCHAR2(32);     var_name2 VARCHAR2(32);     tmp NUMBER; BEGIN     :var_name := 'Some value';     SELECT id     INTO tmp     FROM tablename     WHERE col_name = :var_name; -- Попробуйте и :var_name2, наглядно увидите разницу END;

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

• DBMS_OUTPUT.PUT_LINE();

• PRINT var_name;

• SET autoprint ON; — автоматический вывод значения переменной при вызове.

А также:

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

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

  

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

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

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

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

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

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

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

2. Можно объявлять и использовать вне PL/SQL;

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

   

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

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

______________________________________________________________________________

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

Вот основные различия между переменными в 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.

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

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