Для решения типовых (часто повторяющихся) задач выборки или обновления данных, а также в значительной части для управления доступом к данным (как альтернатива механизму разрешения — запрета) и обеспечения целостности данных целесообразно использовать процедуры. Кроме того, другое преимущество, уже в части администрирования, состоит в том, что не надо специально определять пользователю права доступа к таблицам и представлениям, используемым в процедуре: достаточно определить только разрешение на выполнение процедуры.
Хранимая процедура (stored procedure) — это набор операторов T-SQL которые SQL SERVER компилирует в единый план выполнения. Этот план сохраняется в кэше процедур при первом выполнении хранимой процедуры, и затем план можно повторно использовать уже без ре-компиляции при каждом вызове. Хранимая процедура аналогична процедурам в языках программирования: она может принимать входные параметры, возвращать данные и коды завершения.
Хранимая процедура – это именованный набор команд Transact-SQL, хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базу данных. Она существует независимо от таблиц или каких-либо других объектов баз данных. Хранимая процедура может быть вызвана клиентской программой, другой хранимой процедурой или триггером. Возможно управлять правами доступа пользователей к хранимы процедурам. Прежде чем выполнить хранимую процедуру, сервер генерирует для неё так называемый план исполнения (execution plan), выполняет её оптимизацию и компиляцию. Выполняется кэширование плана исполнения процедуры, а также оптимизированного компилирования кода. Использование хранимых процедур реализует принцип модульного проектирования.
Применение хранимых процедур улучшает производительность, например, при использовании в хранимой процедуре условных операторов (таких как IF и WHILE), поскольку условие будет проверяться непосредственно на сервере и серверу не потребуется возвращать промежуточные результаты проверки условия программам-клиентам.
Хранимые процедуры также позволяют централизованно контролировать выполнение задачи, что гарантирует соблюдение бизнес-правил.
Существуют два способа взаимодействия приложения с SQL Server. Можно создать приложение, отправляющее клиентские операторы T-SQL на сервер, либо создать хранимые процедуры непосредственно на сервере. В первом случае операторы каждый раз ре-компилируются сервером. Второй способ активизирует хранимые процедуры, вызывая их из приложения одним оператором. При первом вызове хранимой процедуры она компилируется и создается план ее выполнения, который сохраняется в памяти. При последующих вызовах SQL Server будет использовать этот план и процедуру повторно не компилирует. Таким образом, когда для решения определенных задач требуется многократно выполнить одну и ту же последовательность операторов SQL, применение хранимой процедуры обеспечивает более высокую производительность.
Для управления обработкой в процедурах можно использовать локальные переменные, которые создаются с помощью оператора DECLARE. Переменная доступна с момента ее объявления и до выхода из процедуры. После выхода из процедуры на переменную ссылаться нельзя. Локальные переменные можно объявлять в пакете, в сценарии, внешней программе, а также в хранимой процедуре. В операторе DECLARE необходимо указать имя переменной и ее тип.
Типы хранимых процедур
Системные хранимые процедуры (system stored procedures) – это хранимые процедуры, поставляемые в составе SQL Server 2000. Предназначены для выполнения различных административных действий. Такие процедуры имеют префикс sp_ . Они хранятся в база данных master.
Пользовательские хранимые процедуры (user-defined stored procedures) – это процедуры созданные пользователями, реализующие те или иные действия (полноценный объект баз данных). Следствием этого является то, что каждая хранимая процедура хранится в конкретной базе данных.
Временные хранимые процедуры (temporary stored procedures) – эти процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Бывают локальные и глобальные.
Локальные временные хранимые процедуры(local temporary stored procedure) – могут быть вызваны только из того соединения, в котором они были созданы. При создании такой процедуры необходимо дать ей имя, начинающееся символом #. Они хранятся в база данных tempdb и автоматически удаляются при отключении пользователя.
Глобальные временные хранимые процедуры (global temporary stored procedure) – доступны для любых соединений сервера, на котором была создана соответствующая глобальная временная хранимая процедура. При создании такой процедуры необходимо дать ей имя, начинающееся символом ## и удаляются при перезапуске или остановке сервера, а также при закрытии соединения в контексте которого были созданы.
Способ создания хранимой процедуры
Для создания хранимой процедуры на языке Transact-SQL используется SQL-оператор CREATE PROCEDURE
Синтаксис данного оператора для MS SQL Server 2000
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Аргументы:
procedure_name – имя создаваемой процедуры. Используются префиксы sp_, #, ## (значение указано выше). Как видно из синтаксиса, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, в которой должна быть размещена процедура. Чтобы разместить хранимую процедуру в конкретной базе данных нужно создать её в контексте этой базы данных.
number – идентификационный номер хранимой процедуры, однозначно определяющий её в группе процедур. На пример: orderproc;1, orderproc;2. Процедура orderproc объединяет в себе две процедуры. При вызове DROP PROCEDURE orderproc, будут удалены все процедуры этой группы.
@parameter – имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Имена параметров должны начинаться с символа @. В одной хранимой процедуре может использоваться до 1024 параметров.
data_type – тип данных, который будет иметь соответствующий параметр хранимой процедуры. Можно использовать все типы данных, включая text, ntext и image и пользовательские типа данных. Однако тип данных cursor может использоваться только как выходной параметр (с указанием ключевого слова OUTPUT).
VARYING – ключевое слово, которое используется совместно с параметром OUTPUT, имеющим тип данных cursor. В качестве выходного параметра будет представлено результирующее множество.
default – значение, которое будет принимать соответствующий параметр по умолчанию. При вызове процедуры, явно можно будет не указывать значение соответствующего параметра. Будет использовано значение, созданное с помощью этого параметра.
OUTPUT – его наличие указывает, что параметр предназначается для возвращения данных из хранимой процедуры. Но этот параметр также может использоваться и для передачи значений в процедуру. Значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Нельзя использовать выражения и константы, допустимые для обычных параметров.
n – количество определённых параметров.
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE - Указывает, что SQL Server должен создавать план выполнения хранимой процедуры при каждом её вызове.
ENCRYPTION - Указывает, что SQL Server должен выполнить кодирование хранимой процедуры. Обратите внимание, сохранённые процедуры, созданные с выбором ENCRYPTION не могут рассматриваться с sp_helptext.
FOR REPLICATION – используется при репликации данных и включение создаваемой хранимой процедуры в качестве статьи в публикацию. Этот параметр не может быть использован совместно с параметром RECOMPILE.
AS – ключевое слово, свидетельствующее о начале тела процедуры.
sql_statement – любое число.
n – имена других параметров.
Дополнительно.
Процедуры базы данных. В различных СУБД они носят название хранимых (stored), присоединенных, разделяемых и т.д. Они используются для того, чтобы:
обеспечить централизованный контроль доступа к данным, осуществляемый администратором БД;
экономить вычислительные ресурсы системы (одна процедура может использоваться несколькими прикладными программами, что позволяет существенно сократить время написания программ за счет оформления их общих частей в виде процедур БД; процедура компилируется и помещается в БД, становясь доступной для многократных вызовов);
предоставить администратору мощные средства поддержки целостности БД;
значительно снизить трафик сети в системах с архитектурой "клиент-сервер".
Прикладная программа, вызывающая процедуру, передает серверу лишь ее имя и параметры (рис. 5.16).
