Создание хранимых процедур — до 20 мин

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

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

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

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

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

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

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

· с помощью средств Transact-SQL;

· с помощью Enterprise Manager (SQL Server 2000);

· с помощью SQL Server Management Studio (SQL Server 2005);

· с помощью мастера Create Stored Procedure Wizard.

Рассмотрим процесс создания хранимой процедуры средствами Transact-SQL с помощью команды CREATE PROCEDURE, имеющей следующий синтаксис:

CREATE PROC[EDURE] procedure_name [; number]

[ {@parameter datatype} [VARYING] [= default] [OUTPUT]][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FOR REPLICATION] AS sql_statement [,...n ]

Рассмотрим аргументы команды создания хранимой процедуры.

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

· number — идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур.

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

· data_type — тип, данных, который будет иметь соответствующий параметр хранимой процедуры. Для определения параметров можно использовать любые типы данных SQL Server, включая типы данных text, ntext и image; а также определенные пользователем типы данных. Однако заметим, что тип данных cursor может быть использован только как выходной параметр хранимых процедур, то есть с указанием ключевого слова OUTPUT.

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

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

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

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

· FOR REPLICATION — это ключевое слово используется при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Когда процедура, созданная с параметром FOR REPLICATION, копируется механизмами репликации на подписчиков, она не может быть выполнена стандартными средствами. Ее вызов осуществляется только подсистемой репликации. Параметр FOR REPLICATION не может указываться совместно с параметром RECOMPILE.

· ENCRYPTON — это ключевое слово предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Код, используемый при создании объектов базы данных, хранится в системной таблице syscomments, имеющейся в каждой базе данных. По умолчанию код не шифруется и поэтому может быть просмотрен с помощью обычного запроса SELECT.

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

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

При вызове одной хранимой процедуры из тела другой образуются так называемые вложенные процедуры (nested procedure). При работе с такими процедурами следует учитывать ограничение SQL Server 2000 на количество уровней вложенности — 32. Для определения текущего уровня вложенности можно воспользоваться переменной @@NESTLEVEL.

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

· указание только имени процедуры;

· использование команды EXECUTE.

В первом варианте вызов хранимой процедуры должен быть единственной командой передаваемого на исполнение пакета. Когда вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, использование этой команды требуется при вызове процедуры из тела другой процедуры или триггера. Синтаксис команды EXECUTE следующий:

[[EXEC[UTE]] {[@return_status = ]

{procedure_name [;number] | @procedure_name_var}

[[©parameter = ] {value | @variable [OUTPUT] | [DEFAULT]}]

[,...n]

[WITH RECOMPILE]

Помимо указания полного названия команды — EXECUTE — также допускается и укороченный вариант — EXEC. Обычно для вызова хранимой процедуры используется упрощенный синтаксис:

EXEC procedure_name

[[@parameter = ] { value | @variable [OUTPUT] | [DEFAULT]}]

[,...n]

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

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

Поиск по сайту: