ИСПОЛЬЗОВАНИЕ СОХРАНЕННЫХ ПРОЦЕДУР

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

 

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 является базой данных, заданной по умолчанию. Название базы данных является необязательным параметром только, если… Процедура может содержать несколько операторов.

Create procedure showall as

select count(*) from sysusers

select count(*) from sysobjects

select count(*) from syscolumns

 

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

Showall

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

Sp_helptext showall

---------------               1 (1 row affected)

Sp_recompile titles

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

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

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

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,…  

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

and a.au_id = ta.au_id and t.title_id = ta.title_id and t.pub_id = p.pub_id

Exec pub_info

--------------  ------------ -------------------- Green         Marjorie       Algodata   Infosystems                      Bennet        Abraham        Algodata   Infosystems                     

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

-----------  -----------      --------- titles           titleidind          1 titles           titleind            2

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

--------     -------      -------------------------        ------------- Dull          Ann          Secrets of Silicon Valley       Algodata… DeFrance Michel        The Gourmet Microwave      Binnet & Hardley

Au_info2 Ringer

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

Drop proc orders

 

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

 

Drop proc orders;2

Конструкция recompile в операторе create procedure   В операторе создания процедуры create procedure необязательная конструкция with recompile (с перекомпилированием)…

Create procedure inv_amounts

    as

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

    from #tempstores

    group by stor_id

 

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

 

drop table # tempstores

 

1. 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.. в самой сохраненной процедуре. Эти таблицы не…   Выполнение удаленных процедур

Exec gateway.remotedb.dbo.remoteproc

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

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   Проверка прав доступа в процедурах

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 (выход) в названии параметра, то процедура…

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

  Значение параметра, определенного с опцией output, должно передаваться через…  

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 Сервер создает объекты, определенные в процедуре, во время выполнения процедуры, а не во время ее…

Create procedure p1

As

Create index marytab_ind

On mary.marytab(col1)

Дело в том, что названия объектов уточняются во время выполнения процедуры. Если название таблицы marytab не расширить, то при выполнении процедуры…   Удаление сохраненных процедур

Sp_rename showall, countall

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

Sp_help byroyalty

--------     ------                ----------------          ------------------- byroyalty      dbo         stored procedure    Feb  9 1987  3:56PM Data_located_on_segment       When_created         

Sp_helptext byroyalty

---------------               1 (1 row affected)

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

object                 type --------------    ------------------ dbo.titleview      view