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

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

В этом разделе мы рассмотрим три метода создания хранимой процедуры: использование оператора T-SQL CREATE PROCEDURE, использование Enterprise Manager и использование мастера Create Stored Procedure Wizard. Независимо от выбранного метода не забывайте тестировать каждую процедуру, выполняя при необходимости последующее редактирование, пока она не будет работать должным образом.

Использование оператора CREATE PROCEDURE

Оператор CREATE PROCEDURE имеет следующий синтаксис:

CREATE PROC[EDURE]   имя_процедуры
                             [ {@имя_параметра тип_данных} ] [= по_умолчанию][OUTPUT]
                             [,...,n]
AS оператор(ы)_t-sql

Создадим какую-либо простую хранимую процедуру. Эта процедура будет выбирать (и возвращать) три колонки данных для каждой строки таблицы Orders, в которой дата колонки ShippedDate больше даты колонки RequiredDate. Отметим, что хранимая процедура может быть создана только в текущей базе данных, к которой осуществляется доступ, поэтому сначала следует указать эту базу данных с помощью оператора USE. Прежде чем создать эту процедуру, мы выясним также, существует ли хранимая процедура с именем, которое мы хотим использовать. Если она существует, то мы удалим ее, и затем создадим новую процедуру с этим именем. Ниже показан набор T-SQL, используемый для создания этой процедуры:

USE Northwind
GO

IF EXISTS   (SELECT     name
                 FROM       sysobjects
                 WHERE      name = "LateShipments" AND
                              type = "P")
DROP PROCEDURE LateShipments
GO

CREATE PROCEDURE LateShipments
AS
SELECT   RequiredDate,
           ShippedDate,
           Shippers.CompanyName
FROM     Orders, Shippers
WHERE    ShippedDate    > RequiredDate AND
           Orders.ShipVia = Shippers.ShipperID
GO

Если запустить данный набор, то будет создана хранимая процедура. Для запуска хранимой процедуры просто обратитесь к ней по имени, как это показано ниже:

LateShipments
GO

Процедура LateShipments возвратит 37 строк данных.

Если оператор, вызывающий данную процедуру, входит в пакет операторов и не является первым оператором этого пакета, то вы должны использовать вместе с вызовом процедуры ключевое слово EXECUTE (сокращается до "EXEC"), как это показано в следующем примере:

SELECT getdate()
EXECUTE LateShipments
GO

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

Использование параметров

Теперь добавим к нашей хранимой процедуре входной параметр, чтобы мы могли передавать данные в эту процедуру. Чтобы задать входные параметры в хранимой процедуре, укажите список этих параметров с символом @ перед именем каждого параметра, т.е. @имя_параметра. Вы можете задать в хранимой процедуре до 1024 параметров. В нашем примере мы создадим параметр с именем @shipperName. При запуске хранимой процедуры мы укажем имя компании-грузоотправителя (shipperName), и результатом запроса будут строки только для этого грузоотправителя. Ниже приводится T-SQL-программа, используемая для создания новой хранимой процедуры:

USE Northwind
GO 
 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "LateShipments" AND 
                              type = "P")  
DROP PROCEDURE LateShipments 
GO 
 
CREATE PROCEDURE LateShipments @shipperName char(40) 
AS 
SELECT   RequiredDate, 
           ShippedDate, 
           Shippers.CompanyName 
FROM     Orders, Shippers 
WHERE    ShippedDate                  > RequiredDate AND 
           Orders.ShipVia             = Shippers.ShipperID AND 
            Shippers.CompanyName           = @shipperName 
GO

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

Procedure LateShipments, Line 0 Procedure 'LateShipments'
expects parameter '@shipperName', which was not supplied.
(Процедура LateShipments, Строка 0 процедуры 'LateShipments',
предполагается параметр '@shipperName', который не был указан)
Чтобы получить строки для грузоотправителя Speedy Express, выполните следующие операторы:
USE Northwind
GO

EXECUTE LateShipments "Speedy Express" 
GO

Вы увидите 12 строк, возвращенные в результате вызова этой хранимой процедуры.

Вы можете также задать для параметра значение по умолчанию, которое будет использоваться, когда этот параметр не указан в обращении к процедуре. Например, чтобы использовать для вашей хранимой процедуры значение по умолчанию United Package, измените текст создаваемой процедуры следующим образом: (изменена только строка CREATE PROCEDURE )

USE Northwind
GO 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "LateShipments" AND 
                              type = "P")  
DROP PROCEDURE LateShipments 
GO 


CREATE PROCEDURE LateShipments @shipperName char(40) = "United Package"

AS 
SELECT   RequiredDate, 
           ShippedDate, 
           Shippers.CompanyName 
FROM     Orders, Shippers 
WHERE    ShippedDate                  > RequiredDate AND 
           Orders.ShipVia             = Shippers.ShipperID AND 
           Shippers.CompanyName   = @shipperName 
GO

Теперь при запуске процедуры LateShipments без входного параметра ( @shipperName ) по умолчанию для этого параметра будет использоваться значение United Package ; процедура возвратит 16 строк. Но если вы укажете входной параметр, то его значение заместит значение, определенное по умолчанию.

Для возврата значения параметра хранимой процедуры в вызывающую программу используйте ключевое слово OUTPUT после имени этого параметра. Чтобы сохранить значение в переменной, которую можно использовать в вызывающей программе, используйте при вызове хранимой процедуры ключевое слово OUTPUT. Чтобы увидеть, как это происходит, мы создадим новую хранимую процедуру, которая выбирает цену единицы указанного продукта. Входной параметр @prod_id – это идентификатор продукта, а выходной параметр @unit_price – это возвращаемое значение цены единицы продукта. В вызывающей программе будет объявлена локальная переменная с именем @price, которая будет использоваться для сохранения возвращаемого значения. Ниже приводится набор операторов, используемый для создания хранимой процедуры GetUnitPrice:

USE Northwind
GO

IF EXISTS   (SELECT     name
                 FROM       sysobjects
                 WHERE      name = "GetUnitPrice" AND
                              type = "P")
DROP PROCEDURE GetUnitPrice
GO

CREATE PROCEDURE GetUnitPrice @prod_id int, @unit_price money OUTPUT

AS 
SELECT @unit_price = UnitPrice 
FROM   Products 
WHERE  ProductID = @prod_id 
GO

Прежде чем использовать переменную в вызове хранимой процедуры, вы должны объявить эту переменную в вызывающей программе. Например, в следующей программе мы сначала объявим переменную @price и присвоим ей тип данных money (который должен быть совместим с типом данных выходного параметра), а затем выполним хранимую процедуру:

DECLARE @price money
EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT
PRINT CONVERT(varchar(6), @price)
GO

Оператор PRINT возвращает значение 13.00 в переменной @price. Отметим, что мы использовали оператор CONVERT для преобразования значения @price в данные типа varchar, чтобы их можно было напечатать как строку, как символьный тип данных или как тип данных, которые могут быть неявно преобразованы в символьный тип, что требуется для оператора PRINT.

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

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

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

Как показано в предыдущем разделе, для создания локальных переменных используется ключевое слово DECLARE. При создании локальной переменной вы должны задать для нее имя и тип данных, а также поставить перед именем переменной символ @. При объявлении переменной ей первоначально присваивается значение NULL.

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

Рассмотрим пример хранимой процедуры, содержащей локальные переменные. Эта процедура вставляет пять строк в таблицу с помощью циклической конструкции WHILE. Сначала мы создадим таблицу с именем mytable для этого примера и затем создадим хранимую процедуру InsertRows. В этой процедуре мы будем использовать локальные переменные @loop_counter и @start_val, которые объявим вместе и разделим запятой. В следующей T-SQL-программе создается таблица и хранимая процедура:

USE MyDB
GO

CREATE TABLE mytable
(
        column1 int,
         column2 char(10)
)
GO

CREATE PROCEDURE InsertRows @start_value int
AS
DECLARE     @loop_counter int, @start_val int
SET             @start_val = @start_value – 1
SET             @loop_counter = 0
WHILE (@loop_counter < 5)
     BEGIN
     INSERT INTO mytable VALUES (@start_val + 1, "new row")
          PRINT (@start_val)
          SET @start_val = @start_val + 1
          SET @loop_counter = @loop_counter + 1
     END
GO

А теперь выполним эту хранимую процедуру с начальным значением 1, как это показано ниже:

EXECUTE InsertRows 1
GO

Вы увидите пять значений, выведенных для @start_val: 0, 1, 2, 3 и 4.

Выберите все строки из таблицы mytable с помощью следующего оператора:

SELECT *
FROM   mytable
GO

После выполнения этого оператора SELECT мы получим следующие выходные результаты:

column1    column2
-----------------------
1           new row
2           new row
3           new row
4           new row
5           new row

После завершения хранимой процедуры переменные @loop_counter и @start_val уже недоступны. Вы получите сообщение об ошибке, если попытаетесь вывести их с помощью следующего оператора T-SQL:

PRINT (@loop_counter)
PRINT (@start_val)
GO

Сообщение об ошибке будет иметь следующую форму:

Msg 137, Level 15, State 2, Server JAMIERE3, Line 1
Must declare the variable '@loop_counter'.
(Должна быть объявлена переменная '@loop_counter')
Msg 137, Level 15, State 2, Server JAMIERE3, Line 2 
Must declare the variable '@start_value'.
(Должна быть объявлена переменная '@start_value')

Те же правила, связанные с областью действия переменной, применимы к выполнению пакетного набора операторов. Как только появляется ключевое слово GO (являющееся признаком конца пакета), локальные переменные, объявленные внутри пакета, становятся недоступны. Область действия локальной переменной находится только в пределах пакета. Чтобы лучше понять эти правила, рассмотрим вызов хранимой процедуры в одном из приведенных выше примеров:

USE Northwind
GO

DECLARE @price money
EXECUTE GetUnitPrice 77, @unit_price = @price OUTPUT
PRINT CONVERT(varchar(6), @price)
GO

PRINT CONVERT(varchar(6), @price)
GO

В первом операторе PRINT выводится значение локальной переменной @price из данного пакета. Во втором операторе сделана попытка вывести его снова вне пакета, но в результате появится сообщение об ошибке в следующей форме:

13.00

Msg 137, Level 15, State 2, Server JAMIERE3, Line 2
Must declare the variable '@price'.
(Должна быть объявлена переменная '@price')

Отметим, что первый оператор PRINT выполнен успешно. (Он вывел значение 13.00.)

Возможно, вам потребуется использовать операторы BEGIN TRANSACTION, COMMIT и ROLLBACK в хранимой процедуре, которая содержит более одного оператора T-SQL. Это позволяет указывать, какие операторы должны быть сгруппированы как одна транзакция. (Об использовании транзакций и этих операторов см. лекцию 19.)

Использование RETURN

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

Сначала рассмотрим пример использования RETURN просто для выхода из хранимой процедуры. Мы создадим модифицированную версию процедуры GetUnitPrice, которая проверяет, было ли задано входное значение, и если нет, то выводит сообщение для пользователя и возвращается в вызывающую программу. Для этого мы определим входной параметр со значением по умолчанию NULL и затем будем проверять это значение в процедуре; если входной параметр имеет значение NULL, это означает, что входное значение не задано. Ниже приводится пример удаления и повторного создания этой процедуры:

USE Northwind
GO

IF EXISTS   (SELECT     name
                 FROM       sysobjects
                 WHERE      name = "GetUnitPrice" AND
                              type = "P")
DROP PROCEDURE GetUnitPrice
GO

CREATE PROCEDURE GetUnitPrice @prod_id int = NULL
AS
IF @prod_id IS NULL
     BEGIN
          PRINT "Please enter a product ID number"
          RETURN
     END
ELSE
     BEGIN
          SELECT   UnitPrice
          FROM     Products
          WHERE    ProductID = @prod_id
     END
GO

А теперь запустим GetUnitPrice без ввода входного значения и посмотрим результаты. Для запуска этой хранимой процедуры вы должны указать оператор EXECUTE, поскольку вызов процедуры не является первым оператором этого пакета. Используйте следующую последовательность:

PRINT "Before procedure"
EXECUTE GetUnitPrice
PRINT "After procedure returns from stored procedure"
GO

Результаты будут выведены в следующей форме:

Before procedure (До процедуры)
Please enter a product ID number (Введите идентификационный номер продукта)
After procedure returns from stored procedure (После возврата из хранимой процедуры)

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

Теперь рассмотрим использование RETURN для возврата значения в вызывающую программу. Возвращаемое значение должно быть целым. Это может быть константа или переменная. Вы должны объявить в вызывающей программе переменную, в которой будет храниться возвращаемое значение для дальнейшего использования в этой программе. Например, следующая процедура возвратит значение 1, если цена единицы продукции для продукта, указанного во входном параметре, меньше $100 ; иначе она возвратит значение 99.

CREATE PROCEDURE CheckUnitPrice @prod_id int
AS
IF   (SELECT UnitPrice
      FROM   Products
      WHERE  ProductID = @prod_id) < 100
      RETURN 1
ELSE
      RETURN 99
GO

Для вызова этой хранимой процедуры и использования возвращаемого значения объявите в вызывающей программе переменную и приравняйте ее возвращаемому значению хранимой процедуры (указав значение 66 в ProductID для входного параметра):

DECLARE @return_val int
EXECUTE @return_val = CheckUnitPrice 66
IF (@return_val = 1) PRINT "Unit price is less than $100"
GO

В результате будет выведен текст "Unit price is less than $100" (Цена единицы продукции меньше $100), поскольку цена единицы продукции для указанного продукта равна $17 и, тем самым, возвращаемое значение равно 1. Убедитесь в том, что вы задали целый тип данных, когда объявляете переменную, которая используется для хранения значения, возвращаемого оператором RETURN, поскольку этот оператор возвращает целое значение.

Использование SELECT для возвращаемых значений

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

Рассмотрим пару примеров. Сначала мы создадим новую хранимую процедуру с именем PrintUnitPrice, которая возвращает цену единицы продукции для продукта, указанного во входном параметре (с помощью идентификатора этого продукта). Используется следующая последовательность:

CREATE PROCEDURE PrintUnitPrice @prod_id int
AS
SELECT     ProductID,
             UnitPrice
FROM       Products
WHERE      ProductID = @prod_id
GO

Вызовите эту процедуру со значением входного параметра 66, как это показано ниже:

PrintUnitPrice 66
GO

Результаты будут выведены в следующей форме:

ProductID       UnitPrice
------------------------------------------
66                    17.00
(1 row(s) affected)

Для возврата значений переменных с помощью оператора SELECT, укажите после SELECT имя соответствующей переменной. В следующем примере мы снова создадим хранимую процедуру CheckUnitPrice, которая возвращает значение переменной, а также зададим заголовок выводимой колонки:

USE Northwind
GO  
 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "CheckUnitPrice" AND 
                              type = "P")  
DROP PROCEDURE CheckUnitPrice 
GO 

CREATE PROCEDURE CheckUnitPrice @prod_id INT 
AS 
DECLARE @var1 int 
IF   (SELECT     UnitPrice 
      FROM       Products 
      WHERE      ProductID = @prod_id) > 100 
      SET          @var1 = 1 
ELSE 
      SET    @var1 = 99 
SELECT "Variable 1" = @var1 
PRINT "Can add more T-SQL statements here" 
GO

Вызовите эту процедуру со значением входного параметра 66, как это показано ниже:

CheckUnitPrice 66 
GO

Результаты выполнения этой хранимой процедуры будут выведены в следующей форме:

Variable   1 
--------------
           99 
 
(1 row(s) affected) 
 
Can add more T-SQL statements here

Мы вывели текст оператора PRINT "Can add more T-SQL statements here" (Здесь можно добавить другие операторы T-SQL), чтобы показать отличие между возвратом значения с помощью SELECT и возвратом значения с помощью RETURN. Оператор RETURN прекращает работу хранимой процедуры в том месте, где он находится, а оператор SELECT возвращает свой результирующий набор, после чего продолжается выполнение хранимой процедуры.

Если бы в этом примере мы не задали заголовок колонки (просто указали бы SELECT @var1 ), то получили бы результат без заголовка, как это показано ниже:

--------------
99
 
(1 row(s) affected)

Использование Enterprise Manager

Теперь, когда вы знаете, как использовать T-SQL для создания хранимых процедур, рассмотрим, как использовать для их создания Enterprise Manager. Чтобы создать хранимую процедуру с помощью Enterprise Manager, вам по-прежнему нужно знать, как записываются операторы T-SQL. Enterprise Manager просто снабжает вас графическим интерфейсом, в котором можно создавать вашу процедуру. Мы опробуем этот метод, повторно создав хранимую процедуру InsertRows, как это описано ниже.

Чтобы удалить хранимую процедуру, сначала раскройте папку базы данных MyDB в левой панели. Enterprise Manager и щелкните на папке Stored Procedures (Хранимые Процедуры). В правой панели появятся все хранимые процедуры этой базы данных. Щелкните правой кнопкой мыши на хранимой процедуре InsertRows (она должна существовать – мы создали ее ранее в этой лекции) и выберите из контекстного меню пункт Delete (Удалить). (Вы можете также переименовать или скопировать хранимую процедуру через это контекстное меню.) Появится диалоговое окно Drop Objects (Удаление объектов) (рис. 21.1). Щелкните на кнопке Drop All (Удалить все), чтобы удалить хранимую процедуру.

Щелкните правой кнопкой мыши на папке Stored Procedures и выберите из контекстного меню пункт New Stored Procedure (Создать хранимую процедуру). Появится диалоговое окно Stored Procedure Properties (Свойства хранимой процедуры) (рис 21.2(рис 21.2) Диалоговое окно Drop Objects (Удаление объектов)(рис 21.1) Окно Stored Procedure Properties (Свойства хранимой процедуры)

В поле Text (Текст) вкладки General (Общие) замените (рис 21.3) Текст T-SQL-программы для новой хранимой процедуры

Щелкните на кнопке Check Syntax (Проверить синтаксис), чтобы SQL Server указал ошибки синтаксиса T-SQL в хранимой процедуре. Исправьте найденные синтаксические ошибки и снова щелкните на кнопке Check Syntax. После успешной проверки синтаксиса вы увидите окно сообщения (рис 21.4(рис 21.4) Окно сообщения об успешной проверке синтаксиса хранимой процедуры

Щелкните на кнопке OK в окне Stored Procedure Properties, чтобы создать вашу хранимую процедуру и вернуться в окно Enterprise Manager. Щелкните на папке Stored Procedures в левой панели Enterprise Manager, чтобы показать новую хранимую процедуру в правой панели (рис 21.5(рис 21.5) Новая хранимая процедура в окне Enterprise Manager

Чтобы присвоить пользователям полномочия выполнения новой хранимой процедуры, щелкните правой кнопкой мыши на имени этой хранимой процедуры в правой панели Enterprise Manager и выберите из контекстного меню пункт Properties (Свойства). В появившемся окне Stored Procedure Properties щелкните на кнопке Permissions (Полномочия). Появится окно Object Properties (Свойства объектов) (рис 21.6(рис 21.6) Вкладка Permissions окна Object Properties (Свойства объектов)

Щелкните на кнопке Apply (Применить) и затем щелкните на кнопке OK, чтобы присвоить выбранные вами полномочия и вернуться в окно Stored Procedure Properties. Для завершения щелкните на кнопке OK.

Вы можете также использовать Enterprise Manager для редактирования хранимой процедуры. Для этого щелкните правой кнопкой мыши на имени этой процедуры и выберите из контекстного меню пункт Properties. Выполните редактирование процедуры в окне Stored Procedure Properties (рис. 21.3), проверьте синтаксис, щелкнув на кнопке Check Syntax, щелкните на кнопке Apply и затем щелкните на кнопке OK.

Кроме того, вы можете использовать Enterprise Manager для управления полномочиями по хранимой процедуре. Для этого щелкните правой кнопкой мыши на имени хранимой процедуры в окне Enterprise Manager, укажите в контекстном меню пункт All Tasks (Все задачи) и выберите пункт Manage Permissions (Управление полномочиями). Вы можете также создавать публикацию для репликации (см. лекцию 26), генерировать сценарии SQL и отображать зависимости (dependencies) для хранимой процедуры из подменю All Tasks. Если вы решите генерировать сценарии SQL, то SQL Server автоматически создаст файл сценария (с указанным вами именем), который будет содержать определение хранимой процедуры. Затем вы сможете при необходимости повторно создать процедуру, используя этот сценарий.

Использование мастера Create Stored Procedure Wizard

Третий метод создания хранимых процедур – использование мастера создания хранимых процедур Create Stored Procedure Wizard – дает вам основу для написания ваших процедур из шаблонных операторов T-SQL. Вы можете применить мастер для создания хранимой процедуры, используемой для вставки, удаления или обновления строк таблиц. Этот мастер не помогает создавать процедуры, которые считывают строки из таблицы.

Мастер позволяет вам создавать несколько хранимых процедур в одной базе данных без необходимости выхода из мастера и его перезапуска. Однако для создания процедуры в другой базе данных вы должны снова запустить мастер. Для запуска этого мастера выполните следующие шаги.

В окне Enterprise Manager выберите из меню Tools (Сервис) пункт Wizards (Мастера), чтобы появилось диалоговое окно Select Wizard (Выбор мастера). Раскройте папку Database и щелкните на строке Create Stored Procedure Wizard (рис 21.7(рис 21.7) Диалоговое окно Select Wizard (Выбор мастера)

Щелкните на кнопке OK, чтобы появилось начальное окно мастера Create Stored Procedure Wizard (рис 21.8(рис 21.8) Начальное окно мастера Create Stored Procedure Wizard (Создание хранимых процедур)

Щелкните на кнопке Next (Далее), чтобы появилось окно Select Database (Выбор базы данных). Введите имя базы данных, в которой вы хотите создать хранимую процедуру.

Щелкните на кнопке Next, чтобы появилось окно Select Stored Procedures (Выбор хранимых процедур) (рис 21.9(рис 21.9) Окно Select Stored Procedures (Выбор хранимых процедур)В данном примере показаны две таблицы, которые использовались на протяжении всей этой книги. Как видно из рисунка, таблице Bicycle_Inventory были присвоены две процедуры: процедура вставки (insert)) и процедура обновления (update). Как показано в последующих шагах, вы можете изменять эти процедуры до их реального создания.Примечание. Одна хранимая процедура может выполнять несколько типов модификаций данных, но мастер Create Stored Procedure Wizard создает каждый тип модификаций в виде отдельной хранимой процедуры. Вы можете изменять любую из создаваемых этим мастером процедур, добавляя другие операторы T-SQL.

Щелкните на кнопке Next, чтобы появилось окно мастера Create Stored Procedure Wizard (рис. 21.10). В этом окне содержится список имен и описаний всех хранимых процедур, которые будут созданы, когда вы завершите работу мастера.

Чтобы переименовать и отредактировать хранимую процедуру, начните с выбора ее имени в окне Completing the Create Stored Procedure Wizard и затем щелкните на кнопке Edit (Правка), чтобы появилось окно Edit Stored Procedure Properties (Редактирование свойств хранимой процедуры) (рис 21.11(рис 21.11) Окно мастера Completing the Create Stored Procedure Wizard(рис 21.10) Окно Edit Stored Procedure Properties (Редактирование свойств хранимой процедуры)В этом примере показано шесть колонок таблицы Bicycle_Inventory, на которые может повлиять процедура вставки с текущим именем insert_Bicycle_Inventory_1. Для каждой колонки таблицы установлен флажок в колонке Select. Эти флажки указывают, что при выполнении данной хранимой процедуры потребуется ввод значений во все шесть колонок и что хранимая процедура вставит эти шесть значений в соответствующие шесть колонок.

Для переименования хранимой процедуры удалите существующее имя в текстовом поле Name и замените его новым именем.

Для редактирования хранимой процедуры щелкните на кнопке Edit SQL (Редактировать SQL), чтобы появилось диалоговое окно Edit Stored Procedure SQL (Редактирование SQL хранимой процедуры) (рис. 21.12). Здесь вы можете видеть T-SQL-программу для хранимой процедуры. Как видно из рисунка, здесь используются довольно простые операторы T-SQL. В данном примере пять параметров, которые вы указываете при вызове этой хранимой процедуры, – это значения, которые вставляются в виде новой строки в таблицу. Для редактирования этой процедуры просто введите ваши изменения в текстовом окне. Закончив правку, щелкните на кнопке Parse (Синтаксический разбор), чтобы проверить синтаксис, исправьте ошибки и затем щелкните на кнопке OK, чтобы вернуться в окно мастера Completing the Create Stored Procedure Wizard.(рис 21.12) Диалоговое окно Edit Stored Procedure SQL

После внесения возможных изменений и проверки процедуры щелкните на кнопке Finish (Готово), чтобы создать хранимые процедуры. Не забудьте задать полномочия по каждой из хранимых процедур после создания этих процедур. (См. раздел "Использование Enterprise Manager" выше.)

Как видите, этот мастер нельзя назвать очень полезным. Если вы знаете, как писать программы на языке T-SQL, то вы можете также использовать сценарии или Enterprise Manager для создания ваших собственных хранимых процедур.

Управление хранимыми процедурами с помощью T-SQL

Теперь, когда мы знаем, как создавать хранимые процедуры, рассмотрим, как использовать операторы T-SQL для изменения, удаления и просмотра содержимого хранимой процедуры.

Оператор ALTER PROCEDURE

Оператор T-SQL ALTER PROCEDURE используется для изменения хранимой процедуры, созданной с помощью оператора CREATE PROCEDURE. При использовании оператора ALTER PROCEDURE сохраняются исходные полномочия, установленные для данной хранимой процедуры, а изменения не влияют на любые зависимые процедуры или триггеры. (Зависимая процедура или триггер – это соответствующий объект, который вызывается процедурой.)

Синтаксис оператора ALTER PROCEDURE аналогичен синтаксису оператора CREATE PROCEDURE:

ALTER PROC[EDURE]     имя_процедуры
                               [ {@имя_параметра тип_данных} ] [= по_умолчанию] [OUTPUT]
                              [,...,n]
AS оператор(ы)_t-sql

В операторе ALTER PROCEDURE вы должны переписать всю хранимую процедуру, внося нужные изменения. Например, изменим хранимую процедуру GetUnitPrice, которую мы использовали в предыдущем примере, добавив условие проверки цен единицы продукции, превышающих $100, как это показано ниже:

USE Northwind
GO
 
IF EXISTS   (SELECT     name 
                 FROM       sysobjects 
                 WHERE      name = "GetUnitPrice" AND 
                              type = "P")  
DROP PROCEDURE GetUnitPrice 
GO 
 
CREATE PROCEDURE GetUnitPrice   @prod_id    int, 
                             @unit_price money OUTPUT 
AS 
SELECT   @unit_price = UnitPrice 
FROM     Products 
WHERE    ProductID = @prod_id 
GO  
ALTER PROCEDURE GetUnitPrice   @prod_id    int, 
                            @unit_price money OUTPUT 
AS 
SELECT   @unit_price = UnitPrice 
FROM     Products 
WHERE    ProductID = @prod_id AND 
           UnitPrice > 100 
GO

Теперь предоставим полномочия выполнения по этой хранимой процедуре пользователю DickB с помощью следующего оператора:

GRANT EXECUTE ON GetUnitPrice TO DickB
GO

Как уже говорилось выше, при изменении хранимой процедуры полномочия сохраняются. Изменим данную процедуру, чтобы выбирать строки, у которых значение колонки UnitPrice больше 200 (вместо 100), как это показано ниже:

ALTER PROCEDURE GetUnitPrice   @prod_id    int,
                       @unit_price money OUTPUT 
AS 
SELECT   @unit_price = UnitPrice 
FROM     Products 
WHERE    ProductID = @prod_id AND 
           UnitPrice > 200 
GO

После выполнения этого оператора ALTER PROCEDURE пользователь DickB будет по-прежнему иметь полномочия запуска данной хранимой процедуры.

Оператор DROP PROCEDURE

Оператор T-SQL DROP PROCEDURE действует просто – он удаляет хранимую процедуру. Вы не сможете восстановить хранимую процедуру после ее удаления. Если вам нужно использовать удаленную процедуру, вы должны полностью воссоздать ее с помощью оператора CREATE PROCEDURE. Все полномочия по удаленной хранимой процедуре будут утрачены, и они должны быть предоставлены снова. Ниже приводится пример использования DROP PROCEDURE для удаления процедуры GetUnitPrice:

USE Northwind
GO
 
DROP PROCEDURE GetUnitPrice 
GO

Хранимая процедура sp_helptext

Системная хранимая процедура sp_helptext позволяет вам просматривать определение любой хранимой процедуры и оператора, который использовался для создания этой процедуры. (Ее можно также использовать для просмотра определения триггера, представления, правила или значения по умолчанию.) Это средство полезно, если вы хотите быстро воспроизвести определение процедуры (или одного из только что упомянутых объектов), когда используете ISQL, OSQL или анализатор запросов SQL Query Analyzer. Вы можете также направлять выходные результаты в файл, чтобы создать из этого определения сценарий, который можно использовать для редактирования или повторного создания процедуры. Чтобы использовать sp_helptext, вы должны указать в качестве параметра имя вашей хранимой процедуры (или имя другого объекта). Например, для просмотра операторов, которые использовались выше для создания процедуры InsertRows, используйте следующую команду. (И здесь для выполнения данной команды вы должны использовать базу данных, в которой находится процедура.)

USE MyDB GO sp_helptext InsertRows GO

Выводимые результаты выглядят следующим образом:

Text
---------------------------------------------------------------------
CREATE PROCEDURE InsertRows   @start_value int 
AS 
DECLARE    @loop_counter int, 
               @start_val    int 
SET            @start_val = @start_value – 1 
SET            @loop_counter = 0 
WHILE (@loop_counter < 5) 
     BEGIN 
          INSERT INTO mytable VALUES (@start_val + 1, 'new row') 
          PRINT (@start_val) 
          SET      @start_val        = @start_val + 1 
          SET      @loop_counter   = @loop_counter + 1 
   END

Заключение

В этой лекции вы узнали, что такое системные хранимые процедуры и определяемые пользователем хранимые процедуры и почему они используются. Вы также узнали, как создавать хранимые процедуры с помощью операторов T-SQL, Enterprise Manager или мастера Create Stored Procedure Wizard, и увидели, чем отличаются эти методы. Кроме того, вы узнали, как использовать параметры и переменные и как выполнять хранимые процедуры. Мы также рассмотрели операторы T-SQL, используемые для изменения, удаления и просмотра текста хранимой процедуры. В лекции 22 мы рассмотрим триггеры – специальный тип хранимых процедур, – которые автоматически запускаются при определенных условиях.