Россия, г. Санкт-Петербург |
Лекция 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.
Кроме того, отметим, что в хранимой процедуре и в вызывающей программе для выходных переменных использовались различные имена, чтобы вам было легче следить за положением этих переменных в примере и чтобы показать, что можно использовать различные имена.
При обращении к хранимой процедуре вы можете также задать входное значение в выходном параметре. Это означает, что это значение будет введено в хранимую процедуру, где это значение может быть изменено или использовано для операций; затем новое значение возвращается в вызывающую программу. Чтобы поместить входное значение в выходной параметр, нужно просто присвоить это значение соответствующей переменной в вызывающей программе перед выполнением процедуры или выполнить запрос, который считывает значение в переменную и затем передает эту переменную в хранимую процедуру. А теперь рассмотрим использование локальных переменных внутри хранимой процедуры.