Разработка хранимых процедур

Хранимая процедура (stored procedure) – это именованный набор команд Transact-SQL, хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базы данных. Без хранимых процедур пользователю пришлось бы вводить весь набор команд всякий раз, когда он хочет выполнить какое-либо действие. Таким образом, если вы часто выполняете однотипные операции, реализуемые довольно большим количеством команд, вы рано или поздно захотите облегчить себе жизнь, реализовав используемый набор команд в виде хранимой процедуры. Это позволит не писать вновь и вновь десятки команд, когда необходимо выполнить ту или иную задачу, а указать лишь имя хранимой процедуры, которую нужно выполнить. В результате сервер в соответствии с логикой работы процедуры будет запускать те или иные включенные в нее команды. При этом можно использовать циклы, ветвление, транзакции и т.д. Чтобы читатель более явно понимал суть хранимых процедур SQL Server, можно провести аналогию с процедурами современных языков программирования, например Delphi или FoxPro.

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

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

- системные хранимые процедуры (system stored procedures). Это хранимые процедуры, поставляемые в составе SQL Server и предназначенные для выполнения различных административных действий – создание учетных записей, получение информации об объектах базы данных, управление свойствами сервера и баз данных, управление подсистемой репликации и автоматизации и множество других задач. Практически все действия по администрированию SQL Server выполняются с помощью хранимых процедур этого типа. Более того, инструмент Enterprise Manager в конечном счете также использует эти процедуры. Системные хранимые процедуры имеют префикс sp_, который произошел от английского system procedure. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами. То есть, в конечном счете, работа системных хранимых процедур сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры хранятся в системной базе данных master и могут быть вызваны в контексте любой другой базы данных.

- пользовательские хранимые процедуры (user-defined stored procedures). Помимо системных хранимых процедур, разработанных программистами Microsoft, при работе со SQL Server пользователи также могут создавать свои собственные процедуры, реализующие те или иные действия. Как уже было сказано, хранимая процедура является полноценным объектом базы данных. Следствием этого является то, что каждая хранимая процедура хранится в конкретной базе данных. Соответственно, для выполнения этой процедуры необходимо сослаться на соответствующую базу данных. Непосредственный вызов хранимой процедуры возможен только в том случае, если этот вызов осуществляется в контексте базы данных, в которой находится процедура.

- временные хранимые процедуры (temporary stored procedures). До сих пор мы рассматривали хранимые процедуры как нечто постоянное и доступное всем пользователям. Однако в SQL Server существуют и так называемые временные хранимые процедуры. Такие процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. В свою очередь, временные процедуры делятся на локальные и глобальные.

- локальные временные хранимые процедуры (local temporary stored procedures). Локальные временные процедуры могут быть вызваны только из того соединения, в котором они были созданы. При создании хранимой процедуры такого типа необходимо дать ей имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа хранятся в базе данных tempdb и автоматически удаляются при отключении пользователя, перезапуске или остановке сервера.

- глобальные временные хранимые процедуры (global temporary stored procedures). Процедуры этого типа доступны для любых соединений сервера, на котором была создана соответствующая глобальная временная хранимая процедура. Для определения такой процедуры достаточно при создании дать ей имя, начинающееся с символов ##. Хранятся эти процедуры в базе данных tempdb и удаляются при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

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

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

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

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

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

- включить создаваемую процедуру в группу. Для удобства управления процедурами вы можете группировать логически однотипные хранимые процедуры. Для этого всем процедурам группы при создании дают одинаковые имена, но разные идентификационные номера. Таким образом, получается несколько хранимых процедур, которые обозначаются следующим образом: <имя_процедуры>;<идентификационный__номер>.

Ссылка на группу осуществляется по имени ее процедур. Например, в группе процедур ##proced отдельные хранимые процедуры могут иметь имена ##proced;1, ##proced;2 и т.д. Достоинство такого подхода заключается в том, что вы можете одновременно управлять сразу всей группой процедур. Например, можно удалить сразу все процедуры, входящие в состав группы.

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