7, Закройте нову нину. созданную в окне Query, но не закрывайте Query Analyzer и исходную страницу, которая илась на панели Editor.

Выводится сообщение Query Analyzer с запросом на сохранение внесенных изменений.

8. Щелкните No.

На панели Editor появляется исходная страница.

процедуры

1. На панели Editor в окне Query введите следующий код:

usEMorthwinri

GO-CREATE PROCEDURE dbo. CustOrderHistRep SCustomerlD char(5) AS

SELECT ContactName, ContactTitle

FR Customers WHERE CustomerlD = ©CustotnerlD

SELECT ProductName. Total=SUM(Quantity)

FROM Products P. [Order Details] OD, Orders 0. Custorners С

WHERE

C. CustomerlD = giCustomerlD AND CCustoraerlD = 0. CustomerlD AND O.OrderlD = OD.OrderlD AND OD.ProductID = P.productlD GROUP BY ProductName

2. Замените ключевое слово CREATE на ALTER.

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

3. Необходимо отсортировать число заказов амию Для этого ниже оператора GROUP BY ProductName введите следующий оператор:

ORDER BY Total DESC

4. Также решено переместить соединения таблиц из конструкции WHERE в конструк-пик) FROM. В окончательном виде программа выглядит примерно так:

USE Northwind GO

ALTER PROCEDURE dbo.CustOrderHistRep

gJCustonierlO char(5)

SELECT ContaciHaine, ContactTitle

FROM Customers WHERE CustornedD = fiCustOerlD

SELECT ProductName. Total=SUM(Quantity)

FROM Products P INNER JOIN [Order Details] OD

ON P.ProductID = OD. ProdUctlC JOIN Orders 0

ON OD.OrderlD = O.OrderlD JOIN Customers С

ON 0 CustomerlD = C.CustomerlD WHERE C.CustomerlD = fiiCustOfflerlD GROUP BY ProductName

25 ранимые процедуры Глава 8

ORDER Total DESC . - . .

GO .

5. Исполните запрос.

6. Чтобы убедиться, что нужные изменения внесены, перейдите в нижнюю часть панели Editor нажмите ENTER, затем вцедите и исполните оператор:

sp.helptext custorderhistrep

На вкладке Grids панели Results появляется текст хранимой проиедуры.

7. Оставьте Query Analyzer открытым, он для следующего упражнения.

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

Перейдите в нижнюю часть панели Editor нажмите ENTER, затем введите и исполните следующий оператор:

DROP PROCEDURE Qbo.custorderhisLTep

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

2. Проверьте с помошью Object Browser в Query Analyzer или ЕпГефйве Manager, удалена ли хранимая процедура.

3. Закройте Query Analyzer.

Выводится запрос на сохранение сценария.

4. Если вы хотите сохраните а[)ий. нажмите Yes, в противном случае - No.

Резюме

Для создания процедур, сохраняемых в базе данных, используются ключевые слова CREATE PROCEDURE. Процедуры создают с помощью графических инструментов типа Enterprise Manager или утилит командной строки, например osql. После создания имя процедуры попадает в таблицу sysObjects, а ее текст - в SysComments. Во время создания процедуры можно сгруппировать, определить в них коды возврата, входные и выходные параметры, значения параметров по умолчанию. Такж ается зашифровать процедуру и определить, должен ли план исполнения и нужно ли исполнять процедуру на подписчике репликации. Основное назначение процедур - реализация той или иной задачи. Для исполнения процедур применяется ключевое слово EXECUTE. Процедуры можно запускать вручную или помечать для автоматического исполнения во время

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

в базе данных Master.

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

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

создавать вложенные хранимые процедуры и обработчики ошибок. Продолжительность занятия - около 45 минут.

Параметры и переменные

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

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

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

хранения этого значения. Как правило, выходные значения отображаются после завершения исполнения процедуры.

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

USE Pubs

CREATE PROCEDURE dbo.SalesForTitle

eiTitle varchar(80), - Это первый входной параметр. ©YtdSales int OUTPUT, - Это первый выходной параметр. @TlfleText varchar-(30) OUTPUT - Это второй выходной параметр.

Присвоить данные столбца выходным параметрам -- и проверить наличие книги, заданной входным параметром. SELECT Lit4dad.es = ytd aaies. @TitleText=title

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

Хранимые процедуры существенно расширяют возможности программирования на языке В занятии рассказано, как создавать, изменять, удалять и ис-

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

нимо дадуре. Это занятие посвящено использованию входных и выходных параметров. Вы познакомитесь с переменными, узнаете, как выполняется обработка ошибок хранимых процедурах с помощью кодов возврата и функции @@ERROR. В завершение мы расскажем о вложенных процедурах и использовании курсоров в хранимых