Использование сохраненных процедур

Дата: 01.01.2007

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

В этой главе обсуждаются следующие темы:

Дается общий обзор сохраненных процедур;
Объясняется, как создавать и выполнять сохраненные процедуры;
Объясняется, как возвращать информацию из сохраненных процедур;
Приводятся правила, связанные с сохраненными процедурами;
Объясняется, как удалять и переименовывать сохраненные процедуры;
Описывается, как использовать системные сохраненные процедуры;
Объясняется, как получать информацию о сохраненных процедурах.

Что такое сохраненные процедуры

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

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

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

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

С SQL Сервером предоставляется большое число сохраненных процедур в качестве удобных инструментов для пользователя. Эти сохраненные процедуры называются системными сохраненными процедурами.

Сохраненные процедуры создаются с помощью команды create procedure (создать процедуру). Для выполнения сохраненной процедуры, как системной, так и определенной пользователем, используется команда execute (выполнить). Можно также просто указать название сохраненной процедуры, если оно является первым словом в операторе или пакете.

Примеры создания и использования сохраненных процедур

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

create procedure название_процедуры
   as SQL_операторы

Сохраненные процедуры являются объектами базы данных и их название должны соответствовать правилам для идентификаторов.

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

create procedure namelist
as select name from sysusers

Для выполнения сохраненной процедуры нужно использовать ключевое слово execute, за которым следует название сохраненной процедуры, или просто указать название процедуры, если оно одно сообщается SQL Серверу, или является первым оператором в пакете. Процедура namelist может быть выполнена любым из следующих способов:

namelist
execute namelist
exec namelist

Для выполнения сохраненной процедуры на удаленном SQL Сервере необходимо указать название сервера. Полный синтаксис вызова удаленной процедуры выглядит так:

execute
название_сервера.[название_базы_данных].[владелец].название_процедуры

В следующих примерах процедура namelist из базы данных pubs2 выполняется на удаленном сервере GATEWAY:

execute gateway.pubs2..namelist
gateway.pubs2.dbo.namelist
exec gateway...namelist

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

Название базы данных является необязательным параметром только, если сохраненная процедура находится в базе данных заданной по умолчанию (default database). Имя владельца процедуры является необязательным только, если владельцем процедуры является владелец базы данных ("dbo") или сам пользователь, вызывающий эту процедуру. Безусловно необходимо иметь разрешение (permission) для выполнения процедуры.

Процедура может содержать несколько операторов.

create procedure showall as
  select count(*) from sysusers
  select count(*) from sysobjects
  select count(*) from syscolumns

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

showall
------------
          5
(Выбрана 1 строка)

------------
         88
(Выбрана 1 строка)

------------
        349
(1 row affected, return status = 0)

Если команда create procedure выполнилась успешно, то название процедуры заносится в таблицу sysobjects, а ее текст в syscomments.

Текст процедуры может быть выведен с помощью системной процедуры sp_helptext:

sp_helptext showall

# Lines of Text
---------------
             1
(1 row affected)

text
---------------------------------------
create procedure showall as
  select count(*) from sysusers
  select count(*) from sysobjects
  select count(*) from syscolumns
(1 row affected, return status = 0)

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

Сохраненные процедуры могут служить в качестве механизма обеспечения секретности, поскольку пользователь может получить разрешение на выполнение сохраненной процедуры, даже в том случае если у него или у нее нет разрешения на обращение к таблицам или вьюверам, к которым обращается процедура, или нет разрешения на выполнение определенных команд. Более детально этот вопрос обсуждается Руководстве пользователя по средствам ограничения доступа SQL Сервера (Security Features User's Guide).

Сохраненные процедуры и производительность

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

sp_recompile titles

Более детально о команде sp_recompile можно узнать из Справочного руководства SQL Сервера.

Создание и выполнение сохраненных процедур

Полный синтаксис команды create procedure выглядит так:

create procedure [владелец.]название_процедуры[;номер] [
  [(] @название_параметра тип_данных [= default] [output]
  [,  @название_параметра тип_данных [= default]
[output]]...[]] [with recompile]
as sql_операторы

Создавать процедуру можно только в текущей базе данных.

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

Далее приводится полный синтаксис оператора execute:

[execute] [@return_status =]
    [[[сервер.]база_данных.]владелец.]название_процедуры[;номер]
  [[@название_параметра = ] значение |
  [@название_параметра = ] @переменная [output]
  [,[@название_параметра = ] значение |
  [@название_параметра = ] @переменная [output]...]]
[with recompile]

Замечание:
Вызов удаленной процедуры не является частью транзакции. Если вызов удаленной процедуры происходит после слов begin transaction (начать транзакцию), а затем встречается команда rollback transaction (откатить транзакцию), то любые изменения, которые произвела удаленная процедура над удаленными данными, не восстанавливаются. Создатель сохраненной процедуры должен быть уверен, что все условия, которые могут вызвать откат со стороны триггера, должны проверяться перед вызовом удаленной процедуры, которая может изменить удаленные данные.

Параметры

Параметр - это аргумент сохраненной процедуры. Один или несколько параметров могут быть объявлены в операторе создания процедуры. Значение каждого параметра, объявленного в операторе create procedure, должно указываться пользователем в момент вызова процедуры.

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

Далее приведена сохраненная процедура, которая используется в базе данных pubs2. По заданным именам и фамилиям писателей процедура выдает названия книг этих авторов и название каждого издательства, где они были опубликованы.

create proc au_info @lastname varchar(40),
                     @firstname varchar(20) as
select au_lname, au_fname, title, pub_name
from authors, titles, publishers, titleauthor
where au_fname = @firstname
  and au_lname = @lastname
  and authors.au_id = titleauthor.au_id
  and titles.title_id = titleauthor.title_id
  and titles.pub_id = publishers.pub_id

Теперь выполним процедуру au_info:

au_info Ringer, Anne

au_lname au_fname title                    pub_name
-------- ------------------------------    ----------
Ringer   Anne     The Gourmet Microwave    Binnet & Hardley
Ringer   Anne     Is Anger the Enemy?      New Age Books

(2 rows affected, return status = 0)

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

create proc showind @table varchar(30) as
  select table_name = sysobjects.name,
         index_name = sysindexes.name, index_id = indid
  from sysindexes, sysobjects
  where sysobjects.name = @table
    and sysobjects.id = sysindexes.id

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

execute showind titles
exec showind titles
execute showind @table = titles
execute GATEWAY.pubs2.dbo.showind titles
showind titles

Последняя синтаксическая форма, не содержащая ключевого слова exec или execute, допустима только, если этот оператор является единственным в строке или первым оператором в пакете.

Ниже приведены результаты выполнения процедуры showind в базе данных pubs2, когда параметром является название таблицы titles:

table_name  index_name  index_id
----------  ----------  --------
titles      titleidind     1
titles      titleind       2
(2 rows affected, return status = 0)

Замечание:
Если параметры задаются в виде "@параметр=значение", то их можно задавать в любом порядке. В противном случае, они должны быть заданы в том же порядке, в каком они указаны в операторе create procedure. Если хотя бы один параметр был задан в виде "@параметр=значение ", то все остальные параметры должны быть заданы в таком же виде.

Значения по умолчанию для параметров

В операторе create procedure для параметра можно указать значение, принимаемое по умолчанию. Это значение, которое может быть любой константой, используется в качестве аргумента процедуры, если для этого параметра не было указано никакого значения.

Далее приведена процедура, которая выводит имена всех авторов, которые написали книги, опубликованные заданным издательством. Если название издательства не указано, то процедура выводит имена авторов, которые изданы в Algodata Infosystems.

create proc pub_info
  @pubname varchar(40) = "Algodata Infosystems" as
  select au_lname, au_fname, pub_name
  from authors a, publishers p, titles t, titleauthor ta
  where @pubname = p.pub_name
    and a.au_id = ta.au_id
    and t.title_id = ta.title_id
    and t.pub_id = p.pub_id

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

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

exec pub_info

au_lname     au_fname      pub_name
-----------  ------------  --------------------
Green        Marjorie      Algodata Infosystems
Bennet       Abraham       Algodata Infosystems
O'Leary      Michael       Algodata Infosystems
MacFeather   Stearns       Algodata Infosystems
Straight     Dick          Algodata Infosystems
Carson       Cheryl        Algodata Infosystems
Dull         Ann           Algodata Infosystems
Hunter       Sheryl        Algodata Infosystems
Locksley     Chastity      Algodata Infosystems
(9 rows affected, return status = 0)

В следующей процедуре showind2 параметру @table присваивается по умолчанию значение "titles":

create proc showind2 @table varchar(30) = titles
as
  select table_name = sysobjects.name,
         index_name = sysindexes.name, index_id = indid
  from sysindexes, sysobjects
  where sysobjects.name = @table
    and sysobjects.id = sysindexes.id

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

showind2 authors

table_name   index_name  index_id
-----------  ----------  --------
authors      auidind            1
authors      aunmind            2
(2 rows affected, return status = 0)

Если пользователь не указывает никакого параметра для этой процедуры, то SQL Сервер по умолчанию будет использовать таблицу titles:

showind2

table_name   index_name   index_id
-----------  -----------  --------
titles       titleidind          1
titles       titleind            2
(2 rows affected, return status =0)

Если в процедуре предусмотрен параметр, но он не указан, и в операторе create procedure для этого параметра не указано никакого значения по умолчанию, то SQL Сервер выводит сообщение об ошибке и перечисляет параметры, которые должны быть заданы.

NULL как значения по умолчанию для параметра

Значение по умолчанию может быть неопределенным (NULL). В этом случае, если пользователь не указывает параметр, то SQL Сервер не выдает сообщения об ошибке и выполняет сохраненную процедуру.

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

create procedure showind3 @table varchar(30) = null
as
  if @table is null
    print "Please give a table name"
  else
    select table_name = sysobjects.name,
           index_name = sysindexes.name, index_id = indid
    from sysindexes, sysobjects
    where sysobjects.name = @table
      and sysobjects.id = sysindexes.id

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

Другие примеры установки неопределенного значения в качестве значения по умолчанию можно увидеть в тексте системных процедур с помощью процедуры sp_helptext.

Символы замены в значениях по умолчанию параметров

Если в процедуре используется параметр с ключевым словом like, то значение по умолчанию может содержать символы замены (%, _, [] и [^].

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

create procedure showind4 @table varchar(30)="sys%"
as
  select table_name = sysobjects.name,
         index_name = sysindexes.name, index_id = indid
  from sysindexes, sysobjects
  where sysobjects.name like @table
    and sysobjects.id = sysindexes.id

Использование нескольких параметров

Ниже приведен один из вариантов сохраненной процедуры au_info, которая содержит значения по умолчанию с символами замены для обоих параметров:

create proc au_info2 @lastname varchar(30) = "D%",
            @firstname varchar(18) = "%" as
select au_lname, au_fname, title, pub_name
from authors, titles, publishers, titleauthor
where au_fname like @firstname
  and au_lname like @lastname
  and authors.au_id = titleauthor.au_id
  and titles.title_id = titleauthor.title_id
  and titles.pub_id = publishers.pub_id

Если процедура au_info2 выполняется без параметров, то выдаются фамилии всех писателей, которые начинаются с буквы D:

au_info2

au_lname  au_fname  title                      pub_name
--------  --------  -------------------------  -------------
Dull      Ann       Secrets of Silicon Valley  Algodata Infosystems
DeFrance Michel     The Gourmet Microwave      Binnet & Hardley
(2 rows affected)

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

Замечание:
Если параметры задаются в виде "@параметр=значение", то они могут располагаться в любом порядке. Можно также пропустить любой параметр, если для него указано значение по умолчанию. Если хотя бы один параметр был задан в виде "@параметр=значение", то остальные параметры должны быть заданы в таком же виде.

Чтобы проиллюстрировать вызов процедуры с явно заданным одним параметром, когда для двух параметров указаны значения по умолчанию, рассмотрим следующий пример, в котором запрашиваются названия всех книг, написанных автором по фамилии «Ringer», вместе с издательствами их опубликовавшими:

au_info2 Ringer

au_lname   au_fname   title                   Pub_name
--------   --------   ---------------------   ------------
Ringer     Anne       The Gourmet Microwave   Binnet & Hardley
Ringer     Anne       Is Anger the Enemy?     New Age Books
Ringer     Albert     Is Anger the Enemy?     New Age Books
Ringer     Albert     Life Without Fear       New Age Books
(4 rows affected)

Группы процедур

Необязательная точки с запятой вместе с целым числом после названия процедуры в операторах create procedure и execute позволяют группировать процедуры с одинаковым названиями так, что они могут быть удалены одновременно одним оператором drop procedure (удалить процедуру).

Процедуры, которые используются в одном и том же приложении, часто группируются таким образом. Например, можно создать последовательность процедур orders;1, orders;2, и т.д. Следующий оператор будет удалять всю эту группу:

drop proc orders

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

drop proc orders;2

Конструкция recompile в операторе create procedure

В операторе создания процедуры create procedure необязательная конструкция with recompile (с перекомпилированием) расположена точно перед SQL-операторами, составляющими тело процедуры. Она сообщает SQL Серверу о том, что не нужно сохранять план выполнения процедуры, поскольку при каждом запуске этой процедуры будет создаваться новый план ее выполнения.

Если конструкция with recompile не указана, то SQL Сервер сохраняет созданный план выполнения процедуры. Обычно этот план является вполне удовлетворительным.

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

Конструкцию with recompile в операторе создания процедуры следует использовать, когда пользователю может потребоваться новый план выполнения процедуры. Дополнительная информация об этом дается также в Справочном руководстве SQL Сервера.

Конструкция recompile в операторе execute

В операторе execute необязательная конструкция with recompile располагается сразу после параметров. Она сообщает SQL Серверу, что нужно создавать новый план выполнения процедуры. Новый план используется при дальнейших запусках процедуры.

Конструкцию with recompile следует указывать, если данные сильно изменились или среди значений параметров процедуры появились нетипичные, т.е. когда у пользователя есть уверенность, что текущий план выполнения процедуры не оптимален.

Замечание: Если в определении процедуры используется команда select *, то процедура не распознает новые столбцы, добавленные в таблицу, даже если в операторе execute используется опция with recompile. Такая процедура должна быть удалена и создана заново.

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

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

Временные таблицы в сохраненных процедурах

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

Создавать временные таблицы;
Вставлять, обновлять и удалять данные;
Выполнять запросы над временными таблицами;
Вызывать другие процедуры, которые обращаются к временной таблице.

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

Создайте необходимую временную таблицу с помощью операторов create table или select into. Например:
```
create table #tempstores
(stor_id char(4), amount money)
```
Создайте процедуру, которая имеет доступ к временной таблице (но не ту, что создает таблицу).
```
create procedure inv_amounts
as
   select stor_id, "Total Due" =sum(amount)
   from #tempstores
   group by stor_id
```
Удалите временную таблицу:
```
drop table # tempstores
```

Создайте процедуру, которая создает временную таблицу и вызывает процедуру, указанную в п. 2:

create procedure inv_proc
as
  create table #tempstores
  (stor_id char(4), amount money)

  insert #tempstores
    select stor_id, sum(qty*(100-discount)/100* rice)
    from salesdetail, titles
    where salesdetail.title_id = titles.title_id
    group by stor_id, salesdetail.title_id

exec inv_amounts

Можно создавать временные таблицы без префикса #, используя оператор create table tempdb..tablename.. в самой сохраненной процедуре. Эти таблицы не удаляются после завершения выполнения процедуры, поэтому на них могут ссылаться независимые процедуры. Для создания таких таблиц можно использовать способы, описанные в пп.1-4.

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

Процедуры можно выполнять на SQL Сервере, отличном от локального SQL Сервера. Если оба сервера имеют совместимые конфигурации, то можно выполнять любую процедуру на удаленном сервере, просто записывая название сервера как часть идентификатора. Например, для выполнения процедуры remoteproc на удаленном сервере GATEWAY, нужно выполнить следующую команду:

exec gateway.remotedb.dbo.remoteproc

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

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

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

Возврат информации из сохраненных процедур

Сохраненные процедуры сообщают свой «статус возврата», который указывает, была ли выполнена процедура полностью, или нет, а также причины неудачи. Это значение может храниться в переменной, которая передается процедуре при ее вызове, и использоваться в последующих операторах Transact-SQL. SQL Сервер резервирует значения в диапазоне от -1 до -99 для кодов возврата ошибочных ситуаций, которые могут возникнуть при выполнении процедуры; а значения, находящиеся вне этого диапазона, пользователи могут использовать для определения своих статусов (кодов) возврата.

Другой способ возврата информации из сохраненных процедур состоит в возврате значений через выходные параметры. Параметры, определенные как выходные, в операторе create procedure (создать процедуру) или execute (выполнить) используются для возврата значений в место вызова процедуры. Затем с помощью условных операторов можно проверить возвращаемое значение.

Код возврата и выходные параметры позволяют разделить сохраненные процедуры на модули. Группа SQL операторов, которые используются несколькими сохраненными процедурами, могут быть объединены в одну процедуру, которая сообщает свой статус выполнения или значения своих параметров вызывающей процедуре. Например, многие системные процедуры, поставляемые с SQL Сервером, обращаются к процедуре, которая проверяет являются ли указанные параметры правильными идентификаторами.

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

Коды возврата

Сохраненные процедуры могут возвращать целое число, которое называется кодом возврата (return status). Это число показывает, была ли процедура выполнена полностью или указывает на причины неудачного выполнения. SQL Сервер имеет набор заранее определенных кодов возврата. Пользователю может определить свои собственные коды возврата. Ниже приведен пример пакета, в котором оператор execute возвращает код состояния:

declare @status int
execute  @status = pub_info
select @status

Статус выполнения (код возврата) процедуры pub_info сохраняется в переменной @status. В этом примере код возврата просто выводится с помощью оператора select; в последующих примерах код возврата будет анализироваться с помощью условных конструкций.

Зарезервированные значения кодов возврата

SQL Сервер резервирует код 0 для указания успешного выполнения процедуры и значения в диапазоне от -1 до -99 для указания различных причин неудачи. В следующей таблице показаны коды возврата от 0 до -14 и даны их описания:

Таблица 14-1: Зарезервированные значения обратного статуса

+---------------------------+-----------------------------------+
| Код                       | Значение                          |
+---------------------------+-----------------------------------+
| 0                         | Процедура выполнена без ошибок    |
+---------------------------+-----------------------------------+
| -1                        | Отсутствует объект                |
+---------------------------+-----------------------------------+
| -2                        | Ошибка в типе данных              |
+---------------------------+-----------------------------------+
| -3                        | Прекращение выполнения процедуры  |
|                           | для выхода из тупика              |
+---------------------------+-----------------------------------+
| -4                        | Нарушение прав доступа            |
+---------------------------+-----------------------------------+
| -5                        | Синтаксическая ошибка             |
+---------------------------+-----------------------------------+
| -6                        | Ошибка пользователя               |
+---------------------------+-----------------------------------+
| -7                        | Недостаток ресурсов, например,    |
|                           | памяти                            |
+---------------------------+-----------------------------------+
| -8                        | Не фатальная внутренняя проблема  |
+---------------------------+-----------------------------------+
| -9                        | Достижение системного предела     |
+---------------------------+-----------------------------------+
| -10                       | Фатальная внутренняя проблема     |
+---------------------------+-----------------------------------+
| -11                       | Фатальная внутренняя проблема     |
+---------------------------+-----------------------------------+
| -12                       | Испорчена таблица или индекс      |
+---------------------------+-----------------------------------+
| -13                       | Испорчена база данных             |
+---------------------------+-----------------------------------+
| -14                       | Аппаратная ошибка                 |
+---------------------------+-----------------------------------+

Кода от -15 до -99 зарезервированы для дальнейшего использования SQL Сервером.

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

Коды возврата пользователя

Пользователь может определить свои собственные коды возврата из сохраненных процедур, добавляя параметр в операторе return (возврат). Коды от 0 до -99 зарезервированы SQL Сервером; остальные значения можно использовать для определения своих кодов. В следующем примере возвращается 1, если книга имеет правильный код контракта, и 2 во всех остальных случаях:

create proc checkcontract @titleid tid
as
  if (select contract from titles
      where title_id = @titleid) = 1
    return 1
  else
    return 2

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

create proc get_au_stat @titleid tid
as
declare @retvalue int
execute @retvalue = checkcontract @titleid 
if (@retvalue = 1)
  print "Contract is valid"
else
  print "There is not a valid contract"

Ниже показан результат выполнения процедуры get_au_stat, аргументом которой является идентификатор книги с правильным номером контракта:

get_au_stat «MC2222»

Contract is valid

Проверка прав доступа в процедурах

Если сохраненная процедура выполняет задачу системного администрирования, то пользователь должен иметь права на ее использование. (Информацию о правах см. в Руководстве пользователя по средствам ограничения доступа SQL Сервера). Функция proc_role позволяет проверить права (роль) пользователя во время выполнения процедуры. Она возвращает 1, если пользователь имеет соответствующие права. Различают три степени прав доступа: sa_role, sso_role, и oper_role.

Ниже приведен пример использования функции proc_role в процедуре test_proc, требующей от вызывающего ее пользователя прав доступа системного администратора:

create proc test_proc
as
  if (proc_role("sa_role") = 0)
  begin
    print "You don't have the right role"
    return -1
  end
  else
    print "You have SA role"
    return 0

Возвращаемые параметры

Если в операторах create procedure и execute указывается опция output (выход) в названии параметра, то процедура возвращает значение этого параметра вызывающему объекту. Этим объектом может быть SQL пакет или другая сохраненная процедура, которые используют возвращаемые значения в своей дальнейшей работе. Если возвращаемые параметры используются в операторе execute, который является частью пакета, то значения возвращаемых параметров вместе с заголовком выводятся на экран перед выполнением последующих операторов пакета.

Нижеприведенная процедура выполняет умножение двух целых чисел, которые передаются ей в качестве двух первых аргументов, а третий аргумент @result определяется с опцией output:

create procedure mathtutor @mult1 int, @mult2 int,
@result int output
as
  select @result = @mult1 * @mult2

Чтобы использовать процедуру mathtutor для целей обучения, можно объявить переменную @result и включить ее в оператор execute. Добавление ключевого слова output в операторе execute позволяет увидеть значения возвращаемого параметра.

declare @result int
exec mathtutor 5, 6, @result output

(return status = 0)

Return parameters:
-----------
        30

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

mathtutor 5,6,32

(return status=0)

Значение параметра, определенного с опцией output, должно передаваться через переменную, а не через константу. В следующем примере переменная @guess используется для передачи в процедуру mathtutor значения третьего параметра. При этом SQL Сервер выводит значение возвращаемого параметра:

declare @guess int
select @guess = 32
exec mathtutor 5, 6, @result = @guess output

(1 row affected)
(return status = 0)

Return parameters:

@result
----------- 
       30

Значения возвращаемых параметров выводятся всегда, независимо от того, изменились эти значения, или нет. Заметим, что:

В предыдущем примере выходной параметр @result должен передаваться в виде "@параметр=@переменная". Если бы он не был последним передаваемым параметром, то все следующие за ним параметры также должны передаваться в таком же виде;
Переменную @result не нужно объявлять в вызывающем пакете, поскольку это название параметра процедуры mathtutor.
Несмотря на то, что измененное значение параметра @result возвращается через переменную, указанную в операторе execute, в данном случае через переменную @guess, оно выводится под своим названием, т.е. @result.

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

declare @guess int
declare @store int
select @guess = 32
select @store = @guess
execute mathtutor 5, 6, @result = @guess output
select Your_answer = @store, Right_answer = @guess
if @guess = @store
  print "Right-o"
else
  print "Wrong, wrong, wrong!"

(1 row affected)
(1 row affected)
(return status = 0)

Return parameters:

@result
----------- 
        30 

Your_answer     Right_answer 
-----------     ------------ 
         32               30 
(1 row affected)

Wrong, wrong, wrong!

Ниже приведена сохраненная процедура, которая проверяет, влияет ли объем продажи новой книги на изменение гонорара ее автора. Параметр @pc определяется как выходной (output) параметр:

create proc roy_check @title tid, @newsales int,
       @pc int output
as
  declare @newtotal int
  select @newtotal = (select titles.total_sales + @newsales
                      from titles
                      where title_id = @title)
  select @pc = royalty from  roysched
  where @newtotal >= roysched.lorange and
        @newtotal <  roysched.hirange
    and roysched.title_id = @title

Следующий SQL пакет вызывает процедуру roy_check после присваивания значения переменной percent. Значения возвращаемых параметров выводятся на экран перед выполнением следующего оператора пакета:

declare @percent int
select @percent = 10
execute roy_check "BU1032", 1050, @pc = @percent output
select Percent = @percent
go

(1 row affected)
(return status = 0)

Return parameters:
@pc
----------- 
        12 

Percent
----------- 
        12
(1 row affected)

Следующая сохраненная процедура вызывает процедуру roy_check и использует возвращаемое в переменной percent значение в условном операторе:

create proc newsales @title tid, @newsales int
as
  declare @percent int
  declare @stor_pc int
  select @percent = (select royalty from roysched, titles
                     where roysched.title_id = @title
                       and total_sales >= roysched.lorange
                       and total_sales < roysched.hirange
                       and roysched.title_id=titles.title_id)
  select @stor_pc = @percent
  execute roy_check @title, @newsales, @pc = @percent
  output
  if @stor_pc != @percent
  begin
    print "Royalty is changed"
    select Percent = @percent
  end
  else
    print "Royalty is the same"

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

execute newsales "BU1032", 1050

Royalty is changed

Percent
----------- 
        12
(1 row affected, return status = 0)

В двух предыдущих примерах, где вызывается процедура roy_check, @pc является названием параметра, который передается процедуре roy_check, а @percent является выходной переменной. Когда процедура newsales вызывает процедуру roy_check, то значение переменной @percent может изменяться в зависимости от значения других передаваемых параметров. Если нужно сравнить возвращаемое значение percent с первоначальным значением параметра @pc, то следует сохранить начальное значение в другой переменной. В предыдущем примере это значение сохраняется в переменной stor_proc.

Передача значений параметров

Значения параметров должны передаваться в следующем виде:

@параметр=@переменная

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

Замечание:
Если сохраненная процедура требует нескольких параметров, то либо выходной параметр должен быть указан последним в операторе execute, либо все следующие после него параметры должны быть указаны в виде "@параметр=значение".

Ключевое слово output

Ключевое слово output (выходной) можно сокращать до out, также как и execute можно сокращать до exec.

Сохраненные процедуры могут возвращать несколько значений, каждое из которых должно определяться как выходная (output) переменная в сохраненной процедуре и вызывающем операторе, например:

exec myproc @a = @myvara out, @b = @myvarb out

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

Правила, связанные с сохраненными процедурами

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

В пакет можно поместить только один оператор create procedure (создать процедуру) вместе с телом этой процедуры и в этом случае в пакете не должно быть других SQL операторов;
Тело процедуры может содержать любое количество SQL операторов любого типа, за исключением оператора use и следующих операторов создания объектов:
```
create view
create default
create rule
create trigger
create procedure
```
Другие объекты базы данных можно создавать внутри процедуры. К объекту базы данных можно обратиться внутри этой же процедуры, если он был создан до того как к нему обратились. Поэтому оператор создания объекта базы данных нужно располагать в начале процедуры;
Внутри сохраненной процедуры нельзя создать объект, затем удалить его, а затем снова создать новый объект с таким же названием;
SQL Сервер создает объекты, определенные в процедуре, во время выполнения процедуры, а не во время ее компилирования;
При выполнении процедуры, которая вызывает другую процедуру, вызываемая процедура может обращаться к объектам, созданным первой процедурой;
Внутри процедуры разрешается обращаться к временным таблицам;
Если внутри процедуры была создана временная таблица, то она существует только во время выполнения этой процедуры, и исчезает после выхода из процедуры;
Максимальное число параметров сохраненной процедуры равно 255.
Максимальное число локальных и глобальных переменных процедуры ограничивается только объемом доступной памяти.

Расширение названий объектов внутри процедур

Если многие пользователи обращаются к сохраненной процедуре, то названия объектов, которые используются в некоторых командах внутри процедуры, должны быть расширены именем владельца объекта. Такими командами являются: alter table, create table, drop table, truncate table, create index, drop index, update statistics, dbcc. Названия объектов, которые используются в других операторах, например select или insert, не требуют расширения, поскольку их названия уточняются во время компиляции процедуры.

Например, пользователь "Мэри" (mary), которая является владельцем таблицы marytab, должна расширить название своей таблицы, когда она используется с одной из перечисленных выше команд в том случае, если "Мэри" хочет дать возможность другим пользователям исполнять эту процедуру с указанной таблицей:

create procedure p1
as
  create index marytab_ind
  on mary.marytab(col1)

Дело в том, что названия объектов уточняются во время выполнения процедуры. Если название таблицы marytab не расширить, то при выполнении процедуры пользователем c именем "Джон" (john), SQL Сервер будет искать таблицу marytab, владельцем которой является Джон. В предыдущем примере было показано правильное использование этого правила. SQL Серверу было сообщено, что нужно искать таблицу marytab, владельцем которой является Мэри.

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

Сохраненные процедуры удаляются с помощью команды drop procedure (удалить процедуру). Синтаксис этой команды выглядит следующим образом:

drop procedure [владелец.]название_процедуры
            [, [владелец.]название_процедуры] ...

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

Группа процедур, т.е. несколько процедур с одинаковыми названиями, но с разными числовыми суффиксами, удаляются одним оператором drop procedure. Процедуры, объединенные в группу, не могут быть удалены независимо друг от друга.

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

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

sp_rename название_объекта, новое_название

Например, чтобы процедуру с названием showall переименовать на countall нужно выполнить следующую команду:

sp_rename showall, countall

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

Переименование объектов внутри процедур

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

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

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

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

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

Информацию о предоставлении и отзыве прав на сохраненные процедуры и другие объекты базы данных, можно посмотреть в Руководстве пользователя по средствам ограничения доступа SQL Сервера.

Системные процедуры

Системные процедуры могут быть полезными в следующих отношениях:

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

В большинстве случаев системные таблицы обновляются только через сохраненные процедуры. Системный администратор может разрешить непосредственное обновление системных таблиц путем изменения конфигурационной переменной и выдачи команды reconfigure with override (реконфигурация с перезаписью). См. по этому поводу Руководство системного администратора SQL Сервера (System Administration Guide).

Названия всех системных процедур начинаются с приставки «sp_». Они создаются согласно сценарию installmaster (мастер инсталляции) в базе данных sybsystemprocs во время инсталляции SQL Сервера.

Системную процедуру можно запускать из любой базы данных. Если системная процедура вызывается из базы данных, отличной от sybsystemprocs, то любые ссылки к системным таблицам отображаются в базу данных, из которой процедура была запущена. Например, если владелец базы данных pubs2 запускает процедуру sp_adduser (добавить пользователя), то новый пользователь будет добавлен в таблицу pubs2..sysuser.

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

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

Другие системные процедуры могут запускаться любым пользователем, которой имеет право на использование оператора execute, но это право должно быть предоставлено из базы данных sybsystemprocs. Отсюда вытекают два следствия:

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

Более подробную информацию на эту тему см. в Руководстве системного администратора SQL Сервера.