Пользовательские хранимые процедуры предназначены для реализации алгоритмов обработки данных. Такие хранимые процедуры создаются на уровне конкретной базы данных. В SQL Server 2000 имеется специальное право доступа – EXECUTE, с помощью которого можно разрешить пользователю или роли базы данных выполнять указанную процедуру.
Создание хранимой процедуры производится с помощью команды CREATE PROCEDURE, имеющей следующий синтаксис:
Рассмотрим назначение и использование параметров команды:
procedure_name – с помощью этого параметра указывается имя, которое будет иметь хранимая процедура. Имя должно следовать общим правилам именования объектов базы данных. Если в имени процедуры используются недопустимые символы, например пробелы, то имя должно быть заключено в ограничители (квадратные скобки или двойные кавычки). В SQL Server 2000 допускается создание глобальных и локальных временных хранимых процедур. Для этого перед именем процедуры ставится символ # (локальные) или символы ## (глобальные). Оба типа хранимых процедур автоматически уничтожаются при закрытии соединения, в котором они были созданы. Глобальные временные процедуры доступны для выполнения изо всех соединений, тогда как локальные могут вызываться только из того соединения, в котором они были созданы. Максимальная длина имени хранимой процедуры, включая символы # и ##, не должна превышать 128 символов. Дополнительно к имени самой процедуры разрешается указание имени владельца. Указание имени базы данных не разрешается, поэтому хранимую процедуру можно создать только в текущейбазе данных.
number – этот параметр предназначен для создания группы одноименных процедур. При этом дополнительно к имени хранимой процедуры можно указать через точку с запятой целое число, которое будет являться номером процедуры. Такой подход позволяет управлять несколькими процедурами как одним целым. Номера процедур не обязательно должны следовать друг за другом.
@parameter – c помощью этого аргумента для хранимой процедуры указываются входные параметры. При вызове хранимой процедуры пользователь должен будет указать значения для этих параметров, если не определено значение по умолчанию. С помощью параметров хранимой процедуры пользователи могут управлять логикой ее выполнения. В принципе, хранимая процедура может не иметь ни одного параметра. Но даже если параметры указаны, то они могут никак не влиять на ход выполнения процедуры. Параметры представляют собой не что иное, как обычные локальные переменные, создаваемые во время выполнения процедуры. Однако этим переменным автоматически присваиваются значения, указанные пользователями при вызове хранимой процедуры. В теле хранимой процедуры эти переменные используются точно так же, как и переменные, объявленные с помощью команды DECLARE. Как и все обычные переменные, имя параметра хранимой процедуры должно начинаться с символа @ и следовать стандартным правилам именования объектов.
data_type – этот параметр определяет тип данных, который будет иметь соответствующий параметр хранимой процедуры. Допускается использование не только встроенных в SQL Server 2000, но также и определенных пользователями типов данных (UDDT, user defined data type). Также разрешается использованиетипа данных cursor, позволяющего передавать в процедуру или из нее наборы строк, что представляет удобный механизм для обмена большими блоками данных, не умещающимися в одну переменную.
VARYING – это ключевое слово используется только для параметров хранимой процедуры, имеющих тип данных cursor. Использование VARYING необходимо, когда в хранимой процедуре происходит создание динамического курсора и полученные данные нужно возвратить из процедуры для дальнейшего использования.
Default – с помощью этого аргумента указывается значение по умолчанию, которое будет автоматически присваиваться соответствующему параметру хранимой процедуры, если при ее вызове пользователь не задал явного значения параметра. Применение значения по умолчанию для параметра хранимой процедуры очень напоминает указание значений по умолчанию для столбцов таблицы. В обоих случаях, если не приведено конкретное значение, то будет использоваться значение по умолчанию. При работе с хранимыми процедурами это позволяет упростить их вызов, освободив пользователя от необходимости ввода всех параметров, количество которых может достигать нескольких десятков. В качестве значения по умолчанию разрешается использовать только константы. Применение функций, вызовов хранимых процедур, имен столбцов, подзапросов и т. д. не разрешается. При необходимости значение параметров хранимой процедуры можно изменить в теле процедуры, проверив, что оно совпадает со значением по умолчанию, в качестве которого допускается также использовать значение null.
output – это ключевое слово приводится, когда необходимо возвратить измененное значение параметра хранимой процедуры. Например, если при вызове хранимой процедуры в качестве значения параметра используется переменная, то при указании ключевого слова output после завершения выполнения процедуры значение этой переменной будет относиться к значению соответствующего параметра хранимой процедуры. Не стоит понимать, что при указании output параметр процедуры используется исключительно как выходной. Он также может быть применен и в качестве входного параметра.
[,…n] – эта конструкция подразумевает, что для хранимой процедуры может быть указано множество параметров, общее количество которых может достигать 1024. Параметры должны быть указаны через запятую. Для каждого из них обязательно указывается тип данных. Дополнительно можно указать значение по умолчанию и параметры varying и output.
WITH – после этого ключевого слова приводятся дополнительные опции хранимой процедуры:
• recompile. Использование этого параметра предписывает не выполнять кэширование плана выполнения хранимой процедуры. То есть всякий раз при вызове процедуры будет происходить генерирование плана обработки запроса. В обычных ситуациях использование этого параметра не рекомендуется.
• encryption. При указании данного параметра происходит шифрование кода процедуры в таблице syscomments, где хранится исходный текст процедуры. Если разработанный алгоритм является коммерческой тайной, то использование шифрования позволит сохранить в тайне код процедуры.
• for replication. Этот параметр применяется только при выполнении репликации хранимых процедур. Указанный тип репликации позволяет пердавать от издателя к подписчикам не весь набор выполненных изменений, и лишь вызов хранимой процедуры со значениями всех входных параметров. Таким образом, можно резко снизить объем сетевого трафика. Хранимая процедура, созданная на подписчике с использованием for replication, не может быть вызвана пользователем. Ее запуск разрешен только подсистеме репликации.
AS – после этого ключевого слова начинается телохранимой процедуры, которое содержит набор команд Transact-SQL.
sql_statement [,...п] – этот параметр подразумевает указание собственно команд Transact-SQL, из которых и формируется тело хранимой процедуры. В теле процедуры могут присутствовать вызовы других процедур, команды создания, фиксирования и отката транзакций, команды создания объектов базы данных, команды управления данными и т. д.
Хранимая процедура с использованием параметров (БД pubs):
CREATE PROCEDURE get_list_authors
@state char(2) = 'UT' AS SELECT au_id, au_Lname, city, state, phone
FROM authors WHERE state=@state
Хранимая процедуру, использующая параметр OUTPUT для возвращения значений. Процедура должна выполнять поиск телефона автора по фамилии и имени или по идентификационному номеру автора. Кроме того, также можно будет выполнить обратную операцию – возвратить идентификационный номер автора, его имя и фамилию по номеру телефона.
Для выполнения поиска номера автора по его данным и получения данных об авторе по его номеру телефона нужно проверять значения входного параметра процедуры, соответствующего номеру телефона. Если он содержит значение NULL, то выполняется поиск номера телефона по имени с фамилией или по идентификационному номеру. В противном случае телефонный номер будет использоваться для получения данных об авторе.
