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

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

Основные сведения о хранимых процедурах

Выше мы показывали как создавать, исполнять и сохранять в Query Analyzer команды и пакеты Transact-SQL в виде сценариев, написанных на языке Transact-SQL. При исполнении сохраненных сценариев SQL Server обрабатывал содержащиеся в них команды для вывода результирующих наборов, администрирования SQL Server и манипулирования информацией, хранящейся в базе данных. Как правило, при сохранении сценария файлу присваивается расширение «.SQL». Существует и другой способ сохранения сценария Transact-SQL: присвоить ему имя и сохранить в виде хранимой процедуры. Известно несколько спссобов вызова сохраненного таким образом сценария, например через Query Analyzer.

Ранее мы упоминали и даже использовали ряд хранимых процедур (sp_help или sp_helpconstraint). Они хранятся в базе данных Master и содержат команды Transact-SQL для просмотра различных свойств объектов баз данных.

Назначение и преимущества хранимых процедур

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

При пересылке каждого оператора (или пакета операторов) Transact-SQL на сервер для обработки последний должен определить, есть ли у отправителя права на исполнение этих команд и допустимы ли сами команды. Проверив права доступа и синтаксис команд, SQL Server строит план исполнения запроса.

Хранимые процедуры в данном случае более эффективны. При создании они сохраняются в SQL Server, поэтому при вызове хранимой процедуры ее содержимое сразу же обрабатывается сервером. Один-единственный оператор позволяет вызвать сложный сценарий Transact-SQL, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд.

Перед созданием хранимой процедуры ее команды проходят синтаксическую проверку. Если при этом не обнаружено ни одной ошибки, имя процедуры сохраняется в таблице SysObjects, а ее текст — в таблице SysComments. При первом запуске хранимой процедуры создается план исполнения и хранимая процедура компилируется. В дальнейшем ее обработка осуществляется быстрее, поскольку SQL Server не приходится проверять синтаксис команд, создавать план исполнения и компилировать текст процедуры. До создания нового плана в кэше проверяется наличие существующего плана исполнения.

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

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

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

Если разработчикам удобно писать сложные программы на таких языках, как C# или C++, то затем эти программы можно вызывать из SQL Server средствами хранимых процедур особого типа, которые называются расширенными хранимыми процедурами.

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

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

Категории хранимых процедур

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

Системные хранимые процедуры находятся в базе данных Master. Как правило, их имена начинаются с префикса sp_. Они предназначены для поддержки функций SQL Server (в частности, процедур для работы с каталогом). К ним относится выборка данных из системных таблиц внешними приложениями, администрирование базы данных и управление безопасностью. Например, средствами хранимой процедуры для работы с каталогом sp_table_privileges можно просматривать привилегии для таблиц. В следующем операторе системная хранимая процедура sp_table_privileges отображает привилегии для таблицы Persons из базы данных People:

USE People

GO

EXECUTE sp_table_privileges Persons

Очень часто администратору требуется просмотреть сведения о пользователях и процессах текущей базы данных. Этот важный этап предшествует закрытию базы данных. Следующий оператор с помощью системной хранимой процедуры sp_who выводят все процессы пользователя MyCalculator\Administrator:

EXECUTE sp_who @loginame = 'MyCalculator\Administrator'

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

EXECUTE sp_validatelogins

В SQL Server включены сотни системных хранимых процедур; их полный список приведен в разделе «System Stored Procedures» замечательного справочника Books Online, В котором упоминается и несколько расширенных хранимых процедур, о которых мы поговорим в следующих разделах.

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

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

Временная хранимая процедура похожа на локальную, однако она существует лишь до закрытия соединения, в котором создана, или до завершения работы SQL Server. В зависимости от типа такая процедура удаляется после завершения работы сервера или разрыва соединения. Непостоянство обусловлено тем, что временные хранимые процедуры находятся в базе данных TempDB. При каждом запуске сервера эта база создается заново, поэтому после закрытия сервера все объекты этой базы данных исчезают. Временные хранимые процедуры полезны при работе с более ранними версиями SQL Server, которые не поддерживают повторное использование планов исполнения, а также в тех случаях, когда нет смысла сохранять процедуру, поскольку значения ее параметров постоянно меняются. Существует три типа временных хранимых процедур: локальные (или закрытые), глобальные и создаваемые непосредственно в TempDB. Локальная процедура всегда начинается с символа #, а глобальная — с ##. Создание каждого типа временных хранимых процедур описано в следующем параграфе. При исполнении временной хранимой процедуры ее область действия ограничена соединением, в котором она создана. Однако такая процедура видима всем пользователям, установившим соединение с базой данных, в окне Object Browser в Query Analyzer. Ограниченность области ее действия исключает возникновение конфликтов имен с другими соединениями, в которых созданы временные хранимые процедуры. Чтобы гарантировать уникальность имени временной хранимой процедуры, SQL Server добавляет к нему набор символов подчеркивания и уникальный номер соединения. Привилегии для локальной процедуры не предоставляются другим пользователям. Временная хранимая процедура удаляется из TempDB при закрытии соединения, в котором она создана.

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

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

• для них разрешается настроить права доступа;

• они сохраняются даже после завершения соединения, в котором созданы;

• они не удаляются до завершения работы SQL Server.

Поскольку процедуры этого типа создаются непосредственно в TempDB, важно полностью определять имя объекта базы данных в коде Transact-SQL. Например, на таблицу Persons из базы данных People, принадлежащую dbo, следует ссылаться по имени people.dbo.persons.

Расширенные хранимые процедуры обращаются к внешним программам, скомпилированным в виде 32-разрядных DLL. Некоторые системные хранимые процедуры также рассматриваются как расширенные. Например, хранимая процедура xp_sendmail, которая посылает по заданному адресу почтовое сообщение с вложенным результирующим набором, является системной и расширенной одновременно. Соглашение об именовании предполагает использование в именах расширенных хранимых процедур префикса хр_. Однако имена некоторых расширенных процедур начинаются с префикса sp_, а в именах некоторых других, не расширенных процедур используется префикс хр_. Поэтому нельзя различить системные и расширенные хранимые процедуры, полагаясь лишь на отличия в именах.

Определить, является ли хранимая процедура расширенной, позволяет функция ОВJECTPROPERTY. Она возвращает для свойства IsExtendedProc значение 1, если процедура является расширенной, или 0, если процедура таковой не является. Следующие примеры демонстрируют, как можно выяснить, что процедура sp_prepare — расширенная, а xp_logininfo — нет:

USE Master

-- Расширенная хранимая процедура с префиксом sp_.

SELECT OBJECTPROPERTY(object_id('sp_prepare'), 'IsExtendedProc')

GO

Этот пример возвращает значение 1.

USE Master

-- Хранимая процедура, которая не является расширенной,

-- но имеет префикс хр_

SELECT OBJECTPROPERTY(object_id('xp_logininfo'), 'IsExtendedProc')

GO

Этот пример возвращает значение 0.

Удаленная хранимая процедура работает на удаленной копии SQL Server. Удаленные хранимые процедуры оставлены для совместимости с предыдущими версиями, в SQL Server 2005 вместо них используются распределенные запросы.

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

Процедуру разрешается создавать раньше объектов, на которые она ссылается. Эта возможность называется отложенным разрешением имен. Для создания процедур используют ключевые слова CREATE PROCEDURE.

Перед исполнением процедуры следует задать значения всех необходимых параметров. Хранимые процедуры можно исполнять вручную или автоматически (при запуске SQL Server). Для исполнения процедуры служит ключевое слово EXECUTE. Его можно опустить, если исполняемая процедура состоит из единственной строки или имя процедуры является первым словом пакета.

Часто приходится модифицировать созданные хранимые процедуры, например, чтобы добавить параметр или изменить ту или иную команду. Модификация процедуры вместо удаления и повторного создания «с нуля» позволяет сэкономить время, поскольку при модификации сохраняются многие свойства хранимых процедур (например, права доступа). Для модификации процедур используются ключевые слова ALTER PROCEDURE.

Для удаления процедур используют ключевое слово DROP. Процедуру можно удалить в Server Management Studio или Query Analyzer, выбрав ее и нажав клавишу DELETE. He следует удалять процедуру до тех пор, пока все зависящие от процедуры объекты не будут удалены или модифицированы (чтобы удалить зависимость).

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

Во время создания процедуры SQL Server проверяет синтаксис составляющих ее операторов Transact-SQL. При обнаружении синтаксической ошибки SQL Server генерирует сообщение типа «syntax incorrect», и процедура не создается. Если текст процедуры проходит синтаксическую проверку, то процедура сохраняется, при этом ее имя и другая информации (например, автоматически генерируемый идентификационный номер) записывается в таблицу SysObjects, а текст процедуры — в таблицу SysComments текущей базы данных.

Оператор SELECT запрашивает идентификационный номер хранимой процедуры ByBirthday из таблицы SysObjects базы данных People:

SELECT [name], [id] FROM [people].[dbo].[SysObjects]

WHERE [name] = 'bybirthday'

Следующий оператор SELECT с помощью информации из таблицы SysObjects (идентификационного номера хранимой процедуры ByBirthday) выполняет запрос к таблице SysComments:

SELECT [text] FROM [people].[dbo].[SysComments]

WHERE [id] = 111577110

Этот запрос возвращает исходный текст хранимой процедуры ByBirthday, идентификационный номер которой равен 111577110.

Понятно, что оба показанных оператора SELECT можно объединить средствами ключевого слова JOIN. Для простоты и ясности здесь мы привели их по отдельности.

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

Методы создания хранимых процедур

SQL Server предоставляет несколько методов для создания хранимых процедур: оператор Transact-SQL CREATE PROCEDURE, SQL-DMO (при использовании объекта StoredProcedure), консоль Server Management Studio и мастер Create Stored Procedure.

Оператор CREATE PROCEDURE (или его сокращенная версия CREATE PROC) предназначен для создания хранимых процедур с помощью Query Analyzer или утилит командной строки, например osql. CREATE PROC позволяет выполнять следующие задачи:

• определять сгруппированные хранимые процедуры;

• определять входные и выходные параметры, их типы данных и значения по умолчанию. Определение параметра всегда начинается со знака «at» (@), после которого следует имя параметра и описание его типа данных. В описании входных параметров должно присутствовать ключевое слово OUTPUT, которое позволяет отличить их от входных параметров;

• выводить сведения об успешном или неудачном завершении задачи;

• управлять кэшированием плана исполнения процедуры;

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

• контролировать режим исполнения процедуры для подписчика репликации;

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

Все хранимые процедуры, кроме временных, всегда создаются в текущей базе данных. Поэтому перед созданием процедуры следует выбирать текущую базу данных с помощью оператора USE имя_базы_данных, после которого указана пакетная команда GO. Для выбора текущей базы данных также используют раскрывающийся список Change Database в Query Analyzer.

Первый пакет показанного далее сценария делает базу данных People текущей и создает процедуру ListPeopleNames, владельцем которой является dbo:

USE People

GO

CREATE PROCEDURE [dbo].[ListPeopleNames]

AS

SELECT [au_fname], [au_lname] FROM [people].[dbo].[persons]

GO

Обратите внимание, что здесь используется полное имя процедуры. Полное имя хранимой процедуры состоит из имени владельца процедуры (в этом случае dbo) и имени самой процедуры — ListPeopleNames. Если процедуру следует исполнять независимо от владельца таблицы или базы данных, владельцем процедуры должен быть dbo. При использовании оператора CREATE PROCEDURE имя базы данных не входит в полное имя хранимой процедуры.

Чтобы создать временную хранимую процедуру, следует добавить к имени символ #. Этот знак сообщает SQL Server, что создать процедуру надо в базе TempDB. Чтобы создать глобальную процедуру, необходимо добавить к ее имени символы ##, — SQL Server создаст ее в базе данных TempDB. При создании временных хранимых процедур SQL Server игнорирует текущую базу данных. Временные хранимые процедуры по определению существуют только в TempDB. Чтобы создать процедуру (за исключением локальной или глобальной) непосредственно в базе TempDB, следует вначале сделать TempDB текущей базой данных. В показанном далее фрагменте кода создаются три временных хранимых процедуры: локальная, глобальная и размещенная непосредственно в TempDB:

-- Создать локальную временную хранимую процедуру.

CREATE PROCEDURE fllocaltemp

AS

SELECT * from [people].[dbo].[persons]

GO

-- Создать глобальную временную хранимую процедуру.

CREATE PROCEDURE ttflglobaltemp

AS

SELECT * from [people].[dbo].[persons]

GO

-- Создать временную хранимую процедуру непосредственно в tempdb.

USE TEMPDB

GO

CREATE PROCEDURE directtemp

AS

SELECT * [people].[dbo].[persons]

GO

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

В следующем примере временная хранимая процедура создается непосредственно в ТеmрDB. Для этого необходимо сделать базу данных TempDB текущей (USE TempDB) перед исполнением команды или использовать ее полное имя ([TempDB].[dbo].[directtemp]). Подобно хранимым процедурам из базы данных Master, локальные и глобальные процедуры разрешается вызывать, указывая краткие имена (независимо от текущей базы данных).

После создания хранимые процедуры можно логически связать посредством группировки. Такая методика полезна для тех процедур, которые необходимо администрировать совместно и использовать как единое целое. Чтобы сгруппировать хранимые процедуры, следует присвоить каждой процедуре в группе одинаковое имя с уникальным для каждой процедуры номером, отделенным точкой с запятой. Например, если во время создания присвоить двум процедурам имена GroupedProc;1 и GroupedProc;2, то эти процедуры будут логически сгруппированы. При просмотре содержимого GroupedProc отображается код обеих процедур, GroupedProc;1 и GroupedProc;2.

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

Чтобы вызывать перекомпиляцию хранимой процедуры при каждом ее исполнении, при создании следует добавить к процедуре ключевые слова WITH RECOMPILE. Разрешается также принудительно выполнить перекомпиляцию с помощью хранимой процедуры sp_rеcompile или ключевых слов WITH RECOMPILE, заданных при исполнении процедуры.

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

Например, вот как создать зашифрованную процедуру Protected:

USE People

GO

CREATE PROC [dbo].[protected] WITH ENCRYPTION

AS

SELECT [au_fname], [au_lname] FROM [people].[dbo].[persons]

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

-- Проверить, зашифрована ли хранимая процедура.

-- Если это так, вернуть значение 1 в IsEncrypted

SELECT OBJECTPROPERTY(object_id('protected'), 'IsEncrypted')

или путем вызова процедуры с sp_helptext:

-- Если хранимая процедура зашифрована,

-- вернуть "The object comments have been encrypted."

EXEC sp_helptext protected

Зашифрованную хранимую процедуру нельзя реплицировать. SQL Server расшифровывает ее для исполнения. Однако никому, даже владельцу, не удастся просмотреть исходный текст хранимой процедуры в незашифрованном виде. Поэтому обязательно в надежном месте хранят незашифрованную версию определения хранимой процедуры. Чтобы модифицировать процедуру, следует отредактировать незашифрованную версию и снова сохранить ее и безопасном месте. После этого можно модифицировать существующую зашифрованную хранимую процедуру средствами оператора ALTER PROCEDURE с шифрованием (об операторе ALTER PROCEDURE рассказано далее).

Во время создания хранимой процедуры SQL Server не проверяет существование объектов, на которые ссылается процедура Такая возможность необходима, поскольку иногда объект (например, таблица), на который ссылается хранимая процедура, не существует на момент создания процедуры. Эта функция называется отложенным разрешением имен.

Объект проверяется уже при исполнении хранимой процедуры. При ссылке в хранимой процедуре на объект (например, на таблицу), обязательно следует указывать его владельца. По умолчанию SQL Server предполагает, что создатель хранимой процедуры одновременно является владельцем объектов, на которые она ссылается. Во избежание путаницы, при создании самих хранимых процедур и объектов, на которые они ссылаются, следует указывать dbo в качестве их владельца.

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

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

Если хранимая процедура не является первым оператором пакета, то для запуска хранимой процедуры необходимо предварить ее имя ключевым словом EXECUTE (или его сокращенной версией — EXEC).

Для вызова хранимой процедуры достаточно указать ее полное имя, например [имя__базы_данных].[владелец].[имя_процедуры]. Если мы сделаем базу данных, в которой содержится хранимая процедура, текущей (USE имя_базы_данных), то для вызова процедуры достаточно указать часть имени: [владелец].[имя_процедуры], а если имя процедуры уникально в активной базе данных, то можно использовать просто [имя_процедуры].

При исполнении системных хранимых процедур с префиксом sp_, а также локальных и глобальных процедур не обязательно использовать полные имена. SQL Server будет искать в базе данных Master любую хранимую процедуру с префиксом sp_, владельцем которой является dbo. Если мы все-таки решим присвоить локальной и системной процедурам одинаковые имена, то обязательно нужно указать в первом случае имя владельца, отличное от dbo. SQL Server не выполняет поиск расширенных хранимых процедур в базе данных Master автоматически. Поэтому следует либо указывать полное имя расширенной хранимой процедуры, либо сделать активной базу данных, в которой она находится.

Если хранимой процедуре требуются параметры, при исполнении процедуры необходимо задать их значения. Определения входных и выходных параметров начинаются со знака @, после которого следует имя параметра и описание его типа данных. При вызове функции необходимо задать значение параметра и (не обязательно) его имя. В следующих двух примерах хранимая процедура pu_info из базы данных People запускается с двумя параметрами — @lastname и @firstname:

-- Вызвать хранимую процедуру с параметрами.

USE People

GO

EXECUTE pu_info Putin, Vladimir

-- Вызвать хранимую процедуру с указанием имен и значений

параметров.

USE People

GO

EXECUTE pu_info

@lastname = 'Putin', @firstname = 'Vladimir'

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

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

Ниже приводится список некоторых вариантов синтаксиса при исполнении хранимых процедур.

• Хранимая процедура возвращает значение в целочисленной переменной, определенной для хранения кодов возврата. В хранимой процедуре должно присутствовать ключевое слово RETURN с целочисленным значением (значениями).

• Для вызова процедуры используется ее номер в группе, указанный после точки с запятой. Если хранимые процедуры сгруппированы, можно исполнить все их, просто указав имя хранимой процедуры, или выбрать для исполнения определенную процедуру, задав ее номер. Например, если созданы процедуры с именами GroupedProc;1 и GroupedРгос;2, командой EXEC groupedproc можно запустить обе эти процедуры, а командой EXEC groupedproc;1 — только первую процедуру. При определении параметров сгруппированных хранимых процедур каждому параметру необходимо давать уникальное в пределах группы имя, а значения параметров сгруппированных хранимых процедур можно задавать как обычно.

• В хранимой процедуре определены переменные для хранения параметров. Перед командой EXECUTE определяются переменные с помощью ключевого слова DECLARE.

Используя процедуру sp_procoption, можно пометить некоторые хранимые процедуры для исполнения при запуске SQL Server. Это позволяет реализовать ряд задач для повышения производительности, администрирования системы, а также выполнять задачи в фоновом режиме. Процедура sp_procoption принимает три параметра: @ProcName, @OptionName и @OptionValue. Вот как можно настроить процедуру AutoStart для автоматического запуска:

USE Master

GO

EXECUTE sp_procoption

@procname = autostart,

@optionname = startup,

@optionvalue = true

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

Кроме того, для настройки автоматического запуска можно использовать SQL Server Management Studio. Для этого открывается базу данных Master и «кликается» узел Stored Procedures. Затем выбирается хранимую процедура, которую нужно запускать автоматически, и щелкните ее правой кнопкой. Далее в диалоговом окне свойств хранимой процедуры устанавливается флажок Execute Whenever SQL Server Starts.

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

USE Master

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

SELECT OBJECTPROPERTY(object_id('autostart'), 'ExecIsStartup')

Отключают автоматический запуск посредством хранимой процедуры sp_configure. Показанный далее оператор настраивает SQL Server так, что помеченные для автоматического запуска хранимые процедуры не исполняются во время следующего запуска SQL:

EXECUTE sp_configure

@configname = 'scan for startup procs', @configvalue = 0

RECONFIGURE

GO

Модификация хранимой процедуры

Оператор ALTER PROCEDURE (или его сокращенная версия ALTER PROC) позволяет модифицировать содержимое пользовательской хранимой процедуры с помощью Query Analyzer или инструмента командной строки, например osql. Синтаксис оператора ALTER PROCEDURE почти полностью идентичен синтаксису CREATE PROCEDURE.

Ценность применения оператора ALTER PROCEDURE вместо удаления и создания процедуры «с нуля» заключается в том, что ALTER PROCEDURE сохраняет большинство свойств процедуры (например, ее идентификатор объекта, набор прав доступа и флаги).

Чтобы оставить параметр шифрования или перекомпиляции, необходимо задать соответствующие ключевые слова (WITH ENCRYPTION и/или WITH RECOMPILE) при исполнении оператора ALTER PROCEDURE.

Для изменения пользовательских хранимых процедур обычно применяют SQL Server Management Studio и Query Analyzer.

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

USE People

GO

EXECUTE sp_rename

@objname = 'bybirthday', @newname = 'BirthdayByPersonId',

@objtype = 'object'

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

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

Для удаления одной пользовательской хранимой процедуры, нескольких процедур одновременно или набора сгруппированных процедур используют оператор DROP PROCEDURE или его сокращенную версию DROP PROC. Вот как удалить из базы данных People две процедуры с именами Procedure01 и Procedure02:

USE people

GO

DROP PROCEDURE procedure01, procedure02

База данных People — текущая. При удалении процедуры нельзя указывать имя базы данных, полное имя процедуры выглядит так: [владелец].[имя_процедуры]. При удалении системной хранимой процедуры, определенной пользователем (с префиксом sp_), оператор DROP PROCEDURE ищет ее в текущей базе данных. Если он здесь не найдет определенную пользователем системную хранимую процедуру с таким именем, то обратится к базе данных Master.

Чтобы удалить группу хранимых процедур, необходимо задать имя процедуры. С помощью оператора DROP PROCEDURE нельзя удалить часть группы. Например, не удастся удалить из набора сгруппированных процедур GroupedProc, состоящего из процедур GroupedProc;1 и GroupedProc;2, только процедуру GroupedProc;1, не удалив при этом GroupedРгос;2. Если нужно удалить часть группы, придется удалить всю группу и создать нужные процедуры заново.

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

Программирование хранимых процедур

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

Мы расскажем о переменных и покажем, как выполняется обработка ошибок в хранимых процедурах с помощью кодов возврата и функции @@ERROR. В завершение мы обсудим вложенные процедуры.

Параметры и переменные — основа динамичности хранимых процедур. Входные параметры позволяют пользователю, исполняющему процедуру, передавать процедуре различные значения и получать в каждом случае конкретные уникальные результаты. Выходные параметры позволяют получать не только результирующий набор, но и дополнительные сведения. Во время исполнения процедуры значения выходных параметров сохраняются в памяти. Чтобы получить значение выходного параметра, необходимо создать переменную для его хранения. Полученное значение можно отобразить с помощью команд SELECT и PRINT или использовать его для выполнения других команд процедуры. Входной параметр определяют в хранимой процедуре, а его значение задают при ее исполнении.

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

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

USE People

GO

CREATE PROCEDURE dbo.TitleForPerson

@Title varchar(80), -- Это первый входной параметр.

@PersonId int OUTPUT, -- Это первый выходной параметр.

@TitleText varchar(60) OUTPUT -- Это второй выходной параметр.

AS

-- Присвоить данные столбца выходным параметрам

-- и проверить наличие должности, заданной входным параметром.

SELECT @PersonId = PersonId, @TitleText=title

FROM titles WHERE title LIKE @Title

GO

Следующий оператор исполняет процедуру TitleForPerson:

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

DECLARE @p_PersonId int, @t_TitleText varchar(80)

EXECUTE TitleForPerson

-- Значения выходных параметров будут присвоены объявленным переменным.

@PersonId = @p_PersonId OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "%Docent%" -- Задать значение входного параметра.

-- Вывести значения переменных, полученные

-- в результате исполнения процедуры.

Select "Title" = @t_TitleText, "PersonId" = @p_PersonId

Объявляются две переменные, получающие значения, которые хранятся в выходных параметрах. Типы данных, объявленные для этих переменных, совпадают с типами данных соответствующих выходных параметров. Эти переменные могут иметь те же имена, что и выходные параметры, поскольку в хранимых процедурах переменные локальны для пакета, в котором они объявлены. Для ясности здесь использованы имена переменных, отличные от имен выходных параметров. При объявлении переменной ее значение не совпадает со значением выходного параметра. Значение переменных становятся равными значениям выходных параметров лишь после исполнения оператора EXECUTE. После присвоения переменным значений параметров задано ключевое слово OUTPUT. Без него оператор SELECT, расположенный в конце пакета, не сможет вывести значения переменных. Обратим также внимание на значение входного параметра @Тitlе, которое равно %Docent%. Это значение передается и конструкцию WHERE оператора SELECT хранимой процедуры. Поскольку в конструкции WHERE указано ключевое слово LIKE, можно использовать знаки подстановки, например %. Полученный в результате запрос настроен на поиск должностей, в которых содержится слово “Docent”.

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

DECLARE @p_PersonId int, @t_TitleText varchar(80)

EXECUTE TitleForPerson

"%Docent%". -- устанавлиеает значение входного параметра.

@p_PersonId OUTPUT, -- получает первый выходной параметр

@t_TitleText OUTPUT -- получает второй выходной параметр

-- Вывести значения переменных, полученные в результате

-- исполнения процедуры.

Select "Title" = @t_TitleText, "Person Id" = @p_PersonId

GO

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

Оператор RETURN и обработка ошибок

Часто основные усилия при программировании качественных хранимых процедур затрачиваются на реализацию обработки ошибок. SQL Server предоставляет функции и операторы для обработки ошибок, возникающих во время исполнения процедуры. Ошибки можно разделить на две основные категории: связанные с компьютерами, например, когда недоступен сервер баз данных, и пользовательские. Для обработки ошибок, возникающих во время исполнения процедур, используются коды возврата и функция @@ERROR.

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

Рассмотрим процедуру TitleForPerson, которую мы создали и исполнили в предыдущем параграфе. Если в базе данных нет строк, соответствующих значению входного параметра (@Тitlе), процедура возвращает результирующий набор, состоящий из значений NULL.

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

ALTER PROCEDURE dbo.TitleForPerson

@Title varchar(80), -- Это первый входной параметр.

@PersonId int OUTPUT, -- Это первый выходной параметр.

@TitleText varchar(60) OUTPUT -- Это второй выходной параметр.

AS

-- Проверить, есть ли в базе данных заданные должности. Если нет,

-- выйти из процедуры и установить код возврата равным 1,

IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0

RETURN(1)

ELSE

SELECT @PersonId = PersonId, @TitleText=title

FROM titles WHERE title LIKE @Title

GO

Оператор IF, стоящий после ключевого слова AS, определяет, задан ли входной параметр при исполнении процедуры и соответствуют ли ему какие-либо записи в базе данных. Если функция COUNT возвращает 0, то код возврата устанавливается равным 1: RETURN(1). Если функция COUNT возвращает значение, отличное от 0, то оператор SELECT делает запрос к таблице Title. В этом случае код возврата равен 0.

Чтобы задействовать коды возврата, придется перепрограммировать операторы для исполнения процедуры. В следующем примере входному параметру @Title присваивается значение Docent%;

-- Добавить переменную @r_Code дня хранения результирующего кода.

DECLARE @p_PersonId int, @t_TitleText varchar(80), @r_Code int

-- Запустить процедуру и установить @r_Code равной результату процедуры.

EXECUTE @r_Code = TitleForPerson

@PersonId = @p_PersonId OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "Docent%"

-- Определить значение @r_Code и исполнить программу.

IF @r_Code = 0

SELECT "Title" = @t_TitleText,

"PersonId" = @p_PersonId,

"Return Code" = @r_Code

ELSE IF @r_Code = 1

PRINT 'No matching titles in the database. Return code-' +

CONVERT(varchar(1), @r_Code)

GO

К оператору DECLARE была добавлена новая переменная — @r_Code. Далее эта переменная хранит значение, возвращаемое оператором RETURN. Она определена как целочисленная, поскольку код возврата передается как целое число. В строке с оператором EXECUTE переменной @r_Code присваивается значение кода возврата. Переменная @r_Code содержит значение, которое возвращает хранимая процедура. Теперь вместо %Docent% для входного параметра @Title задано значение Docent%. Другими словами, оператор SELECT будет искать в таблице Titles названия, значения которых начинаются со слова «Docent». Ниже параметров и комментария размещено условие. Сначала выполняется проверка ветви IF. Если процедура находит запись, код возврата равен 0 и выполняется оператор SELECT. Если процедура не находит ни одной соответствующей записи, переменная @r_Code равна 1 и исполняется оператор PRINT. Поскольку в базе данных может не быть должности, название которой начинается со слова «Docent», результат процедуры определяет оператор PRINT.

Чтобы проверить, задано ли значение входного параметра @Title, необходимо установить для него значение по умолчанию и модифицировать хранимую процедуру, добавив к ней еще один условный оператор. Значение по умолчанию — мощное средство, позволяющее избегать ошибок. Если мы не установим значение параметра @Тitlе, оператор SELECT использует его значение по умолчанию. В этом случае лучше задать значение по умолчанию для входного параметра равным NULL, а в обработке кода возврата подсказать пользователю, как правильно исполнять процедуру. Для этого нужно модифицировать процедуру следующим образом:

ALTER PROCEDURE dbo.TitleForPerson

@Title varchar(80) = NULL, -- Это первый входной параметр.

@PersonId int OUTPUT, -- Это первый выходной параметр.

@TitleText varchar(60) OUTPUT -- Это второй выходной параметр.

AS

IF @Title IS NULL

RETURN(2)

ELSE IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0

RETURN(1)

ELSE

SELECT @PersonId = PersonId, @TitleText=title

FROM titles WHERE title LIKE @Title

GO

Параметру @Title присвоено значение по умолчанию NULL. Сразу после ключевого слова AS добавлен условный оператор IF. Проверка условия должна выполняться перед исполнением оператора SELECT COUNT, поскольку в противном случае при проверке условия получится TRUE, даже если значение параметра равно NULL.

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

DECLARE @p_PersonId int, @t_TitleText varchar(80), @r_Code int

-- Запустить процедуру и установить @r_Code равной результату процедуры.

EXECUTE @r_Code = TitleForPerson

@PersonId = @p_PersonId OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "Docent%"

-- Определить значение @r_Code и исполнить программу.

IF @r_Code = 0

SELECT "Title" = @t_TitleText,

"PersonId" = @p_PersonId,

"Return Code" = @r_Code

ELSE IF @r_Code = 1

PRINT 'No matching titles in the database. Return code-' +

CONVERT(varchar(1), @r_Code)

ELSE IF @r_Code = 2 --test for a return code of 2.

PRINT 'You must add a value for @Title for this procedure to function properly. Return code=' + CONVERT(varchar(1),@r_Code)

GO

Другая важная категория ошибок, которые необходимо обнаружить, — ошибки базы данных. Функция @@ERROR позволяет выявлять несколько тысяч различных ошибок базы данных.

Она фиксирует номера ошибок по мере исполнения процедуры. Каждый номер, который возвращает функция, можно использовать для вывода сообщения, раскрывающего пользователю причины ошибки. В операторах INSERT и UPDATE часто допускают пользовательские ошибки, которые приводят к возникновению ошибок базы данных. Как правило, в этих операторах пользователь пытается ввести информацию, нарушающую целостность данных (например, недействительный идентификационный номер). Номера и описания ошибок хранятся в таблице master.dbo.sysmessages. Эти сведения можно запросить посредством оператора SELECT:

SELECT error, description from master.dbo.sysmessages

Если процедура исполнена успешно, значение функции @@ERROR равно 0. При возникновении ошибки функция @@ЕRROR возвращает номер ошибки, отличный от 0. Значение функции @@ERROR изменяется при выполнении каждого оператора Transact-SQL, так что это значение следует хранить в переменной или генерировать код возврата по завершении каждого оператора Transact-SQL. Следующий сценарий демонстрирует генерацию кода возврата при возникновении ошибки в базе данных:

ALTER PROCEDURE dbo.TitleForPerson

@Title varchar(80) = NULL, -- Это первый входной параметр.

@PersonId int OUTPUT, -- Это первый выходной параметр.

@TitleText varchar(60) OUTPUT -- Это второй выходной параметр.

AS

IF @Title IS NULL

RETURN(2)

ELSE IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0

RETURN(1)

ELSE

SELECT @PersonId = PersonId, @TitleText=title

FROM titles WHERE title LIKE @Title

-- Обработка ошибок БД SQL Server.

IF @@ERROR <> 0

RETURN(3)

GO

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

Следующий фрагмент кода исполняет процедуру и выводит стандартное сообщение об ошибке (если она есть):

DECLARE @p_PersonId int, @t_TitleText varchar(80), @r_Code int

-- Запустить процедуру и установить @r_Code равной результату процедуры.

EXECUTE @r_Code = TitleForPerson

@PersonId = @p_PersonId OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "Docent%"

-- Определить значение @r_Code и исполнить программу.

IF @r_Code = 0

SELECT "Title" = @t_TitleText,

"PersonId" = @p_PersonId,

"Return Code" = @r_Code

ELSE IF @r_Code = 1

PRINT 'No matching titles in the database. Return code-' +

CONVERT(varchar(1), @r_Code)

ELSE IF @r_Code = 2 --test for a return code of 2.

PRINT 'You must add a value for @Title for this procedure to function properly. Return code=' + CONVERT(varchar(1),@r_Code)

ELSE IF @r_Code = 3

PRINT 'There was a database error.

GO

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

Вложенные процедуры

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

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

Библиографический список:

1. Браст, Э.Дж. Разработка приложений на основе Microsoft SQL Server 2005. // Э.Дж. Браст, С.Форте. – М.: «Русская редакция», 2007. – 880 с.

2. Вийера, Р. Программирование баз данных Microsoft SQL Server 2005. Базовый курс. // Р.Вийера. – М. Издательский дом «Вильямс», 2007. – 832 с.

3. Дейт, К. Введение в системы баз данных, 8-е издание. // К.Дейт. – М. Издательский дом «Вильямс», 2005. – 1328 с.

4. Коннолли, Т. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. 3-е издание. // Т.Коннолли, К.Бегг. – М. Издательский дом «Вильямс», 2003. – 1440 с.

5. Ролланд, Ф.Д. Основные концепции баз данных. // Ф.Д.Ролланд. – М.: Издательский дом «Вильямс», 2002. – 256 с.

6. Хендерсон, К. Профессиональное руководство по Transact-SQL. // К.Хендерсон. – СПб.: Питер, 2005. 560 с.

7. http://citforum.ru/database/sql.shtml

8. http://www.corpsite.ru/Resources/Technology/DB/

9. http://www.intuit.ru/department/database/mssqlserv2005/

10. http://www.intuit.ru/department/database/rdbdev/

11. http://www.microsoft.com/sql

12. http://www.sql.ru

Дата добавления: 2014-01-11