Глава 11

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

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

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

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

В этой главе приведен общий обзор способов создания основных хранимых процедур и пользовательских функций (User-Defined Function - UDF), в которых применяются базовые объекты СУБД SQL Server, после чего изложение переходит к более сжатому описанию сложных процедурных конструкций и отладки. После того как будет окончено изучение основных объектов SQL Server, в главе 14 мы перейдем к рассмотрению элементарных сведений об инфраструктуре .NET и ее новых возможностях.

Создание хранимых процедур - основные синтаксические определения

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

CREATE PROCEDURE IPROC <sproc name>

[<parameter name> [schema.]<data type> [VARYING] [= <default value>] [OUT [PUT]] [,

<parameter name> [scliema. ] <data type> [VARYING] [= <default value>] [OUT[PUT]] [,

]] [WITH

RECOMPILE I ENCRYPTION [EXECUTE AS { CALLERSELFOWNER< user name>}] [FOR REPLICATION] AS

<code> I EXTERNAL NAME <assembly name>.<assembly class>

Вполне очевидно, что в этом операторе CREATE, как и во всех других операторах создания объектов базы данных, сохраняется базовая структура CREATE <Object Туре> <Object Name>. Единственное отличие от операторов создания других объектов заключается в том, что в операторе создания хранимой процедуры допускается использование двух вариантов ключевого слова, PROCEDURE и PROC. Оба эти варианта являются вполне допустимыми, но, как всегда, автор рекомендует, выбрав один из этих вариантов, неизменно его придерживаться. (Сам автор предпочитает использовать ключевое слово PROC, поскольку это позволяет немного уменьшить объем вводимого кода, и опыт показывает, что так поступают большинство программистов.) Имя хранимой процедуры должно соответствовать правилам именования, приведенным в главе 1.

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

Наконец, в определении процедуры за ключевым словом AS следует исходный код.

Пример несложной хранимой процедуры

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

Автор надеется, что для читателя уже не составляет труда подготовить запрос, возвращающий все содержимое таблицы (подсказка- SELECT * FROM. . .). USE AdventureWorlcs

CREATE PROC spEmployee AS

SELECT * FROM HumanResources.Employee

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

Если выполняется оператор DROP PROC, а затем процедура вновь создается с использованием оператора CREATE PROC, то достигается почти такой же результат, как и при использовании оператора ALTER PROC, не считая такого важного различия - после применения операторов DROP PROC и CREATE PROC должны быть вновь установлены все разрешения, касающиеся того, кто может и не может использовать хранимую процедуру. Кроме того, СУБД SQL Server теряет контроль над информацией о зависимостях, касающейся любых процедур, представлений, триггеров и функций, которые зависели от модифицируемой хранимой процедуры, прежде чем она была удалена.

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

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

DROP PROCI PROCEDURE <sproc name>

После вьшолнения оператора удаления хранимая процедура становится недоступной.

Теперь, после создания хранимой процедуры вызовем ее на выполнение для ознакомления с полученными результатами: EXEC spEmployee

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

Модификация хранимых процедур с помощью оператора alter

Операторы ALTER действуют применительно к хранимым процедурам почти аналогично тому, как действуют операторы ALTER модификации представлений.

Главное отличие, о котором следует помнить, заключается в том, что после редактирования кода T-SQL в представлении происходит полная замена существующего представления. При использовании операторов ALTER PROC и CREATE PROC обнаруживаются в основном описанные ниже различия.

Оператор ALTER PROC рассчитан на то, что будет обнаружена существующая хранимая процедура, тогда как в операторе CREATE PROC это не предусмотрено.

Оператор ALTER PROC сохраняет все разрешения, которые были определены для хранимой процедуры. Этот оператор сохраняет тот же идентификатор объекта, который был присвоен ранее хранимой процедуре, входящей в число других системных объектов, и обеспечивает сохранение зависимостей. С другой стороны, если в процедуре А предусмотрен вызов процедуры В, но происходит удаление и повторное создание процедуры В с помош?>ю оператора CREATE PROC, то зависимость между этими двумя процедурами больше не обнаруживается. Если же используется оператор ALTER PROC, то такие зависимости сохраняются.

После вьшолнения оператора ALTER PROC сохраняется вся информация о зависимостях, относящаяся к другим объектам, в которых могут быть предусмотрены вызовы модифицируемой хранимой процедуры.