Главная » Microsoft SQL Server, Базы данных » Управление хранимыми процедурами

0

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

Инструкции CREATE, ALTER И DROP

Хранимые процедуры управляются посредством инструкций языка определения данных (DDL) CREATE, ALTER и DROP. Инструкция CREATE должна быть первой в пакете; терминатор пакета завершает создание хранимой процедуры. В следующем примере создается простая хранимая процедура, которая извлекает данные из таблицы ProductCategory базы данных OBXKites:

USE OBXKites; go

CREATE PROCEDURE CategoryList AS

SELECT ProductCategoryName, ProductCategoryDescription FROM dbo.ProductCategory;

RETURN;

На протяжении всей этой главы мы будем добавлять в хранимую процедуру CategoryList новые функции.

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

Хранимыми процедурами можно управлять из окна Object Explorer, однако я настоятельно рекомендую использовать для этого сценарии (файлы .sql), которые можно организовать в систему управления версиями.

Возвращение набора записей

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

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

ЕХЕС CategoryList;Ь

Результат выполнения процедуры (сокращенный):

ProductCategoryName ProductCategoryDescription

Accessory                   kite flying accessories

Book                           Outer Banks books

Clothing                      OBX t-shirts, hats, jackets

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

Компиляция хранимых процедур выполняется автоматически при первом их запуске, после чего скомпилированный код сохраняется в памяти (точнее, SQL Sewer создает план выполнения запросов и программного кода хранимой процедуры, после чего тот сохраняется в памяти).

Для отслеживания скомпилированных объектов SQL Server использует таблицу Master, dbo. SysCacheObjects. Чтобы просмотреть скомпилированный код, запустите на выполнение следующий запрос:

SELECT cast(C.sql as Char(35)) as StoredProcedure, cacheobjtype, usecounts as Count FROM Master.dbo.SysCacheObjects С JOIN Master.dbo.SysDatabases D ON C.dbid = C.dbid WHERE D.Name = DB_Name()

AND Obj Type = ‘Proc1 ORDER BY StoredProcedure;

Результат выполнения запроса (сокращенный):

StoredProcedure                                           cacheobjtype Count ObjType

CREATE PROCEDURE                            [dbo].[CleanBatchR       Compiled Plan       2              Proc

CREATE PROCEDURE                            [dbo].[CleanEventR       Compiled Plan        1              Proc

CREATE PROCEDURE                            [dbo].[CleanExpire        Compiled Plan        2              Proc

CREATE PROCEDURE                            [dbo].[CleanExpire        Compiled Plan        2              Proc

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

ЕХЕС sp_recompile CategoryList;

Результат выполнения будет следующим:

Object ‘CategoryList’ was successfully marked for recompilation.

Дополнительная Более подробно о планах выполнения запросов и методах их хранения в памя- информация ти речь пойдет в главе 50.

Шифрование хранимых процедур

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

Системная хранимая процедура sp_helptext может извлечь исходный текст хранимой процедуры из таблицы SysComments, например: sp_helptext CategoryList;

Результат будет следующим:

Text

CREATE PROCEDURE CategoryList AS

SELECT *

FROM dbo.Productcategory

Если хранимая процедура создается с параметром WITH ENCRYPTION, то ее текст в таблице SysComments невозможно прочитать. Как правило, сторонние производители программного обеспечения шифруют свои хранимые процедуры. Следующая инструкция ALTER сохранит процедуру CategoryList с параметром WITH ENCRYPTION, после чего мы попытаемся прочитать ее исходный текст:

ALTER PROCEDURE CategoryList WITH ENCRYPTION AS

SELECT *

FROM dbo.Productcategory;

sp_helptext CategoryList;

В результате получим следующее сообщение:

The text for object ‘CategoryList’ is encrypted.

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

Основной синтаксис языка SQL содержит всего десять инструкций: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE и DENY. В то же время компания Microsoft заложила в базу данных master массу хранимых процедур, позволяющих выполнить сотни задач. Чтобы эти процедуры стали доступны всем базам данных, нужно знать правила убавления их областью видимости. Любая хранимая процедура, начинающаяся с символов sp_ и находящаяся в базе данных master, может быть выполнена в любой базе данных. Если возникает конфликт между именами системной хранимой процедуры и другой, содержащейся в локальной базе данных, то выполняется последняя.

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

недоразумений. Я начинаю имена своих хранимых процедур с символа р.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии