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

 

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). В этом случае, если пользователь не указывает параметр, то 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

 

 

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

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

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

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

 

 

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

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

 

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

 

 

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

 

 

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

 

·         Создавать временные таблицы;

·         Вставлять, обновлять и удалять данные;

·         Выполнять запросы над временными таблицами;

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

 

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

 

1.       Создайте необходимую временную таблицу с помощью операторов create table или select into. Например:

 

create table #tempstores

(stor_id char(4), amount money)

 

1.       Создайте процедуру, которая имеет доступ к временной таблице (но не ту, что создает таблицу).

 

create procedure inv_amounts

    as

    select stor_id, "Total Due" =sum(amount)

    from #tempstores

    group by stor_id

 

1.       Удалите временную таблицу:

 

drop table # tempstores

 

1.       Создайте процедуру, которая создает временную таблицу и вызывает процедуру, указанную в п. 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 (выходной) можно сокращать до 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 Сервера.

 

 

К этой категории относятся процедуры, выполняющие следующие действия:

 

·           Добавление, удаление и выдача регистраций (logins) SQL Сервера;

·           Добавление, удаление и выдача имен пользователей, групп и псевдонимов (alieses) базы данных;

·           Изменение паролей и базы данных, принимаемой по умолчанию;

·           Изменение владельца базы данных;

·           Добавление, удаление и выдача удаленных серверов, которые имеют доступ к данному SQL Серверу;

·           Добавление имен пользователей удаленных серверов, имеющих доступ к данному SQL Серверу.

 

К этой категории относятся следующие процедуры: sp_addlogin, sp_addalias, sp_addgroup, sp_adduser, sp_changedowner, sp_changegroup, sp_droplogin, sp_dropalias, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helpprotect, sp_helpuser, sp_password.

 

К этой категории относятся процедуры, выполняющие следующие действия:

 

·           Добавление, удаление и выдача удаленных серверов, которые имеют доступ к данному SQL Серверу;

·           Добавление имен пользователей удаленных серверов, имеющих доступ к данному SQL Серверу.

 

К этой категории относятся следующие процедуры: sp_addremotelogin, sp_addserver, sp_dropremotelogin, sp_dropserver, sp_helpremotelogin, sp_helpserver, sp_remoteoption, sp_serveroption.

 

 

К этой категории относятся процедуры, выполняющие следующие действия:

 

·       Связывание и развязывание правил и умолчаний;

·       Добавление, удаление и выдача главных, внешних и общих ключей;

·       Добавление, удаление и выдача типов данных пользователя;

·       Переименование объектов базы данных и типов данных пользователя;

·       Оптимизация сохраненных процедур и триггеров;

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

 

 К этой категории относятся следующие процедуры: sp_bindefault, sp_bindrule, sp_unbindefault,  sp_unbindrule, sp_foreignkey, sp_primarykey,  sp_commonkey, sp_dropkey, sp_depends,  sp_addtype, sp_droptype, sp_rename,  sp_spaceused, sp_help, sp_helpdb, sp_helpindex,  sp_helpjoins, sp_helpkey, sp_helptext,  sp_indsuspect, sp_recompile.

 

 

К этой категории относятся процедуры, выполняющие следующие действия:

 

·           Добавление сообщений пользователя в таблицу sysusermessages базы данных пользователя;

·           Удаление сообщений пользователя из таблицы sysusermessages;

·           Выбор сообщений из таблицы sysusermessages или таблицы sysmessages в базе данных master для вывода их с помощью операторов print и raiserror;

 

К этой категории относятся следующие процедуры: sp_addmessage, sp_dropmessage sp_getmessage.

 

 

К этой категории относятся процедуры, выполняющие следующие действия:

 

·           Добавление, удаление и выдача устройств для хранения базы данных и логирующих (dump) устройств;

·           Выдача запретов (locks), установленных опций базы данных и выполняющихся процессов пользователя;

·           Изменение и вывод конфигурационных переменных;

·           Наблюдение (monitoring) за активностью SQL Сервера.

 

К этой категории относятся следующие процедуры: sp_addumpdevice, sp_dropdevice, sp_helpdevice, sp_helpsort, sp_logdevice, sp_dboption, sp_diskdefault, sp_configure, sp_monitor, sp_lock, sp_who.

 

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

 

 

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

 

 

С помощью системной процедуры sp_help можно получить отчет о сохраненной процедуре. Например, пользователь может получить информацию о сохраненной процедуре byroyalty из базы данных pubs2 с помощью следующей команды:

 

sp_help byroyalty

 

Name       Owner   type                          Created_on

--------     ------         ----------------           -------------------

byroyalty                dbo         stored procedure    Feb  9 1987  3:56PM

 

Data_located_on_segment                  When_created          

---------------------------                             --------------------

 

Parameter_name    Type       Length                  Param_order

--------------              ------        ------                        -----------

@percentage            int            4           1

 

(return status = 0)

 

 

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

 

 

Чтобы увидеть текст (тело) сохраненной процедуры, нужно вызвать системную процедуру sp_helptext:

 

sp_helptext byroyalty

 

# Lines of Text

---------------

              1

 

(1 row affected)

 

text

---------------------------------------------------

create procedure byroyalty @percentage int

as

select au_id from titleauthor

where titleauthor.royaltyper = @percentage

 

(1 row affected, return status = 0)

 

Чтобы увидеть текст системной процедуры нужно вызвать процедуру sp_helptext из базы данных sybsystemprocs.

 

 

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

 

sp_depends byroyalty

 

Things the object references in the current database.

object                type        updated      selected

---------------- ----------- ---------   --------

dbo.titleauthor   user table     no            no

 

(return status = 0)

 

В следующем операторе процедура sp_depends используется для получения списка объектов, которые обращаются к таблице titleauthor:

 

sp_depends titleauthor

 

Things inside the current database that reference the object.

object                 type

--------------    ------------------

dbo.titleview      view

dbo.reptq2         stored procedure

dbo.byroyalty      stored procedure

 

(return status = 0)

 

Необходимо удалить процедуру, а затем вновь создать ее, если какой-либо из объектов внутри процедуры был переименован.

Системные процедуры были кратко рассмотрены в разделе «Системные процедуры» этой главы. Более полная информация о них дается в Справочном руководстве SQL Сервера.

 

[ << Назад ] [ Содержание ] [ Далее >> ]