Главная » Microsoft SQL Server, Базы данных » Выполнение удаленных хранимых процедур

0

Существуют два метода вызова хранимых процедур, размещенных на другом сервере: ссылка по четырехкомпонентному имени и распределенный запрос. Оба метода требуют, чтобы удаленный сервер был связан с текущим. Хранимые процедуры могут только вызываться удаленно — создаваться удаленно они не могут.

Дополнительная О том, как установить защищенные подключения к удаленным серверам, см.

информация в главе 15.

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

сервер. база_данных. схема . имя_процедуры

В следующем примере в базе данных OBXKites во втором экземпляре сервера NOLI (рассматриваемом как удаленный) создается новая категория товаров:

EXEC [Noli\SQL2].OBXKites.dbo.pProductCategory_AddNew ‘Food1, ‘Eatables’

В качестве альтернативы для вызова удаленной хранимой процедуры может использоваться функция OpenQuery ():

OpenQuery (имя_связанного_сервера, ‘ exec хранимая_проце,пура 1 )

В следующем примере на выполнение запускается хранимая процедура pCustomerType_ Fetch, размещенная в базе данных по умолчанию для текущего регистрационного имени пользователя, использованного для подключения к серверу NOLI\SQL2. Если база данных по умолчанию указана некорректно, то для обращения к корректной базе будет использовано трехчастное имя:

SELECT CustomerTypeName, DiscountPercent, [Default]

FROM OPENQUERY^

[Noli\SQL2] , ‘OBXKites.dbo.pCustomerType_Fetch’j Результат выполнения запроса:

CustomerTypeName DiscountPercent Default

Preferred               10                                    0

Retail                     00                                   1

Wholesale              15                                   0

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

Завершенная хранимая процедура

В этом разделе будет представлен полный сценарий с хранимыми процедурами из учебной базы данных OBXKites. В нем для добавления заказов в базу данных будут использоваться три хранимые процедуры: pGetPrice, pOrder_AddNew и pOrder_AddItem. В этом сценарии будет продемонстрировано множество средств Т-SQL и хранимых процедур. При этом в начале каждого из соответствующих подразделов будет описано назначение и содержание процедуры, которой он посвящен.

Полный текст рассматриваемых хранимых процедур и вызывающих их пакетов вы можете найти в файлах OBXKites_Create. sql и OBXKites_Populate. sql.

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

В хранимой процедуре pGetPrice продемонстрировано использование значений параметров по умолчанию, выходных параметров, обработки ошибок и взаимоблокировок. На входе она принимает код товара, необязательную дату и необязательный код клиента. Используя эту информацию, процедура определяет корректную цену и возвращает ее в выходной переменной.

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

База данных OBXKites содержит таблицу прайс-листа, в которой каждый продукт может иметь разные цены в разные дни. Этот метод позволяет сохранить историю ценообразования и вводить цены заранее. Если функция pGetPrice запускается с пустым значением даты, то используется текущая дата. При этом для определения даты установки цены берется в расчет максимальная дата, не превышающая текущую (или заданную). Как только дата ценообразования будет определена, найти корректную цену просто. Для вычисления даты ценообразования в процедуре pGetPrice используется подзапрос.

Ошибки обрабатываются в блоке Catch. Если ошибка вызвана превышением времени ожидания блокировки или взаимоблокировкой, то обработчик ожидает еще четверть секунды, после чего переходит к метке LockTimeOutRetry, расположенной в начале процедуры, чтобы повторить попытку блокировки снова и успешно выполнить процедуру. Если после пяти попыток блокировка так и не была установлена, обработчик сообщает об ошибке и выходит из процедуры. Вот полный текст хранимой процедуры pGetPrice:

CREATE PROCEDURE pGetPrice(

@Code CHAR(10),

@PriceDate DATETIME = NULL,

@ContactCode CHAR(15) = NULL,

@CurrPrice MONEY OUTPUT )

AS

– Возвращает активную цену для заданной даты или текущего дня — Тип заказчика определяет величину скидки

– Выходной параметр, @CurrPrice, будет содержать активную цену — Пример кода для вызова этой хранимой процедуры:

– Declare @Price money

– ЕХЕС GetPrice ‘1006’, NULL, @Price OUTPUT

– Select @Price SET NOCOUNT ON DECLARE

@DiscountPercent NUMERIC (4,2),

@Err INT,

@ErrCounter INT SET @ErrCounter = 0 SET @CurrPrice = NULL

LockTimeOutRetry:

BEGIN TRY

IF @PriceDate IS NULL

SET @PriceDate = GETDATEO — устанавливаем скидку;

– если код клиента отсутствует, она равна нулю SELECT @DiscountPercent = CustomerType.DiscountPercent FROM dbo.Contact JOIN dbo.CustomerType

ON contact.CustomerTypeID = CustomerType.CustomerTypelD WHERE ContactCode = @ContactCode IF @DiscountPercent IS NULL SET @DiscountPercent = 0 SELECT @CurrPrice = Price * (l-@DiscountPercent)

FROM dbo.Price JOIN dbo.Product

ON Price.ProductID = Product.ProductID WHERE Code = @Code

AND EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dbo.Price JOIN dbo.Product

ON Price.ProductID = Product.ProductID WHERE Code = @Code

AND EffectiveDate <= @PriceDate)

IF @CurrPrice IS NULL BEGIN

RAISERROR(‘Code: 11%s1‘ has no established price.1,15,1, @Code) RETURN -100

END END TRY BEGIN CATCH

SET @Err = @@ERROR

IF (@Err = 1222 OR @Err = 12 05) AND @ErrCounter = 5 BEGIN

RAISERROR (‘Unable to Lock Data after five attempts.’, 16,1) RETURN -100

END

IF @Err = 1222 OR @Err = 1205 — Lock Timeout / Deadlock BEGIN

WAITFOR DELAY ’00:00:00.25′

SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END

– неизвестная ошибка RAISERROR (@err, 16,1) WITH LOG RETURN -100 END CATCH

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

Данные заказа содержатся в двух таблицах: [Order] и OrderDetail. Первая из них содержит информацию заголовка, а вторая — список товаров. Открытие заказа предполагает сбор и проверку информации заголовка, генерацию номера заказа (OrderNumber) и вставку строки в таблицу [Order].

Хранимая процедура pOrder_AddNew принимает в качестве параметров код заказчика, код работника, ответственного за прием заказа, местонахождение пункта продажи и дату приема заказа.

Продажи иногда осуществляются задним числом, так что предполагать, что датой заказа будет текущая — бессмысленно. Если дата заказа не передается в процедуру, она устанавливается в инструкции INSERT в текущую (так как параметр принимает значение по умолчанию — null).

Код заказчика также является необязательным параметром. Если он не передается в процедуру, принимается значение по умолчанию — 0 и затем преобразуется в пустое значение идентификатора клиента (ContactID). Схема базы данных допускает пустые значения идентификаторов заказчиков, предполагая, что некоторые из них захотят остаться анонимными.

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

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

Используемая схема обработки ошибок сходна с процедурой pGetPrice. Единственное отличие заключается в том, что перед обработкой ошибки транзакция откатывается.

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

CREATE PROC pOrder_AddNew (

@ContactCode CHAR(15) = 0,

– по умолчанию заказчик анонимный @EmployeeCode CHAR(15),

@LocationCode CHAR(15),

@OrderDate DATETIME = NULL,

@OrderNumber INT OUTPUT )

AS

– Логика:

– Если номер заказчика указан, проверяем его

SET NOCOUNT ON

DECLARE

@ContactID UNIQUEIDENTIFIER,

@OrderID UNIQUEIDENTIFIER,

@LocationID UNIQUEIDENTIFIER,

@EmployeeID UNIQUEIDENTIFIER,

@Err INT,

@ErrCounter INT SET @ErrCounter = 0

LockTimeOutRetry:

– Находим идентификатор заказчика IF @ContactCode = О

SET @ContactID = NULL ELSE BEGIN

SELECT @ContactID = ContactID FROM dbo.Contact

WHERE ContactCode = @ContactCode SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler IF @ContactID IS NULL

BEGIN – указанный заказчик не найден RAISERROR(

‘Код заказчика: ‘ ‘ %s не найден’,15,1, @ContactCode) RETURN -100 END END

– Находим идентификатор местоположения SELECT @LocationID = LocationID FROM dbo.Location

WHERE LocationCode = @LocationCode SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler IF @LocationID IS NULL

BEGIN – Местоположение не найдено RAISERROR(

‘Код региона: ”%s” не найден’,15,1, @LocationCode) RETURN -100 END

IF EXISTS(SELECT *

FROM dbo.Location WHERE LocationID = @LocationID AND IsRetail = 0)

BEGIN – Нет точек продаж RAISERROR(

‘Регион: ”%s1‘ не имеет точек продажи’,15,1,

@Locat ionCode)

RETURN -100

END

– Находим идентификатор сотрудника SELECT @EmployeeID = ContactID FROM dbo.Contact

WHERE ContactCode = @EmployeeCode SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler IF @EmployeeCode IS NULL

BEGIN – Сотрудник не найден RAISERROR(

‘Код сотрудника: ”%s1‘ не найден’,15,1, ©EmployееCode) RETURN -100 END

– Генерация номера заказа SET @OrderID = NEWID()

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT @OrderNumber = Max(OrderNumber) + 1 FROM [Order]

SET @OrderNumber = ISNULL(@OrderNumber, 1)

SET @Err = @@ERROR IF @Err <> 0 BEGIN

ROLLBACK TRANSACTION GOTO ErrorHandler END

– Bee OK – выполняем вставку INSERT dbo.[Order] (

OrderlD, ContactID, OrderNumber,

EmployeelD, LocationID, OrderDate )

VALUES (

@OrderID, @ContactID,@OrderNumber,

@EmployeeID, @LocationID, ISNULL(@OrderDate,GETDATE()))

IF @Err <> 0 BEGIN

ROLLBACK TRANSACTION GOTO ErrorHandler END

COMMIT TRANSACTION

RETURN – номер заказа @OrderNumber установлен ErrorHandler:

IF (@Err = 1222 OR @Err = 1205) AND @ErrCounter = 5 BEGIN

RAISERROR (‘Unable to Lock Data after five attempts.’, 16,1) RETURN -10 0 END

IF @Err = 1222 OR @Err = 1205 — Отсутствие блокировки

– или взаимоблокировка

BEGIN

WAITFOR DELAY *00:00:00.25′

SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END

– иначе — неизвестная ошибка RAISERROR (@err, 16,1) WITH LOG RETURN -100

Хранимая Процедура pOrder_AddItem

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

Как и другие хранимые процедуры, pOrder_AddItem начинается с проверки всех параметров и вставки ассоциированных идентификаторов в таблицу OrderDetail.

В этой процедуре цена товара определяется процедурой pGetPrice, если в параметре она пуста. (В главе 22 мы создадим функцию fGetPrice.) С целью сравнения в данной процедуре также использован вызов этой функции, но эта часть текста закомментирована. Ниже приведен текст хранимой процедуры pOrder_AddItem.

CREATE PROCEDURE pOrder_AddItem(

@OrderNumber CHAR(15),

@Code CHAR(15) =0, –по умолчанию товар ищется на складе @NonStockProduct NVARCHAR(256) = NULL,

@Quantity NUMERIC(7,2),

@UnitPrice MONEY =0, –по умолчанию цена определяется

– по прайс-листу @ShipRequestDate DATETIME = NULL, — по умолчанию доставка

– немедленная

@ShipComment NVARCHAR(256) = NULL — комментарий не обязателен )

AS

DECLARE

@OrderID UNIQUEIDENTIFIER,

@ProductID UNIQUEIDENTIFIER,

@ContactCode CHAR(15),

@PriceDate DATETIME,

@Err INT,

@ErrCounter INT SET @ErrCounter = 0

LockTimeOutRetry:

– Получаем идентификатор заказа SELECT @OrderID = OrderlD FROM dbo.[Order]

WHERE OrderNumber = @OrderNumber SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler

– Получаем идентификатор товара SELECT @ProductID = ProductID FROM Product WHERE Code = @Code SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler

             Получаем код клиента и дату установки прайс-листа

SELECT @ContactCode = ContactCode, @PriceDate = OrderDate FROM dbo.[Order]

LEFT JOIN Contact

ON [Order].ContactID = Contact.ContactID SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler

– Получаем цену за единицу товара IF @UnitPrice IS NULL EXEC pGetPrice

@Code, @PriceDate, @ContactCode, @UnitPrice OUTPUT — Альтернативный вызов функции fGetPrice — SET @UnitPrice = dbo.fGetPrice (

– @Code,@PriceDate, @ContactCode)

SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler

IF @UnitPrice IS NULL BEGIN

RAISERROR(

‘Для кода товара: 1‘%s” цена не установлена. ‘,15,1,

@Code)

RETURN -1 END

– Устанавливаем дату доставки IF @ShipRequestDate IS NULL

SET @ShipRequestDate = @PriceDate — Выполняем вставку строки INSERT OrderDetail(

OrderlD, ProductID, NonStockProduct, Quantity,

UnitPrice, ShipRequestDate, ShipComment)

VALUES (

@OrderID, @ProductID, @NonStockProduct, @Quantity,

@UnitPrice, @ShipRequestDate, @ShipComment)

SET @Err = @@ERROR

IF @Err <> 0 GOTO ErrorHandler RETURN 0

ErrorHandler:

IF (@Err = 1222 OR @Err = 1205) AND ©ErrCounter = 5 BEGIN

RAISERROR (

‘Невозможно блокировать данные за пять попыток.’, 16,1)

RETURN -100 END

– Превышено время ожидания блокировки или взаимоблокировки IF @Err = 1222 OR @Err = 1205 BEGIN

WAITFOR DELAY ’00:00:00.25′

SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END

else – неизвестная ошибка

RAISERROR (@err, 16,1) WITH LOG RETURN -100

Добавление заказа

Чтобы вы смогли увидеть созданные хранимые процедуры в действии, в следующем пакете, взятом из сценария OBXKites_Populate . sql, демонстрируется создание двух заказов. Этот пакет может быть реально послан на SQL Server для вставки в базу данных OBXKites двух заказов.

Хранимая процедура pOrder_AddNew создает строку нового заказа и возвращает вызывающему пакету его номер. После этого пакет создает строки заказа, передавая этот номер хранимой процедуре pOrder_AddItem. Локальная переменная пакета @OrderNumber используется для получения номера заказа из процедуры pOrder_AddNew и передачи его в каждый вызов процедуры pOrder_AddItem.

При формировании первого заказа в явном виде указаны имена параметров; при формировании второго заказа параметры перечисляются в установленном в определении процедуры порядке.

DECLARE @OrderNumber INT

–Первый заказ EXEC pOrder_AddNew

@ContactCode = ‘101’,

@EmployeeCode = ’12 0′,

@LocationCode = ‘ CH’,

@OrderDate=NULL,

@OrderNumber = @OrderNumber output EXEC pOrder_SetPriority @OrderNumber, 4′

EXEC pOrder_AddItem

@OrderNumber = @OrderNumber,

@Code = ‘1002’,

@NonStockProduct = NULL,

@Quantity = 12,

@UnitPrice = NULL,

@ShipRequestDate = 111/15/01 @ShipComment = NULL

– Второй заказ EXEC pOrder_AddNew

‘101’, 420′, ‘CH’, NULL, @OrderNumber output EXEC pOrder_AddItem

@OrderNumber, 4002′, NULL, 3, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, 4003′, NULL, 5, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, ‘1004’, NULL, 2, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, 4044′, NULL, 1, NULL, NULL, NULL

Резюме

Использование хранимых процедур является одним из способов сохранения и оптимизации пакетов. Хранимые процедуры компилируются и сохраняются в памяти при первом запуске. Не существует более быстрого метода выполнения инструкций SQL и не существует программных объектов, находящихся ближе к данным. Подобно пакетам, хранимые процедуры могут вернуть набор данных, воспользовавшись инструкцией SELECT.

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии