Хранимые функции. За и против

Использование хранимых функций СУБД для реализации бизнес-логики или её части, всегда было камнем преткновения. С одной стороны баррикад DBA и программисты БД, с другой - разработчики backend.
Рискну навлечь на себя гнев из обоих лагерей, но всё же просуммирую плюсы и минусы и изложу свои соображения о том, когда стоит писать код в хранимых функциях, а когда следует выносить наружу.

Начнём с аргументов против:

Размазывание бизнес-логики

Это, на самом деле не проблема СУБД и ХФ, как инструмента - это проблема их неверного использования. У программиста бд может возникнуть желание описать всю логику реализуемого действия в хранимой функции - действительно, ведь все данные вот они, под рукой. Если программист поддастся на искушение, а его руководитель не возразит, в будущем могут возникнуть проблемы с узостью интерфейса со внешней системой (например, с сервером приложений) - придётся добавлять новые параметры, усложнять логику и т.п. Это даже может привести к тому, что появятся «дублирующие» ХФ со слегка иным функционалом.

Скудность языка СУБД

Есть такое дело. Традиционные языки для написания ХФ pl/sql, t-sql, pl/pgsql довольно примитивны по сравнению с современными языками общего назначения. Стоит заметить, что есть возможность писать ХФ и на более продвинутых языках, например Java в Oracle или Python в postgresql.

Непереносимость хранимых функций

Имеется в виду несовместимость диалектов процедурных языков разных СУБД. Многоплатформенность как раз на уровне - благодаря поддержке разных ОС и архитектур в самих СУБД и независимости встроенных языков от внешней платформы. Здесь опять решение зависит от специфики проекта. Если проект тиражируемый, причём вы не контролируете платформу (классический пример - CMS), то переносимость вам необходима и использование ХФ - только добавит головной боли. Если же проект уникальный, либо внедрения будут происходить унифицировано (например в разных филиалах одной компании), то про непереносимость между разными СУБД можно забыть.

Отсутствие необходимых навыков у команды и высокая «стоимость» соответствующих специалистов

Это, на мой взгляд, самый серьёзный аргумент против использования ХФ. Тут всё зависит от масштабов проекта. Грубо говоря, использование хранимого кода на стороне СУБД оправдано в средних-крупных enterprise проектах. Если проект помельче - овчинка выделки не стоит. Если проект огромный сверхнагруженный, то архитектура с ХФ и РСУБД упрётся в проблемы масштабирования - тут необходимо использование специфического хранилища и подхода к обработке данных.

Теперь плюсы:

Скорость

При обработке даже небольших объёмов данных во внешнем приложении мы тратим дополнительное время на передачу по сети и преобразование данных в нужный нам формат. К тому же в СУБД уже встроены, отлажены и протестированы близкие к оптимальным алгоритмы обработки данных, вашим программистам незачем практиковаться в изобретении велосипедов.

Сокрытие структуры данных

С ростом и эволюцией программной системы схема данных может и должна меняться. Хорошо спроектированный программный интерфейс на ХФ позволит менять схему данных не изменяя код внешних приложений (которых может быть несколько). Отсюда органично вытекает и разделение ролей разработчиков, которые работают с БД и знают её структуру, и разработчиков внешних приложений, которые должны знать лишь предоставляемый API. При использовании динамического SQL на стороне приложения, для подобного разделения вводятся дополнительные слои программных абстракций БД, различные ORM.

Гибкое управление правами доступа

Хорошей практикой является ограничение пользователя, под которым «ходит» в базу клиентское приложение в правах таким образом, что он не имеет прав на чтение и изменение никаких объектов. Лишь выполняет разрешённые ему функции. Таким образом можно жёстко контролировать какие действия доступны клиенту, уменьшается вероятность нарушения целостности данных из-за ошибки клиентского приложения.

Меньшая вероятность SQL injection

При использовании динамического SQL со стороны клиентской программы, клиентская программа передаёт СУБД SQL команды в виде строк, предварительно формируемых в коде. При формировании этих строк программисту нужно быть предельно внимательным, чтобы не допустить возможности непредусмотренной модификации SQL команды. При использовании ХФ SQL код на стороне приложения обычно статический, и выглядит, как простой вызов ХФ, параметры которой передаются не строками, а через placeholders (:variable) через механизм binding. Конечно это не исключает возможность SQL injection полностью (ведь можно умудриться в ХФ конкатенировать строку, переданную параметром с текстом динамически выполняемого SQL запроса), но значительно уменьшает её вероятность.

Повторное использование SQL

Реализуя логику работы с данными в хранимом слое мы получаем привычную нам иерархическую модель повторного использования SQL кода.
При использовании динамического SQL повторное использование запросов затруднено.
Например пусть есть система A на базе ХФ и система Б на базе динамического SQL. В обеих системах есть функция получения цены товара get_price. В случае A - это хранимая функция или отображение (view), в случае Б, допустим, процедура на java, через JDBC выполняющая SQL запрос. Есть задача - получить общую стоимость товара на складе. В случае A мы джоиним get_price прямо в запрос, получающий список товаров на складе (в случае, если get_price - view или ХФ на SQL, как например в PostgreSQL, то оптимизатор разворачивает запрос inline - тем самым получается один запрос, который быстро находит сумму).
В случае B есть два варианта - либо пробежать по курсору с выборкой товаров на складе и n раз вызвать get_price (а это значит что вся выборка должна передаться по сети на клиент) либо забыть про повторное использование и написать подзапрос, дублирующий тот, что был уже написан в get_price. Оба варианта - плохие.

Простая отладка SQL

Упрощается отладка (по сравнению с разнородной процедурой внешний код+sql)
В системах с динамическим SQL (любые ORM) даже простая задача поиска проблемного куска SQL может оказаться сложной.
Семантическая и синтаксическая проверка SQL на этапе компиляции.
Возможность профилирования функций и поиска узких мест.
Возможность трассировки уже запущеной и работающей системы.
Автоматический контроль зависимостей - при изменении определения объекта инвалидируются зависимые сущности.

Когда писать бизнес-логику в БД?

Если важна скорость обработки данных

Обработка данных прямо на месте их хранения зачастую даёт значительный прирост скорости обработки. Становятся возможными такие оптимизации, как, например, агрегации на уровне хранилища данных - данные с массива даже не передаются на сервер СУБД, не говоря о клиенте.

Когда важна целостность и непротиворечивость данных

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

Данные имеют сложную, но устоявшуюся структуру

Плоские и слабо взаимосвязанные структуры часто не требуют всего богатства инструментов обработки, которые предлагают СУБД. Для них можно использовать сверхбыстрые key-value хранилища и кеширование в памяти.
Сложно организованные сильно связанные иерархические и сетевые структуры - явный показатель, что ваши знания РСУБД пригодятся!

Когда выносить код наружу?

Работа с внешними данными

Если специфика системы такова, что данных, приходящих на обработку снаружи (с датчиков, из других систем) больше, чем данных, сохраняемых в БД, то многие плюсы БД, как платформы программирования теряются. Оказывается проще обработать поступающие даннные снаружи и сохранить результат в БД, чем сначала всё пихать в БД, а потом обрабатывать. Здесь соблюдается тот же принцип - обрабатывать данные как можно ближе к источнику, о котором мы говорили выше применительно обработке данных, уже хранящихся в БД.

Сложные алгоритмы

Сложные или высоко-оптимизированные алгоритмы-числодробилки лучше писать на более приспособленных для этого языках. Встроенные языки РСУБД очень мощны (в том смысле, что высокоуровневые, а не гибкие), но за счёт этого имеют высокий overhead.

Highload

В сверхвысоконагруженных системах обычные подходы к сериализации транзакций и синхронизации серверов кластера становятся узким местом. Для таких систем характерны уникальные решения под конкретные задачи, универсальные и мощные системы РСУБД часто оказываются слишком медлительными при нагрузках в сотни тысяч конкурентных транзакций в секунду.

Вывод такой, что чёткого алгоритма нет. Каждый раз решение остаётся за архитекторами и менеджером и от него зависит то, завязнет ли проект в проблемах с race conditions и неконсистентностью данных NoSQL, проблемах с производительностью и отладкой запросов ORM, или упрётся в проблемы масштабирования СУБД при использовании хранимых функций. Поэтому - принимайте верные решения:)

В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.

В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.

Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL - непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.

Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.

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

Отметим также, что некоторые ODBC-драйверы не поддерживают вызов хранимых процедур из клиентских приложений, даже если таковые поддерживаются самой СУБД. Однако в этом случае хранимые процедуры по-прежнему могут быть вызваны из триггеров.

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

Представления

Представление - это виртуальная таблица, обычно содержащая набор колонок одной или нескольких таблиц. В действительности представление содержит не данные, а лишь SQL-запрос типа SELECT, указывающий, какие именно данные и из каких таблиц нужно взять при обращении к этому представлению. С этой точки зрения представление - это хранимый запрос.

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

Ниже перечислены основные характеристики представлений:

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

Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации - предложение ALTER VIEW, а для удаления его - предложение DROP VIEW.

Мы начнем с оператора CREATE VIEW, позволяющего создать представление для текущей базы данных.

Предложение CREATE VIEW

Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:

CREATE VIEW view_name AS select_statement

Аргумент view_name указывает на имя представления. Ключевое слово , используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.

Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.

Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.

Предложение DROP VIEW

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

Еще один случай, когда представление нужно удалить, может возникнуть при условии, что структура таблиц, на которых оно основано, изменилась после создания представления. В этом случае можно удалить представление и затем создать его заново с помощью предложения CREATE VIEW.

Создание и использование представлений

Предложение CREATE VIEW используется для создания представлений, позволяющих извлекать данные, удовлетворяющие определенным требованиям. Представление создается в текущей базе данных и хранится как ее отдельный объект.

Наилучший способ создать представление - создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).

Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц - поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1 .

Теперь давайте создадим представление, показывающее все территории восточного региона. Это представление базируется на следующем запросе (листинг 2).

Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).

Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.

Верхняя часть View Designer позволяет указать, как связаны таблицы и какие поля будут отображаться в представлении. Ниже можно указать псевдонимы таблиц и полей, ограничения на их значения, способ отображения. Далее приведены исходный текст представления и результаты его выполнения.

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

для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
для переименования представления можно использовать системную хранимую процедуру sp_rename.

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

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

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

Хранимые процедуры позволяют повысить производительность приложений. Во-первых, по сравнению с обычными SQL-запросами, посылаемыми из клиентского приложения, они требуют меньше времени для подготовки к выполнению, поскольку они уже скомпилированы и сохранены. Во-вторых, сетевой трафик в этом случае также меньше, чем в случае передачи SQL-запроса, так как по сети передается меньшее количество данных. Рис. 3 иллюстрирует вызов хранимой процедуры клиентским приложением.

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

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

Для создания, изменения и удаления процедур существуют специальные SQL-предложения - CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.

Предложение CREATE PROCEDURE

Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:

CREATE PROC proc_name [ {@parameter data_type} [= default] ] [...] AS sql_statements

Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.

Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.

Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию - это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, и [^]).

Ключевое слово OUTPUT показывает, что это возвращаемый параметр.

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

Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст - в таблице syscomments.

Для изменения созданной ранее хранимой процедуры следует использовать предложение ALTER PROCEDURE, кратко описанное в следующем разделе.

Предложение DROP PROCEDURE

Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент - имя удаляемой процедуры.

При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.

Создание и использование хранимых процедур

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

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

Чтобы выбрать другой регион, нам нужно изменить условие в предложении WHERE в последней строке запроса. Следовательно, если мы используем переменную (назовем ее RegID), мы сможем выбрать один из четырех регионов без изменения других частей запроса.

В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.

Результат выполнения созданной процедуры в SQL Server Query Analyzer для RegID =2 показан на рис. 3 .

Очевидно, что мы можем применять хранимые процедуры не только для реализации расширенных версий представлений или «интеллектуальных» запросов SELECT. Хранимые процедуры предоставляют механизмы, позволяющие автоматизировать многие рутинные задачи.

В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:

sp_stored_procedures - показывает список хранимых процедур;
sp_helptext - показывает исходный текст хранимой процедуры;
sp_depends - показывает сведения о зависимостях хранимых процедур;
sp_procoption - устанавливает опции хранимых процедур или устанавливает их;
sp_recompile - перекомпилирует процедуру в момент ее следующего вызова;
sp_rename - меняет имя процедуры.

Системные хранимые процедуры

Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.

Обратите внимание на то, что триггеры не должны возвращать пользователю данные.

В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:

SELECT * FROM deleted

В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.

Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.

Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID

Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения - в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.

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

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

Заключение

В этой части мы рассмотрели несколько типов объектов баз данных - хранимые процедуры, представления и триггеры. Мы узнали следующее:

Представление - это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации - предложение ALTER VIEW, а для удаления - предложение DROP VIEW.
Хранимая процедура - это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения - ALTER PROCEDURE, а для удаления - DROP PROCEDURE.
Tриггер - это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления - предложение DROP TRIGGER.

КомпьютерПресс 12"2000

Определяется понятие хранимых процедур. Приводятся примеры создания, изменения и использования хранимых процедур с параметрами. Дается определение входных и выходных параметров. Приводятся примеры создания и вызова хранимых процедур.

Понятие хранимой процедуры

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

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

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

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

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

Хранимые процедуры в среде MS SQL Server

При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.

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

В SQL Server имеется несколько типов хранимых процедур .

Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_ , хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа # . Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ## . Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

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

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

<определение_процедуры>::= {CREATE | ALTER } PROC имя_процедуры [;номер] [{@имя_параметра тип_данных } [=default] ][,...n] AS sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_ , # , ## , создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

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

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры , годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры , т.е. с указанием ключевого слова OUTPUT .

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

Ключевое слово VARYING применяется совместно с параметром OUTPUT , имеющим тип CURSOR . Оно определяет, что выходным параметром будет результирующее множество.

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

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

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

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры} [,...n]

Выполнение хранимой процедуры

Для выполнения хранимой процедуры используется команда:

[[ EXEC [ UTE] имя_процедуры [;номер] [[@имя_параметра=]{значение | @имя_переменной} |][,...n]

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

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров , которые были объявлены при создании процедуры с ключевым словом OUTPUT .

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

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

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

Пример 12.1. Процедура без параметров . Разработать процедуру для получения названий и стоимости товаров, приобретенных Ивановым.

CREATE PROC my_proc1 AS SELECT Товар.Название, Товар.Цена*Сделка.Количество AS Стоимость, Клиент.Фамилия FROM Клиент INNER JOIN (Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара) ON Клиент.КодКлиента=Сделка.КодКлиента WHERE Клиент.Фамилия=’Иванов’ Пример 12.1. Процедура для получения названий и стоимости товаров, приобретенных Ивановым.