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

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

Теперь добавим к нашей хранимой процедуре входной параметр, чтобы мы могли передавать данные в эту процедуру. Чтобы задать входные параметры в хранимой процедуре, укажите список этих параметров с символом @ перед именем каждого параметра, т.е. @имя_параметра. Вы можете задать в хранимой процедуре до 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.

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

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