FROM titles WHERE title LIKE ©Title

@Title - это входной параметр, a выходные - ©YtdSales и ©TitleText. Обратите внимание, что для всех трех определен тип данных. 3 определении выходных параметров присутствует обязательное ключевое слово OUTPUT. Все параметры после определения применяются в операторе SELECT. Сначала значения выходных параметров устанавливаются равными именам столбцов в запросе. Во время исполнения запроса в выходных параметрах будут значения из этих двух столбцов. В WHERE оператора SEEECT присутствует входной параметр STJtle. При исполнении этой процедуры необходимо задать входного параметра, иначе запрос не выполнится.

Следующий оператор исполняет

-- Объявить переменные ающие выходные енм? процедуры. DECLARE ffiy YtdSales int, @t TitleTfiXi- varchar(80) EXECUTE SalesForTitle

-- Значения выходных параметров будут присвоены объявленным переменным. mdSales = @y YtdSales OUTPUT, gTitleText = @t TltleText OUTPUT,

gTitlf rlic% - Задать значение входного параметра,

Вывести ения переменных, гопученные i

-- в результате исполнения процедуры.

Select Title = j . i.: lolexi, Ji.irt.iH of Sales = @y YtdSales 30

Объявляются две переменные: SOyYtdSales и @t TitleText. Они получают значения, которые хранятся в выходные №ieTpax. Обратите внимание, что типы данных, объявленные для этих переменных, совпадают с типами данных выходных параметров. Эти переменные могут иметь те же имена, что и выходные параметры, поскольку в хранимых процедурах локальны для пакета, в котором они объявлены. Для ясности здесь использованы имена переменных, отличные от имен выходных параметров. Пр пере.менной ее значение не совпадает со значением выходного параметра. Значение etn ы\ становятся равными значениям выходных пара- метров лишь после исполнения оператора EXECUTE. Обратите внимание, что после присвоения переменным значение leTpoB задано ключевое слово OUTPUT. Без него оператор расположенный конце пакета, не сможет вывести значения переменных. Обратите также внимание на значение входного параметра которое равно Это значение передается конструкцию оператора SELECT хранимой процедуры. Поскольку в конструкции WHERE указано ключевое слово LIKE, можно использовать знаки подстановки, Полученный в результате запрос настроен на поиск заглавий, в которых содержится слово Garlic .

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

ного параметра или объявленных здесь выходных переменных:

DECLARE @y YtdSales int, @t TitleTe}<t varchar(30) EXECUTE SalesForTitle

%Garlic% . - устанавливает значение входного параметра. @yYtdSales OUTPUT, - получает первый выходной параметр .®it TltleText OUTPUT -- получает еторой выходной параметр - Вывести значения переменных ченые в результате

Занятие 3 Программирование хранимых процедур 257

-- исполнения процедуры.

Select Title = 0t TitleText, Number of Sales = @y YtdSales GO

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

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

Оператор RETURN и обработка ошибок

Часто основные усилия при программировании качественных хранимых процедур и любых программ) затрачиваются на реализацию обработки ошибок. SQL Server пре.дос-тавляет функции и операторы для обработки ошибок, возникающих во время исполнения процедуры. Ошибки можно разделить на две основные категории: связанные с компьютерами, например, когда недоступен сервер баз данных, и пользовательские. Для обработки ошибок, возникающих во время исполнения процедур, используются коды возврата и функция @ ©ERROR.

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

лей. Оператор RETURN используется главным образом li-i обработки ошибок, поскольку при исполнении этого оператора выполняется безусловный выход из процедуры.

Рассмотрим процедуру SalesForTitle торую мы создали и исполнили в предыдущем разделе. Если в базе данных нет строк, соответствующих значению входного параметра (©Title), процедура возвращает результирующий набор, показанный в следующей таблице.

Title Number of Sales

NULL NULL

Однако для пользователя более наглядно сообщение об отсутствии записей, ствующих запросу. В следующем примере показано, как модифицировать хранимую процедуру SalesEorTitle, чтобы задействовать оператор RETURN (и создать более информа-сообщение для пользователя):

ALTER PROCEDURE iJuo.baiesForTitle PTltle varchar(BO). @YtdSales int OUTPUT. @TitleText varchar(80) OUTPUT AS

- Проверить, есть ли в базе данных заданная книга. Если нет, -- выйти из процедуры и установить код возврата ным 1, ГЕ (SELECT C0UNT(O FROM titles WHERE title LIKE ©Title) = 0 RETURN(I)

ELSE

SELECT ШЗаХез = ytd sales, @TitleText=title

FROM titles WHERE title LIKE SiTitle

Оператор IF, стоящий посл евого слова AS, определяет wian ли входной параметр при исполнении процедуры и соответствуют ли ему какие-либо записи в базе данных. Если функция COUN ,1шает 0, то код возврата устанавливается равным 1: RETURN(I). Если функция COUM возвращает значение, отличное от О, то оператор SEEECT запращивает из таблицы 1 iit- годовой объем продаж и сведения об издании. В этом случае код возврата равен 0.

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

Чтобы задействовать код iiara. придется перепрограммировать операторы для исполнения процедуры. В следующем примере входному параметру @Title присваивается значение ОагИс%

-- Добавить переменную tode дпя хранения результирующего кода.

:)ECLAi(tsv (iubai.js int, eit TitliText vrji uiujol;fflr Code int

-- Запустить процедуру и установить @r Code равной результату процедуры.

EXECUTE 0r Code = SalesForTitle

sVttiSales = @v YtdSales OUTPUT,

iSTitleText = i§)t TitleText OUTPUT

;sTitle = Garlic%-

-- Определить значение Code и исполнить программу.

IF ®г .Сог1е = О

SELECT Title = @t TltleText,

Number of Sales - @y YtdSales, Return Code = @r Code ELSE IF @r Code = 1

PRINT Nd matching titles :( the catabase. Return code= +

CONVERT(varchar (1),e4 Codu}

К оператору DECLARE была добавлена kis-, переменная ode. Далее эта переменная хранит значение, возвращаемое оператором RETURN. Она определена как цело-поскольку код возврата передается как целое число. В строке с оператором EXECUTE переменной @rCode присваивается значение кода возврата. Обратите внимание, что переменная содержит значение, которое возвращает хранимая ра. Теперь вместо для входного параметра @Title задано значение Другими оператор SELECT будет искать в таблице Titles издания, названия которых начинаются со слова Garlic . Ниже и комментария размещено условие. Сначала выполняется проверка ветви IF. Если процедура находит запись, код возврата равен О и выполняется оператор SELECT. Если процедура не находит ни одной соответствующей записи, переменная ©rCode рввна 1 и исполняется оператор PRINT. Поскольку в базе данных нет книги, заглавие которой начинается со слова результат процедуры определяет оператор PRINT: