Лекция 21: Создание хранимых процедур и управление этими процедурами

Управление хранимыми процедурами с помощью 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 будет по-прежнему иметь полномочия запуска данной хранимой процедуры.