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

Для удаления одной пользовательской хранимой процедуры, нескольким едур одновременно или набора сгруппированных процедур используют оператор DROP PROCEDURE или его сокращенную версию DROP PROC. Вот как удалить из базы даиньгх Pubs две процедуры с именами redured 1 и Procedure02:

USE pubs

DROP PROCEDURE procedureOI, procf)dure02

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

дуры]. При удалении системной хранимой процедуры, определенной пользователем (с префиксом i i. оператор DROP PROC!DURE ищет ее в текущей базе данных. Если он здесь не найдет определенную пользователем системную хранимую процедуру с таким именем,

то обратится к базе данных Master.

Чтобы удалить группу хранимых процедур, необходимо задать имя процедуры. С помощью оператора DROP PROCEDURE нельзя удалить часть группы. Например, не удас-

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

GroupedProc;2. Если нужно удалить часть группы, придется удалить всю группу и создать нужные процедуры заново.

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

Чтобы создавать, исполнять, изменять и удалять пользовательские хранимые процедуры в Query Analyzer, достаточно Б1елкнуть их правой кнопкой и выбрать одну из опций Script Object Тс... Чтобы сгенерировать сценарий в Query Analyzer, щелкните Script Object То New Window.

Упражнение 2. Работа с хранимыми процедурами

В этом упражнении вы создадите хранимую процедуру в базе данных и

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

► Создание хранимой процедуры в базшых Northwind

Откройте Query Analyzer и подключитесь к локальному серверу.

2. Закройте окно Object Browser, если оно открыто.

3. Раскройте окно Query, чтобы оно заняло все рабочее пространство в Query Analyzer.

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

В Enterprise Manager можно пользовательскую хранимую процедуру,

щелкнув ее правой кнопкой и выбрав команду Rename.

При переименовании хранимых троцедут! и других объектов (например, таблиц) следует соблюдать осторожность. процедуры могут быть вложенными, и при вызове объекта исходной хранимой процедуре не удастся найти его, если он персименоиан.

USE northwind GO

CREATE PROCEDURE dbo.CustOrderHistRep ©CustonierlD char(5)

SELECT ContactName, ContactTitle

FROM Customers WHERE CustomerlD = ©CustomerlD

SELECT ProductName, Total=SUM{Quantity)

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

WHERE

C.CustomerlD = ©CustotDerlD AND G.CustomerlD = 0.CustomerlD

AND, c- n,-P-jD = OD.OrderlD AND 0!}. ProductlD = P. ProductlO

GROUP 8Y ProductName

При исполнении первого пакета база Northwind становится Далее создается

процедура CuslOrder Hist Rep и определяется единственный входной параметр - @Cus-tomerlD. Входной параметр может располагаться в одной строке с оператором PROCEDURE, но здесь для ясности он перенесен в отдельную строку. Аналогичный подход использован далее при разбиении по строкам кода операторов SELECT. Обратите внимание, что для параметра задан тип данных char(5). Если выполнить запрос к таблице (northwind] stomersl, то видно, что длина всех идентификаторов покупателей равна пяти символам. Строка, в которой находится единственное ключевое слово AS, является разделительной линией между созданием процедуры в таблице SysObjects и текстом процедуры, сохраняемым в таблице SysComments. 5. Просмотрите операторы SELECT, которые расположены ниже ключевого слова AS, но не тратьте на это слишком много времени.

При исполнении запроса в ответ на ввод идентификатора покупателя первый оператор

SELECT выводит имя контактного лица и заголовок контактной информации. Второй

оператор SELECT выводит названия и количество (SUM) каждого товара, кото-

рый приобрел покупатель нрующий набор возвращает данные, сгруппированные по названию товара. Можно заметить, чт олько соединений реализовано в конструкции WHERE, а не FROM. Во время модификации процедуры мы переместим выражения JOIN в конструкцию FROM.

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

Просмотр хранимые едур в Query Analyzer

1. Откройте Object Browser в Query Analyzer и раскройте базу данных Northwind.

2. Раскройте узел Stored Procedures.

Появляются все хранимые процедуры, созданные в базе даннтх Northwind,

3. Раскройте хранимую процедуру dbo.CustOrderHistRep.

Обратите внимание, что эта хранимая процедура принаежит dbo и что ii dbo.Cust-OrderHistRep появляются два узла: Parameters и Dependencies.

4. Раскройте узел Parameters.

Обратите внимание на наличие у этой хранимой процедуры двух параметров: созданного вами и встроенного параметра для хранения кода возврата TURN VALUE.

5. Раскройте узел Dependencies.

Обратите внимание, что хранимая ьедура зависит от четырех объектов (таблиц Orders, Products, Order Details и Customers из базы данных Northwind). От самой хранимой процедуры не зависит ни один объект.

6. Переключитесь на панель Editor окна Query.

7. Введите в пустой строке в нижней части панели Editor и исполните дающую команду: sp depLnc J uuo Lui I iiibL I ер

Хранимая едура spdepends проверяет процедуру rHistRep, чтобы определить ее зависимости.

Обратите внимание на наличие поле Nam ющихся элементов. Однако все записи щимися элементами отличаются значениями других полей,

8. Не закрывайте Query Analyzer, он потребуется для выполнения следующего задания.

Исполнение хранимой проиедуры

1. В нижней части панели Editor введите в пустой строке и исполните следующую команду:

EXEC Lnothwindj. Lcoo]. [cusLoi cer instrepj (aCiRfnriPrTD = -hecr

Для запуска хранимой процедуры CustOrderHistRep использована еиная версия ключевого слова EXECUTE - EXEC. Обратите внимание на использование полного имени. Это не обязательно, но в данном случае такой прием позволяет запустить процедуру, не делая активной базу данных Northwind.

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

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

2. В примере для запуска хранимой процедуры было задано ключевое слово EXEC. Обязательно ли его использовать?

3. Найдите в Object Browser чранимую процедуру dbo.CustOrderHistRep и щелкните ее

правой кнопкой.

4. Выберите из контекстного меню Script Object to New Windows As и щелкните Execute. Query Analyzer загружает редактор Query Window новую страницу, появляется оператор EXECUTE для процедуры dbo.CustOrderHistRep. Обратите внимание на объявление двух переменных: @RC tomerl D. Первая переменная используется для хранения любых кодов возврата, частью а вторая хранит

ние входного параметра ггитШ. Обратите внимание, что в операторе EXEC переменная @RC соответствует хранимой процедуре. В занятии 3 мы более подробно

рассмотрим объявление переменных при исполнении хранимых процедур и установку параметра, хранящего код хранимой процедуры.

5. На панели Editor щелкните мышью в конце оператора EXEC, чтобы после слова @CustomerlD появился wiumi курсор. Допишите к концу строки слсдуюи.(ее: = thecr. Теперь оператор EXEC должен принять следующий вид:

EXERC rderHistRep] iCustomerlD = thecr

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

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