SQL/PSM

SQL/PSM (SQL/Persistent Stored Modules) — это стандарт Международной организации по стандартизации (ISO) и Международной электротехнической комиссии (IEC), определяющий расширение языка SQL для написания хранимых модулей, включая процедуры, функции, триггеры и пакеты. Стандарт регламентирует синтаксис и семантику процедурного языка, который встраивается в SQL и позволяет реализовывать бизнес-логику непосредственно на стороне сервера баз данных. Первая версия стандарта была принята в 1996 году, а последующие уточнения вошли в состав стандартов SQL:1999, SQL:2003, SQL:2008, SQL:2011, SQL:2016 и SQL:2023.

История

До принятия стандарта SQL/PSM каждый крупный производитель систем управления базами данных (СУБД) разрабатывал собственные процедурные расширения. Наиболее заметными из них были PL/SQL компании Oracle (1989 год) и Transact-SQL (T-SQL) компаний Sybase и Microsoft (середина 1980-х годов). Отсутствие единого стандарта приводило к фрагментации знаний, сложностям с переносимостью кода между разными СУБД и привязке потребителей к одному поставщику.

Работа над стандартом началась в первой половине 1990-х годов под руководством технического комитета ISO/IEC JTC 1/SC 32 «Управление данными и обмен информацией». В 1996 году был опубликован международный стандарт ISO/IEC 9075-4:1996, который описывал язык управления потоками (условные операторы, циклы), объявление переменных, работу с курсорами и механизмы обработки исключений. Этот документ получил название SQL/PSM. В последующих версиях стандарта — начиная с SQL:1999 — раздел PSM был существенно расширен: были добавлены функции, триггеры, рекурсивные процедуры, команды для работы с большими объектами (LOB) и улучшена интеграция с реляционной моделью.

Классификация

Стандарт SQL/PSM подразделяется на несколько функциональных компонентов, каждый из которых описывает определённый тип хранимых модулей:

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

Хранимые процедуры представляют собой именованные наборы SQL-операторов, которые компилируются и сохраняются в базе данных. Они могут принимать входные (IN) и выходные (OUT) параметры, а также параметры двойного назначения (INOUT). Вызов процедуры осуществляется с помощью оператора CALL. В отличие от функций, процедуры не обязаны возвращать значение, но могут модифицировать данные.

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

Функции отличаются от процедур обязательным возвратом одного значения (скалярного типа или табличного) и строгими ограничениями на побочные эффекты при работе с данными. В стандарте выделяются скалярные функции (возвращают одно скалярное значение) и табличные функции (возвращают таблицу). Функции могут использоваться внутри SQL-выражений, например, в SELECT или WHERE.

Триггеры

Триггеры — это специальные хранимые модули, которые автоматически выполняются в ответ на определённые события, происходящие с таблицами или представлениями. В стандарте SQL/PSM поддерживаются триггеры времени BEFORE, AFTER и INSTEAD OF, а также уровни срабатывания: для каждой строки (FOR EACH ROW) и для оператора (FOR EACH STATEMENT). Триггеры позволяют реализовывать сложные бизнес-правила, аудит и каскадные изменения.

Пакеты

Пакеты (или модули) объединяют логически связанные процедуры, функции, переменные, курсоры и типы данных под общим именем. Пакеты имеют спецификацию (интерфейсную часть) и тело (реализацию). Стандарт SQL/PSM поддерживает концепцию пакетов с сокрытием реализации (инкапсуляцией) и возможностью хранения состояния в глобальных переменных уровня сессии.

Устройство и синтаксис

Основой языка SQL/PSM является блочная структура. Каждый блок (BEGIN ... END) может содержать объявления переменных, констант, локальных курсоров и обработчиков ошибок. Внутри блока допускаются любые операторы SQL, а также управляющие конструкции процедурного языка.

Переменные и типы данных

Переменные объявляются с указанием конкретного типа данных SQL, включая стандартные (INTEGER, VARCHAR, DECIMAL, DATE, TIMESTAMP), а также пользовательские типы. Объявление происходит до тела блока. Переменные могут инициализироваться при объявлении или получать значения через оператор присваивания SET или SELECT INTO.

Управляющие конструкции

• Условные операторы: IF ... THEN ... ELSEIF ... ELSE ... END IF; и CASE.
• Циклы: LOOP, WHILE ... LOOP, FOR ... AS ... CURSOR ... LOOP, REPEAT ... UNTIL ... END REPEAT. Для циклов предусмотрены операторы LEAVE (досрочный выход) и ITERATE (переход к следующей итерации).
• Обработка исключений: блок DECLARE ... CONDITION для объявления пользовательских условий, DECLARE ... HANDLER для определения обработчика (CONTINUE, EXIT, UNDO). Стандарт описывает встроенные условия SQLSTATE для диагностики.

Курсоры

Курсоры позволяют построчно обрабатывать результат запроса. Для работы с курсором используются операторы DECLARE ... CURSOR FOR ... (объявление), OPEN (открытие), FETCH ... INTO ... (извлечение строки), CLOSE (закрытие). Стандарт поддерживает как односторонние, так и прокручиваемые (SCROLL) курсоры.

Применение

Хранимые модули по стандарту SQL/PSM применяются для различных задач управления базами данных:

• Реализация бизнес-логики: Процедуры и триггеры позволяют централизовать правила, управляющие данными (например, проверка корректности вводимых значений, автоматическое выставление даты создания записи, контроль ссылочной целостности).
• Повышение производительности: Выполнение сложных запросов на стороне сервера снижает сетевой трафик и нагрузку на клиентские приложения, а компилируемый или интерпретируемый код процедур быстрее обрабатывается сервером, чем множество отдельных запросов.
• Обеспечение безопасности: Доступ к данным может быть предоставлен только через хранимые процедуры, которые скрывают внутреннюю структуру таблиц и позволяют контролировать права доступа на уровне операций.
• Аудит и журналирование: Триггеры автоматически фиксируют изменения в отдельных таблицах, создавая записи в журналах аудита.
• Пакетная обработка: Процедуры позволяют выполнять последовательности операций (например, ежемесячное начисление процентов по вкладам, генерация отчётов) без участия пользователя.

Поддержка в популярных СУБД

Несмотря на существование стандарта, многие СУБД реализуют собственные процедурные расширения, лишь частично совместимые со стандартом SQL/PSM. Основные реализации включают:

• PL/SQL (Oracle, MySQL, MariaDB): Наиболее близок к стандарту, хотя базируется на языке Ada, а не на оригинальном стандарте SQL/PSM. PL/SQL имеет развитые пакеты, поддержку коллекций и расширенную обработку исключений.
• Transact-SQL (Microsoft SQL Server, Sybase ASE): Существенно отличается синтаксисом (нет блоков BEGIN/END для всех конструкций, другой способ объявления переменных, отсутствие пакетов). Microsoft SQL Server не полностью поддерживает стандарт SQL/PSM.
• PL/pgSQL (PostgreSQL): Ориентируется на стандарт SQL/PSM, но имеет свои особенности (например, использование $$...$$ для строковых литералов, расширенная работа с массивами и пользовательскими типами).
• PSM для IBM Db2: Реализация близка к оригинальному стандарту SQL/PSM, создавалась с учётом проекта стандарта.
• Иные реализации: Sybase SQL Anywhere, SAP HANA, MariaDB (аппаратный слой SQL/PSM), InterBase/Firebird (языки процедурных расширений).

Интересные факты

• Стандарт ISO/IEC 9075-4:1996, описывающий PSM, стал первым международным стандартом, регламентирующим процедурные расширения языка SQL. До его принятия отсутствовала возможность переносимого кода.
• Термин «PSM» часто путают с «SQL/PSM». Фактически PSM — это полное название части стандарта, а «SQL/PSM» является распространённым сокращением.
• Разработчики PostgreSQL и Firebird сознательно минимизировали дивергенцию от стандарта SQL/PSM, чтобы облегчить переносимость кода из других СУБД. Это привело к появлению режима совместимости с плагином PL/PSM.
• В версии SQL:2016 в стандарт PSM была добавлена поддержка JSON-операций и ROW-типов, что отражает эволюцию языка SQL в сторону работы с полуструктурированными данными.
• Сложность полной реализации стандарта обусловлена тем, что в нём детально описан не только синтаксис, но и семантика: например, правила видимости переменных в блоках, типы соединений курсоров и последовательность обработки триггеров. Большинство коммерческих СУБД реализуют подмножество стандарта, жертвуя некоторыми возможностями ради производительности или обратной совместимости.

Критика

Несмотря на стремление к унификации, стандарт SQL/PSM не стал доминирующим на практике. Основные причины:

• Фрагментация рынка: Каждый основной производитель СУБД имеет устоявшуюся базу пользователей, привыкших к собственному языку. Перенос кода на другую платформу часто требует значительных изменений из-за диалектных различий.
• Производительность: Реализация стандарта на уровне оптимизатора и бэкенда сложна. Коммерческие СУБД часто вводят собственные оптимизации (например, компиляция процедур в машинный код), которые не регламентированы стандартом.
• Ограниченная стандартизация: В стандарте не описаны многие востребованные возможности (динамические SQL-запросы с переменным числом параметров, поддержка вложенных коллекций как типов данных, работа с внешними ресурсами через расширения). Разработчикам приходится использовать фирменные расширения, снижая переносимость.

Источники

• ISO/IEC 9075-4:1996 «Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)».
• ISO/IEC 9075-4:2023 «Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)».
• Международный стандарт SQL:1999, спецификации ISO/IEC 9075.
• MySQL 8.0 Reference Manual: «SQL Syntax for Stored Programs».
• PostgreSQL Documentation: «PL/pgSQL — SQL Procedural Language».
• Oracle Database PL/SQL Language Reference, 19c.
• Microsoft SQL Server Documentation: Transact-SQL Reference (SQL Server 2022).