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

0

Хранимая процедура Update использует основной метод идентификации строки и принимает в качестве параметров код товара и новые данные. На основе этих данных она выполняет инструкцию DML UPDATE.

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

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

Обновление С условием Rowversion

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

Если значение столбца rowversion отличается от извлеченного в момент отбора данных, то обновление не выполняется. Эта процедура определяет, что используется глобальная переменная @@rowcount, и передает ошибку вызывающему объекту.

В качестве примера можно привести текст хранимой процедуры pProduct_Update_ RowVersion из базы данных OBXKites:

CREATE PROCEDURE pProduct_Update_RowVersion (

@Code CHAR(15),

©RowVersion Rowversion,

@Name VARCHAR(50),

©ProductDescription VARCHAR(50),

@ActiveDate DateTime,

@DiscontinueDate DateTime )

AS

SET NoCount ON UPDATE dbo.Product SET

ProductName = @Name,

ProductDescription = ©ProductDescription,

ActiveDate = ©ActiveDate,

DiscontinueDate = @DiscontinueDate WHERE Code = @Code

AND [RowVersion] = @RowVersion IF @@ROWCOUNT = 0 BEGIN

IF EXISTS ( SELECT * FROM Product WHERE Code = @Code)

BEGIN

RAISERROR (‘Товар не был обновлен ввиду того, что другая транзакция обновила строку с момента последнего чтения.’, 16,1)

RETURN -100

END

ELSE

BEGIN

RAISERROR (‘Товар не был обновлен,

так как строка была удалена’, 16,1)

RETURN -100 END

END

RETURN

Для тестирования процедуры обновления извлечем штамп для товара с кодом 1001 с помощью процедуры pProduct_Fetch:

ЕХЕС pProduct_Fetch 10 01

Получим следующий результат (столбцы усечены):

Code                       Name RowVersion

1001 Basic Box Kite 21 inch 0x0000000000000077

Теперь вызовем процедуру pProduct_Update_Rowversion с полученным значением rowversion и попытаемся выполнить обновление:

ЕХЕС pProduct_Update_Rowversion 1001,

0x0000000000000077,

‘updatetest’,

‘new description’,

‘1/1/2002′,

NULL

Процедура обновит все столбцы строки, при этом столбцу rowversion будет присвоено новое значение.

Минимальное обновление

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

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

Процедура минимального обновления выполняет инструкцию UPDATE отдельно для каждого передаваемого ей параметра:

CREATE PROCEDURE pProduct_Update_Minimal (

@Code CHAR(15),

@Name VARCHAR(50) = NULL,

@ProductDescription VARCHAR(50) = NULL,

@ActiveDate DateTime = NULL,

@DiscontinueDate DateTime = NULL )

AS

SET NoCount ON

IF EXISTS (SELECT * FROM dbo.Product WHERE Code = @Code)

BEGIN

BEGIN TRANSACTION IF @Name IS NOT NULL BEGIN

UPDATE dbo.Product SET

ProductName = @Name WHERE Code = @Code IF @@Error <> 0 BEGIN

ROLLBACK RETURN -10 0 END

END

IF ©ProductDescription IS NOT NULL BEGIN

UPDATE dbo.Product SET

ProductDescription = @ProductDescription WHERE Code = @Code IF @@Error <> 0 BEGIN

ROLLBACK RETURN -100 END

END

IF @ActiveDate IS NOT NULL BEGIN

UPDATE dbo.Product SET

ActiveDate = @ActiveDate WHERE Code = @Code IF @@Error <> 0 BEGIN

ROLLBACK RETURN -10 0 END

END

IF @DiscontinueDate IS NOT NULL BEGIN

UPDATE dbo.Product SET

DiscontinueDate = @DiscontinueDate

WHERE Code = @Code IF @@Error <> 0 BEGIN

ROLLBACK RETURN -100 END

END

COMMIT TRANSACTION END ELSE BEGIN

RAISERROR

(‘Товар не был обновлен, так как строка была удалена1, 16,1)

RETURN -10 0 END RETURN

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

EXEC pProduct_Update_Minimal @Code = 11001′ ,

@ProductDescription = ‘a minimal update’

Протестируем результаты с помощью хранимой процедуры pProduct_Fetch:

EXEC pProduct_Fetch 10 01

Будет получен следующий результат (столбцы усечены):

Code                             Name            ProductDescription

1001                             updatetest      a minimal update

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

Хранимая процедура удаления выполняет инструкцию DML UPDATE. Она может оказаться наиболее сложной из всех, в зависимости от уровня архивирования данных и использования логических удалений. В примере, взятом из учетной базы данных OBXKites, переменная (©ProductCode преобразуется в (©ProductID, затем проверяется наличие данного товара, после чего он удаляется из таблицы.

CREATE PROCEDURE pProduct_Delete(

(©ProductCode INT )

AS

SET NOCOUNT ON

DECLARE (©ProductID Uniqueldentif ier SELECT (©ProductID = ProductID FROM Product

WHERE Code = (©ProductCode If @@RowCount = 0 BEGIN

RAISERROR

(‘Невозможно удалить товар с кодом %i

-         он не существует.’, 16,1, (©ProductCode)

RETURN

END

ELSE

DELETE dbo.Product

WHERE ProductID = @ProductID RETURN

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

ЕХЕС pProduct_Delete 99

Невозможно удалить товар с кодом 99 – он не существует.

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

Резюме

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

В следующей главе мы продолжим разговор о разработке базы данных, обратив свое внимание на портативный мир SQL Server Mobile.

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

По теме:

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