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

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

общение syntax incorrect , и процедура не создается. Если текст процедуры проходит синтаксическую проверку, то процедура сохраняется, при этом ее имя и другая информации (например, автоматически генерируемый идентификационный номер) записывается в таблицу SysObjects, а текст процедуры - в таблицу SysComments базы данных. Оператор SELECT запрашивает идентификационный номер хранимой процедуры из таблицы SysObjects базы данных Pubs:

SELEC те]. [id] FROM [pubs], [dbo]. [SysObjects] WHERE [name] = byroyalty

Это запрос результат, показанный в таблице.

Name Id

Byroyalty 581577110

Занятие Операции с хранимыми процедурами

На этом занятии вы познакомитесь с методами управления хранимыми процедурами. Как

правило, их создают, модифицируют и удаляют с помощью Query Analyzer или Enterprise

Manager.

Процедуру разрешается создавать раньше объектов, на которые она ссылается. Эта возможность называется отложенным разрешением имен. Для создания процедур используют ключевые слова CREATE PROCEDURE.

Перед исполнением процедуры следует задать значения всех необходимых параметров. Хранимые процедуры можно исполнять вручную или автоматически (при запуске SQL Server). Для исполнения процедуры служит ключевое слово EXECUTE. Его можно опустить, если исполняемая процедура состоит из единственной строки или имя процедуры является первым словом пакета.

Часто приходится модифицировать созданные хранимые процедуры имер, чтобы добавить параметр или изменить ту или иную команду. Модификация процедуры вместо удаления и повторного создания с нуля позволяет сэкономить время, поскольку при модификации сохраняются многие свойства хранимых ироиедур (например, нрава доступа). Для модификации процедур используются ключевые слова ALTER PROCEDURE.

Для удаления процедур используют ключевое слово DROP. Процедуру можно удалить в Enterprise Manager или Query Analyzer, выбрав ее и нажав клавишу DELETE. He следует удалять процедуру до тех пор, пока все от процедуры объекты не будут удалены

или модифицированы (чтобы удалить зависимость).

Изучив материал этого занятия, вы сможете:

создавать и модифицировать хранимые процедуры в SQL Server;

исполнять хранимые процедуры;

удалять хранимые процедуры из SQL Server.

Продолжительность занятия - около 45 минут.

Оператор CREATE PROCEDURE

Оператор CREATE PROCEDURE (или его сокращенная версия CREATE PROC) предна-для создания хранимых процедур с Query Analyzer или утилит команд-

строки, например CREATE PROC позволяет выполнять задачи:

определять сгруппированные хранимые процедуры;

определять входные и выходные параметры, их типы данных и значения по умолчанию. Определение параметра всегда начинается со знака at (@), после которого следует имя параметра и описание его данных. В описании входных параметров должно присутствовать ключевое слово OUTPUT, которое позволяет отличить их от входных параметров;

выводить сведения об успешном или неудачном завершении задачи;

управлять кэшированием плантения процедуры;

шифровать содержимот1мо1 процедуры для обеспечения ее безопасности; контролировать режим исполнени.!fvpF,! для подписчика репликации;

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

Входные и выходные параметры, программирование действий а

также наборы при удачном и неудачном завершении рассматриваются в за-

нятии 3. Дополнительную информацию о синтаксисе CREATE PROC см. в справочнике по языку Transact-SQL в Books Online.

оператор SELECT с помощью из таблицы

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

Select [text] from rpubs],[dbo].[SysCom0nts] j.-

where [id] = 581577110 . . ;

Этот запрос возвращает исходный текст хранимой процедуры ByRoyalty, идентификаЦИОННЫЙ номер которой равен 5S1577110.

Примечание Два показанных оператора SELECT можно объединить средствами ключевого слова JOIN. Для простоты и ясности здесь они приведены по отдельности.

Для вывода исходного текста объекта (например, незашифрованной хранимой проце-лучше использовать системную хранимую процедуру поскольку при этом

текст, разбитый на строки. Вы уже использовали хранимую процедуру sp help-text в последнем задании упражнения

Методы создания хранимых процедур

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

Transact-SQL CREATE PROCEDURE, SQL-DMO (при использовании объекта Stored-Procedure), консоль Enterprise Manager и мастер Create Stored Procedure (в Enterprise Manager).

Примечание Многие обсужждаемые здесь команды доступны через API SQL-DMO. Однако

рассмотрение SQL-DMO выходит за рамки этого учебного курса. Подробно SQL-DMO описан в Books Online.

Передача контекста хранимой процедуре

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

Поэтому перед созданием процедуры следует выбирать базу данных с помощью

оператора USE ш данных. после которого указана пакетная команда GO. Для выбора текущей базы данных также используют раскрывающийся список Change Database в Query Analyzer.

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

процедуру ListAuthorNames, владельцем которой является dbo;

USE Pubs

CREATE PROCEDURE [dbo].[ListAuthorMames]

SELECT [au.fname], [au.lname] FROM [pubs] [dbo]. [authors]

Обратите внимание, что здесь используется полное имя процедуры, Полное нимой процедуры состоит из имени владельца процедуры (в этом случае dbo) и имени самой процедуры - ListAuthorNames. Если процедуру следует исполнять независимо от владельца таблицы или базы данных, владельцем процедуры должен быть dbo. При использовании оператора CREATE PROCEDURE имя базы данных не входит в полное имя

хранимой процедуры.

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

Чтобы создать временную хранимую процедуру, следует добавить к имени символ #. Этот знак сопбшае! SQL Server, что создать процедуру надо в базе TerapDB. Чтобы создать глобальную процедуру, необходимо добавить к ее имени символы ##, - SQL Server создаст

ее в базе данных TempDB. При создании временных хранимых SQL Server игно-

рирует текущую базу данных. Временные хранимые процедуры по определению существуют только в TempDB. Чтобы создать процедуру (за исключением локальной или глобальной) непосредственно в базе TempDB, следует вначале сделать TempDB текущей базой

данных. В показанной дале ~npH\fepe создаются три временн1х храним1х процедуры: глобальная и размещенная непосредственно в TempDB:

- Создать локальную временную хранимую процедуру. CREATE PROCEDURE fllocaltemp

SELECT * from [pubs].[dbo].[authors]

Создать глобальную временную хранимую процедуру. CREATEPROCEDUREttflglobaltemp AS

SELECT * from [pubs].[dbo],[authors]

- Создать временную хранимую процедуру

- непосредственно в tempdD. USE TEMPDB

CREATE PROCEDURE dlrectterap

SELECT * from [риЬз].[dbo].[authors]